2

I'm trying to import a customer csv file via SSMS Import Wizard, file contains 1 million rows and I'm having trouble importing where the field has extra quotes e.g. the file has ben populated freehand so could contain anything.

Name, Address
"John","Liverpool"
"Paul",""New York"""
"Ringo","London|,"
"George","India"""

Before I press on looking into SSMS should SSMS 2016 handle this now or do I have to do in SSIS, it is a one off load to check something?

Hadi
  • 36,233
  • 13
  • 65
  • 124
Roger Clerkwell
  • 406
  • 4
  • 19

1 Answers1

0

In SSMS Import/Export Wizard, when configuring the Flat File Source you have to set:

  • Text Qualifier = "
  • Column Delimiter = ,

This will import the file as the following:

Name    Address

John    Liverpool
Paul    "New York""
Ringo   London|,
George  India

The remaining double quotes must be removed after import is done using SQL, or you have to create an SSIS package manually using Visual Studio and add some transformation to clean data.

Hadi
  • 36,233
  • 13
  • 65
  • 124
  • Thanks Hadi, I'll do above but leave SSIS clean up as I need to leave the original "" in place as this data is entered by customers so if they enter Jo""hn"" I'm not allowed to transform to John has to be imported as Jo""hn"" in db, horrid! – Roger Clerkwell Jun 19 '19 at 08:15