how to read a sql column containing commas as one field with c#

Question

I have a sql08 database table which I am reading with C#. One of the columns (College) contains data where some data has a comma: Miami, FL or Miami, OH, and some don't: USC. My output .csv shifts data from those comma containing rows right one additional column. I would like my output to contain Miami,FL as one field: |Miami,FL| not |Miami| |FL|

myWriter.WriteLine("Player, Position, Current Team, College");
SqlDataReader myReader = null;
SqlCommand myCommand = new SqlCommand("Select * FROM FootballRoster", myConnection);

myReader = myCommand.ExecuteReader();
while (myReader.Read())
{
    string myPlayer = myReader.GetString(0);
    string myPosition = myReader.GetString(1);
    string myCurTeam = myReader.GetString(2);
    string myCollege = myReader.GetString(3);

    myWriter.WriteLine("{0}, {1}, {2}, {3},", myPlayer, myPosition, myCurTeam, myCollege);
}

Use another delimiter. – Hamlet Hakobyan Feb 21 '13 at 16:28 — Hamlet Hakobyan, Feb 21 '13 at 16:28

score 3 · Answer 1 · edited Oct 07 '21 at 05:59

3

You can find the answer in here

Dealing with commas in a CSV file

Basically you need to escape the ',' characters. you can use '' to do it

Here you have the spec for CSV format

https://www.rfc-editor.org/rfc/rfc4180

edited Oct 07 '21 at 05:59

Community

1
1

answered Feb 21 '13 at 16:27

Luis Tellez

2,785
1
20
28

score 2 · Accepted Answer · answered Feb 21 '13 at 16:27

2

Try putting the values within double quotes:

myWriter.WriteLine("\"{0}\", \"{1}\", \"{2}\", \"{3}\",", myPlayer, myPosition, myCurTeam, myCollege);

answered Feb 21 '13 at 16:27

FLOOD racer

195
9

score 1 · Answer 3 · answered Feb 21 '13 at 16:29

Generally in a .CSV, if the string contains a comma, you wrap the field in double quotes.

So Miami, FL becomes "Miami, FL", in the .CSV file.

So you could do something like...

string myCollege = "";

if(myReader.GetString(3).Contains(","))
 {
    myCollege = "\"" + myReader.GetString(3) + "\"";
 }
else
 {
    myCollege = myReader.GetString(3);
 }

how to read a sql column containing commas as one field with c#

3 Answers3