Import and Export Wizard - remove double quotes from csv data

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SQL Server 2005 General Discussion » Import and Export Wizard - remove double...

Import and Export Wizard - remove double quotes from csv data

Rate Topic

Display Mode

Topic Options

Author

Message

_simon_

Posted Tuesday, May 08, 2012 6:16 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, May 06, 2013 3:52 AM
Points: 156, Visits: 648

I am frequently given csv files in following format:

"Id", "Col1", "Col2", "Col3"
"1", "x1", "y1", "z1"
"2", "x2", "y2", "z2"

The column names are in the first row. Two questions:
- is there any way to automatically remove double quotes for the names of the columns? I can remove them manualy (in the Advanced module of the Flat File Source, but this is tedious and I have a lot of columns).
- how can I remove double quotes for data? I can set column delimiter to "{,}", but this leaves first and last double qoutes in my data, like this:

"1, x1, y1, z1"
"2, x2, y2, z2"

Edit - what is the best way to somehow save the settings of the current import so that I can use it in the future also?

Post #1296395

anthony.green

Posted Tuesday, May 08, 2012 6:21 AM

SSCertifiable

Group: General Forum Members
Last Login: Friday, April 12, 2013 3:51 AM
Points: 5,075, Visits: 4,831

use the text qualifier on the import screen and set it to " this should then remove the " when you look at the csv in the preview

Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1296399

_simon_

Posted Tuesday, May 08, 2012 6:26 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, May 06, 2013 3:52 AM
Points: 156, Visits: 648

Wow, how could I miss this one :) Thanks, that's exactly what I needed.

One question remains - what is the best way to somehow save the settings of the current import so that I can use it in the future also?

Post #1296404

anthony.green

Posted Tuesday, May 08, 2012 6:29 AM

SSCertifiable

Group: General Forum Members
Last Login: Friday, April 12, 2013 3:51 AM
Points: 5,075, Visits: 4,831

will the filename be exactly the same and in the same directory every time the import needs to be run? if so one of the last screens is run immediatly and save package, just select save package and then it creates the SSIS dtsx package which you can save in SQL and just run it via SSIS in SSMS or create a job which executes it.

if the name and directory is different every time then you will need to do it manually each time or write something which ranems and moves the file to the right name and location.

Post #1296406

altafamber

Posted Sunday, February 10, 2013 12:55 AM

Forum Newbie

Group: General Forum Members
Last Login: Sunday, February 10, 2013 12:53 AM
Points: 1, Visits: 0

Text qualifier remove " from data but not from column names. How can be " removed from the column names.?

Post #1418088

« Prev Topic | Next Topic »

Permissions