imported comma separated double quoted | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

imported comma separated double quoted

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!
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
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.
See if this helps
http://www.nigelrivett.net/SQLTsql/BCP_quoted_CSV_Format_file.html Madhivanan Failing to plan is Planning to fail
]]>