0

The csv file has Id and Name. Some of the Names are composed of the first and last names eg "John, Smith". If you see in db,after inserting in to SQL table the Name are inserted as "John". Could you please suggest how to get full name of the Name if it is ',' seperated?

string filepath = selecteditem.FullName;

using (StreamReader sr = new StreamReader(filepath))
{
  while (sr.Peek() != -1)
  {
    string line = sr.ReadLine();

    string[] value = line.Split(',');

    List<string> lineValues = line.Split(',').ToList();

    conn.Open();

    cmd.CommandText = "insert into
Fermin
  • 34,961
  • 21
  • 83
  • 129
Jorge Silva
  • 19
  • 3
  • 3
    Your code is too simplistic - as you've found quoted values may have commas inside. My suggestion would be to use a pre-built csv parser of which there are many (no I'm not going to suggest one over another one). – 500 - Internal Server Error Dec 18 '19 at 15:49
  • 3
    Well then your CSV is invalid. You can fix your CSV file by escaping the comma, which you can do by wrapping your name value in quotes: `1,"John, Smith"` – nbokmans Dec 18 '19 at 15:49
  • If you can control how the CSV is generated you can use some other character for a separator, for example ; – Anton Mihaylov Dec 18 '19 at 15:50
  • 1
    @500-InternalServerError - some good options for a pre-built parser in this question https://stackoverflow.com/questions/2081418/parsing-csv-files-in-c-with-header – T_Bacon Dec 18 '19 at 16:03
  • [CsvHelper](https://joshclose.github.io/CsvHelper/) is your friend here. Don't parse CSVs by hand, there are too many edge cases. – trailmax Dec 18 '19 at 16:22

2 Answers2

0

The string.Split method has an overload that allows you to control how many splits are returned by the original string, so if your input string is 

string input = "1,John, Smith";
var splits = input.Split(new char[] { ','}, 2, StringSplitOptions.RemoveEmptyEntries);

and you have only two entries in the splits array, the first is the ID, the second is the name.

Steve
  • 213,761
  • 22
  • 232
  • 286
0

If you have access to Excel, open it, save as XLSX

Find and replace all commas within the workbook with a | or other equally obscure character, having first made sure, the obscure character isn't in the sheet to begin with. Save and then re-save as .csv.

In the C# code or directly in the Sql, replace the obscure character with the original comma.

GerryFC
  • 56
  • 6