SQL Server Import Wizard: column in .csv that contains both my delimiter and my text qualifier

0
votes

SQL Server 2008 R2 Express Edition.

I'm trying to use the import wizard to import a CSV. One column in my CSV contains both my delimiter (,) and my text qualifier ("):

...,"UV-blocking nanoparticles, Cancer therapy using ""magnetic vectoring"", Hearing aid technology",...

It looks like excel escapes the double quote that's within the column, but it's killing the import wizard. Any suggestions besides removing the double-quotes all together?

1
sqlsql-serversql-server-2008import
Is this a singe-instance import or are you going to do this multiple times? - IAmTimCorey
@BiggsTRC - I have about 10 files to import now, each with anywhere between 50 and 2k rows. It's one time for now, but I may be looking to save the SSIS package and schedule it in the future, so would like a cleaner solution if one is available. - Jeremy Wiggins

1 Answers

0
votes

For a one-time import, I would suggest doing a find and replace on the "forbidden" characters and then doing the reverse once the data is in SQL. That may not be elegant, but it is simple.

Edit: If you want to repeat this task and you have access to SSIS, skip the step of moving the data from Excel to a CSV and use SSIS to take the data right from Excel into SQL.