Click here to Skip to main content
15,879,535 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I am able to escape comma between strings with using double quotes such as strings are:"Laxchaman","Bharath","satrudhan" was brother of Ram.It is working fine in csv file But when i am copy the string from the csv cell to notepad it is showing """Laxchaman"",""Bharath"",""satrudhan"" was brother of Ram". That's why the csv file is not being upload in SQL. I want String must be write in csv file with comma as same as sentences in real form.

What I have tried:

C#
using (DataTable dt = new DataTable())
            {

                dt.Load(reader);
                Console.WriteLine(dt.Rows.Count);  //check its filled 
                StringBuilder sb = new StringBuilder();
                string sbb = "";
                sb.AppendLine(string.Join(",", dt.Columns.Cast<datacolumn>().Select(c => c.ColumnName)));
                sb.Remove(sb.Length - 1, 1);
                foreach (DataRow row in dt.Rows)
                {

                    for (int i = 0; i < dt.Columns.Count; i++)
                    {

                        if (row[i].ToString().Contains(","))
                        {

                            sb.Append(String.Format("\"{0}\",", row[i]));
                        }
                        else
                        {
                            sb.Append(row[i].ToString() + ",");
                        }

                    }



                    sb.AppendLine();

                }

                ArrayList ar = new ArrayList();


                File.WriteAllText(@"D:\ISR\1" + ".csv", sb.ToString());



            }
Posted
Updated 17-Sep-20 20:23pm
v3

1 solution

Basically, you can't do what you are describing.
There are two formats of CSV:
1) Basic, where commas delimit column data:
one column,another column,a final column

This version cannot contain commas within the column data as it will always be taken as a column separator.
2) Advanced, where columns delimit double quoted column datas:
one column,"another column, which contains a comma",a final column

And it's quite possible to mix them up.

But if you want a double quote in a column, you have to escape it or it will be treated as the end of the column. CSV processors do this by doubling the double quotes:
one column,"another column, which ""contains"" a comma",a final column
So your Notepad data:
"""Laxchaman"",""Bharath"",""satrudhan"" was brother of Ram"
is correct for a single row of a single column CSV file.

And this is the data format that SQL expects.

Nowever, your code will generate bad CSV in that there are no line breaks:
"Line 1", """double quoted"" text", Final column
Line 2, "text with a comma, this is allowed", 3
Is what you need for multiple rows.
 
Share this answer
 
v2
Comments
Richard Deeming 18-Sep-20 4:45am    
"Nowever, your code will generate bad CSV in that there are no line breaks:"
Isn't that what the sb.AppendLine(); call is for? :)
OriginalGriff 18-Sep-20 5:18am    
I missed that - coffee level of my blood was probably below minimum - but it's still bad CSV: too many commas!

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900