Replace comma with semicolon in between quotas

Question

I have a csv file from which i am getting the data into a table. Example: "ABC",1,"Apple" The requirement is that the strings will be inside the quotas " " and integer will be without quotes. The above line will split into three columns. i am using stream reader class to split the line into columns using line.split(','). It was working fine unfortunately i got a record in a file where there is a comma in between the string quotes like these "ABC,DEF,ghi",2,"Orange". So instead of 3 columns now they are acting as five columns and all the conversion are failing. Can any one help me in writing the script in C# which will replace the comma between the quotas into semicolon and don't touch the comma in between the columns.

Thank you.

how do you escape `"` in those strings, or can you expect them to not contain `"`? — Nappy, Jan 18 '12 at 23:56
is the file is subject to change you can may want to create an enum that defines the file header layout.. and check for extra columns I've done this on my last project otherwise you will have to check the length of each string after doing the split becasue ABC,DEF,ghi" should split into 3 columns instead of 1 is can you follow what I am talking about doing.. then you would have to concat the DEF ghi as part of ABC — MethodMan, Jan 18 '12 at 23:59

score 3 · Answer 1 · answered Jan 18 '12 at 23:53

3

Looks like your CSV might be RFC 4180 compliant. Use an RFC 4180 parser. Many of those exist. Check this one: http://www.codeproject.com/KB/database/CsvReader.aspx

answered Jan 18 '12 at 23:53

yankee

32,900
12
87
147

i am not using any parser i am using C# custom script in script component in ssis – lch Jan 18 '12 at 23:57
so why does this have a VB tag if you are using C# – ChrisPadgham Jan 19 '12 at 03:42

score 2 · Answer 2 · edited May 23 '17 at 11:55

2

This question is answered here: Java: splitting a comma-separated string but ignoring commas in quotes

You could use the same regular expression ",(?=([^\"]*\"[^\"]*\")*[^\"]*$)" and use the C# method Regex.Split().

edited May 23 '17 at 11:55

Community

1
1

answered Jan 18 '12 at 23:56

Sean Dawson

4,476
1
23
32

hi i dont have Regex class which using statement i need to use to get i am a beginner in C# please bare with me – lch Jan 19 '12 at 00:33
You do have a regular expression class. You would need to add a `using System.Text.RegularExpressions` – billinkc Jan 19 '12 at 01:27

Replace comma with semicolon in between quotas

2 Answers2