Remove Commas from CSV file

Question

Remove Commas from CSV file

simon phoenix-479217

SSCommitted

Points: 1573
More actions
March 12, 2009 at 4:18 pm

#206879

Hi
Guys
I would like to delete all the commas from the CSV file, is there a quick way to achieve using VB.Net
and would like to use inside SSIS
Thanks
Simon

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

Jack Corbett SSC Guru Points: 184394 More actions · Answer 1

What is the purpose of removing the commas? Are you replacing with another delimiter? Do you mean commas contained in the delimited columns? Like this example:

Name, Age, Street,City,State

"Jones, James", 35, 12 This Road, Franklin, TN

Do you want to move the data into another file or a database table?

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

simon phoenix-479217 SSCommitted Points: 1573 More actions · Answer 2

Hi

Jack

Thanks for the quick response and the reason why i post in both the forum was because i was not sure where it will fit and i was in quite need of the soln

For the example given by you, when i use the SSIS Flat file connection adapter to import it to table in SQL database, that extra commma like the one in Name "Jones,James" is causing problem and SQL is reading as separate column as soon as it sees that comma, that is the problem

Name, Age, Street,City,State

"Jones, James", 35, 12 This Road, Franklin, TN

simon

Paul_J Hall of Fame Points: 3698 More actions · Answer 3

How large can the file be?

Will the file contain quotations around fields and if so, do you want to preserve the commas within the quotes?

e.g. value1,value2,"This is value3, hi!",value4

You could read the file line by line and strip the commas using a simple string replace function - replacing the commas with an empty string.

If the file is small, you could do the same by reading the whole file into a string and then doing the replace.

If you need to deal with the quotes then you need smarter logic. Possibly with the use of regular expressions.

simon phoenix-479217 SSCommitted Points: 1573 More actions · Answer 4

yes paul , i want to remove the comma inside the quoatiation

Thanks

Simon

Paul_J Hall of Fame Points: 3698 More actions · Answer 5

Oh and as far as I know SSIS (2005 anyway) doesn't deal with commas within quotes by default. You can write code in a script task obviously to do all this.

Jack Corbett SSC Guru Points: 184394 More actions · Answer 6

Are there text qualifiers? If not, do you have any control or influence on the creation of the flat file to have them added?

Can you attach a file similar to what you are having and issue with?

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Jack Corbett SSC Guru Points: 184394 More actions · Answer 7

simon phoenix (3/12/2009)
yes paul , i want to remove the comma inside the quoatiation
Thanks
Simon

If the data has text qualifiers like quotes all you need to do it set the text qualifier property of the flat file connection to quotes and SSIS will ignore commas in the quoted columns.

So in the example I gave the name column will be maintained as is.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

simon phoenix-479217 SSCommitted Points: 1573 More actions · Answer 8

Hi

Jack

I have upload the attachment by saving it xls format because the forum did not let me upload the csv format and you can look at the office name field where it says Galveston,Tx that is causing the problem

thanks

simon

Jack Corbett SSC Guru Points: 184394 More actions · Answer 9

As I said, just set the Text Qualifier property of the Flat File connection to a double-quote and SSIS will ignore commas between double-quotes.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

simon phoenix-479217 SSCommitted Points: 1573 More actions · Answer 10

hi

Jack

I use the double quote as " " in the text qualifier for flat file connection, still did not work

Thanks

simon

Jack Corbett SSC Guru Points: 184394 More actions · Answer 11

You just need to put in a single double-quote "

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Paul_J Hall of Fame Points: 3698 More actions · Answer 12

Can your data ever have quotes within quotes?

e.g. "This is a ""test"""

simon phoenix-479217 SSCommitted Points: 1573 More actions · Answer 13

Hey

Paul

the data in the test.xls is kind of misrepresentation, i saved the original csv file in xls format since the forum did not gave me an option to attach csv file

simon

simon phoenix-479217 SSCommitted Points: 1573 More actions · Answer 14

Hey

Jack

I was using that extra quoatation, after using just one as text qualifier , it worked perfect.

Thanks a lot, i guess sometime we just think too hard and undermine the feature that is already availiable in great tool like SSIS

Thanks

Simion