Import CSV to SQL server 2008 or Excel with delimiter comma and data also contain comma

Question

I have to import a csv file to excel or directly to SQL Server database.

Here is the data format

1. columns are separated by comma (,).
2. Strings are enclosed between ~ and ~ which may contain comma (,)
3. When I try to import into excel by using delimiter Comma(,) it creates extra columns where ModelName contain comma in it.

Here is CSV file some lines, check the last item "~Express, Commercial Cutaway~" it contains comma in it.

~ChromeStyleID~,~Country~,~Year~,~DivisionName~,~SubdivisionName~,~ModelName~ 349645,~US~,2013,~GMC~,~GMC Pickups~,~Sierra 2500HD~

349646,~US~,2013,~GMC~,~GMC Pickups~,~Sierra 2500HD~

349649,~US~,2013,~Chevrolet~,~Chevy Specialty Vehicles~,~Express, Commercial Cutaway~

So how to import it into sql or excel.

Answer 1

Importing into SQL Server could go like this.

On your Import/Export screen, make sure that you specify "~" as the text qualifier. Check the box that says "Column names in the first data row".

Unfortunately your column names will still contain the tilde, but this you can edit by hand.

As for Excel, you are better off replacing your text qualifier to double quotes or something more standard like a single quote. In fact Excel only allows you these two options, perhaps there is a way of changing that, but I don't know how.