Question about Bulk Insert on MSSQL RRS feed

  • 問題

  • 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

    2008年4月4日 上午 10:18



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

    2008年4月4日 下午 03:04
  • 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

    2008年4月7日 上午 07:42
  • 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
  • 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
  • When you export data with bcp, it'll ask you if like to create format file.

    2008年4月8日 下午 05:33
  • 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
  • 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
  • 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.

    "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.

    2008年4月10日 上午 07:43
  • You may try loading data with bcp or data import wizard, both can specify text qualifier.

    2008年4月10日 下午 02:46
  • 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

    2008年4月10日 下午 03:06