How to handle delimiter(,) in csv

rucha joshi

Hi I need to transfer data(product details) from one online store to another. So I have exported data from source store in .CSV format. Now I want to create target store compatible .CSV to import to the target store. There are field like: "ProductCode,ProductName,ProductDescription" and so on. So in ProductDescription column if there is comma(,) present in ProductDescription so it goes to the next column from the comma(,). So please tell me that how can I bind whole description as one record for that particular product??

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

Vulpes14y ago
Accepted answer
I don't know whether FileHelpers can cope with this situation or not.

However, one technique you can use is to:

1. Temporarily replace any commas which are embedded in quoted fields by a special character.

2. Remove all the quotes.

3. Split each line using the remaining commas as delimiters.

4. Iterate through the resulting fields and replace the special character with a comma again.

The special character needs to be chosen to be something that you're certain won't appear in the file naturally. One I often use is code point 255.

Here's some example code showing how this technique can be applied to a single line which contains embedded commas:

using System;
using System.Collections.Generic;

class Test
{
static char special = (char)255; // say

static void Main()
{
string s = "a,b,\"ProductCode,ProductName,ProductDescription\",c,d";
string[] fields = ReplaceEmbeddedCommas(s).Replace("\"","").Split(',');
for(int i = 0; i < fields.Length; i++)
{
fields[i] = fields[i].Replace(special, ',');
Console.WriteLine(fields[i]);
}
Console.ReadKey();
}

static string ReplaceEmbeddedCommas(string s)
{
var sb = new System.Text.StringBuilder(s);
bool embedded = false;

for(int i = sb.Length - 1; i >= 0; i--)
{
if (sb[i] == '\"')
{
embedded = !embedded;
}
else if (sb[i] == ',' && embedded)
{
sb.Replace(',', special, i, 1);
}
}
return sb.ToString();
}
}

The output should be:

a
b
ProductCode,ProductName,ProductDescription
c
d
rucha joshi14y ago
I can not use another delimiter.It is default delimiter (,).I want to bind the description in one column.It should not go to next column after delimiter.
Poppa Wallace14y ago
I had all manor of problems with csv files and characters, so I went and used filehelpers. The wizard makes light work and creates the classes and all code you will need

Check it out

http://filehelpers.sourceforge.net/
Good Lcuk

Jay
Vulpes14y ago
When the export the data, can you specify that a different delimiter to comma be used - such as space, colon, pipe etc?