登入
Microsoft.com
 
Microsoft
 
 
 

none
Question about Bulk Insert on MSSQL RRS feed

 > 

  • 問題

  • Question
    登入以投票
    0
    登入以投票
    Hi

    I have some csv files that I want to import to MS SQL 2000
    The csv file contain text and number, which is exported from another SQL database.
    They have use speech mark as Text quantifier. E.g. "abc"

    I am wondering if I can set a text quantifier on the BULK Insert statement to read any thing contains speech mark as a text, and the other as int? I have read the document on Microsoft website, it does not seem to have a text quantifier options.

    Many thanks

    Chi
    2008年4月4日 上午 10:18
    |

解答

所有回覆

  • Question
    登入以投票
    0
    登入以投票

    Take look at quoted identifier option under 'SET QUOTED_IDENTIFIER (Transact-SQL)' in books online.

    2008年4月4日 下午 03:04
    |
  • Question
    登入以投票
    0
    登入以投票
    Hi rmiao

    Thanks for your information. I have tried to SET QUOTED_IDENTIFIER ON to make it recongise the Speech mark is a String/Literal. Unfortunately, it still reading the speech mark into the database.

    Any suggestion?

    Many thanks

    Chi
    2008年4月7日 上午 07:42
    |
  • Question
    登入以投票
    0
    登入以投票

    Did you generate format file when export data? If so, use that format file in bulk insert. If not, bcp data out again with format file.

    2008年4月7日 下午 02:23
    |
  • Question
    登入以投票
    0
    登入以投票
    Hi Rmiao

    Thanks for the information. I am not sure what format file do I need to export.

    I think the csv/txt file that I have is generated on VB6 and is created programmatically, which mean it is not export directly from SQL Server.

    Thanks very much for your help.
    2008年4月7日 下午 02:32
    |
  • Question
    登入以投票
    0
    登入以投票

    When you export data with bcp, it'll ask you if like to create format file.

    2008年4月8日 下午 05:33
    |
  • Question
    登入以投票
    0
    登入以投票
    Hi Rmiao

    Thanks for your information, unfortunately, the files that I am using is generated by a VB program, for the other company, so I am not quite sure what I can do.

    Many thanks
    2008年4月9日 上午 07:40
    |
  • Question
    登入以投票
    0
    登入以投票

    I thought it's from another sql db per your first post:

     

    The csv file contain text and number, which is exported from another SQL database.

    2008年4月9日 下午 03:53
    |
  • Question
    登入以投票
    0
    登入以投票
    Hi Rmiao

    Sorry to mis-leading you. The csv file is export from another SQL database, that is generated by an other company. I think they are generated by using VB 6 Application.

    Here is example of the data.

    "CustomerCode","Prefix","PhoneNumber","StartTime","Duration",
    "CA1000","HK","01234512321",20/07/2007 20:58:08,2
    "FX1240","HK","01234512321",16/07/2007 09:50:08,66

    I am wondering if it is possible to import this file into SQL Server and remove the "Speech mark" if they are String?

    Thanks very much for your help.

    Chi
    2008年4月10日 上午 07:43
    |
  • Question
    登入以投票
    0
    登入以投票

    You may try loading data with bcp or data import wizard, both can specify text qualifier.

    2008年4月10日 下午 02:46
    |
  • Question
    登入以投票
    0
    登入以投票
    Thanks for your information, I will try to use BCP. I will let you know if the problem has been solved later.

    Thanks very much

    Chi
    2008年4月10日 下午 03:06
    |