Import csv file via SSMS where text fields contain extra quotes

Question

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?

Answer 1

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.