CODEWITHSUNDEEP
c#sqldatareader
krb010301
krb010301

Reputation: 33

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

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);
}

Upvotes: 3

Views: 527

Answers (3)

David C
David C

Reputation: 3810

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);
 }

Upvotes: 1

FLOOD racer
FLOOD racer

Reputation: 193

Try putting the values within double quotes:

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

Upvotes: 2

Luis Tellez
Luis Tellez

Reputation: 2973

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

Upvotes: 3

Related Questions