0

There is an example csv file:

category,fruits,cost 
'Fruits','Apple,banana,lemon','10.58'

When I import this csv into SQL Server 2014

by clicking the database in "Object explorer"=>Task=>Import data.

No matter how I play around with column delimiter options, the row 2 will always become

5 columns (Fruits,Apple,banana,lemon,10.58) instead of the desired 3 columns ('Fruits','Apple,banana,lemon','10.58'). (So I want 'Apple,banana,lemon' to be in one column.)

The solution here How do I escape a single quote in SQL Server? doesn't work. Any guru could enlighten? Python, Linux bash, SQL or simple editor tricks are welcome! Thank you!

Community
  • 1
  • 1
Chubaka
  • 2,933
  • 7
  • 43
  • 58
  • Wouldn't you be able to change the delimiter to `','`? You still have to remove the very first and last one manually, but that could be easy with a SQL `UPDATE`. Not sure the delimiter option is doable in the import wizard, but might be an easy way to go at it. – SchmitzIT Dec 01 '15 at 09:40

1 Answers1

2

No matter how I play around with column delimiter options

That's not the option you need to play with - it's the Text Qualifier:

enter image description here

And it now imports easily.

Damien_The_Unbeliever
  • 234,701
  • 27
  • 340
  • 448