Import csv file via SSMS where text fields contain extra quotes

user_11175896 0
Viewed 63

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?

ssms-2016 ssis-2012 flat-file ssis sql-server
1 Answers

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.

user_70312300
Related