SQL Server Performance

imported comma separated double quoted

Discussion in 'SQL Server 2005 General Developer Questions' started by v1rtu0s1ty, Jan 31, 2006.

  1. v1rtu0s1ty New Member

    hey guys,

    I have a text file that is comma separated and at the same the, the values are enclosed in double quotes.

    "1234567890","01/30/2006","Deleted"

    In MySQL's load data infile(similar to MSSQL's bulk insert), I can specify a comma delimeter and at the same time, the double quotes too. However, I looked at MSSQL BOL and trying to find a similar functionality wherein I can specify double quotes too, but didn't see anything.

    I know I can BULK INSERT it and run UPDATE statement to delete all the double quotes. It will work but it's an burden to the CPU.

    Please let me know how I can do this by just using bulk insert. Data is not fixed width too.

    Thanks!
  2. Chappy New Member

    I dont know the exact options to specify to BULK INSERT, but heres what i recommend...

    Run Import/Export wizard (start menu), and specify the import options. This allows you to specify double quotes, and a host of other parameters (ie, first row is header row).

    once done, on the last tab, opt to save this as a DTS job. You can then run this DTS from Enterprise Manager, or shcedule it to be run automatically using SQL Server Agent

    If you prefer to do it from raw SQL, you might want to run a SQL profile and see how the DTS handles it exactly. I agree that I dont see any options in the BULK INSER syntax whch would allow this explicitly, it could be that DTS simply post-processes the table
  3. v1rtu0s1ty New Member

    I profiled it and didn't see anything about parameter for the double quotes. Looks like Import Wizard is doing it programatically. <img src='/community/emoticons/emotion-6.gif' alt=':(' /><br /><br />I'll just talk to the vendor and ask them to delete the double quotes on their next text file.
  4. Madhivanan Moderator

Share This Page