In a previous recent post, I talked about how to properly export data to respect the CSV format, and allow opening in Excel CSV files with special characters.
Once we've given our customer the CSV file, and they've filled it with data, usually comes importing the data back to our application, so... here it is a simple C# implementation of a method that given a string (usually obtained with a simple file ReadLine()) , will prepare it for Split() parsing.
First the code:
private static string PrepareLineForParsing(string Data,char InputSeparator, string OutputSeparator)
{
StringBuilder replacedData;
int index = 0;
bool flagQuoted = false;
bool previousWasQuote = false;
replacedData = new StringBuilder(Data.Length);
if (Data.Length == 0)
return String.Empty;
for (index=0; index < Data.Length; index++)
{
if (Data[index] == InputSeparator)
{
//If we're quoting, ignore separator
if (!flagQuoted)
replacedData.Append(OutputSeparator);
else
replacedData.Append(Data[index]);
previousWasQuote = false;
}
else
{
if (Data[index] == '\"')
{
//Double quote with something inside means skip the block
//Only write a single double quote if we're inside a double quoted block and have '""'
flagQuoted = !flagQuoted;
if (previousWasQuote)
{
previousWasQuote = false;
if (flagQuoted)
replacedData.Append("\"");
}
else
previousWasQuote = true ;
}
else
{
replacedData.Append(Data[index]);
previousWasQuote = false;
}
}
}
return replacedData.ToString();
}
Then the notes... Of which there are none as the code is self-explaining... just go character by character detecting when we have a new field (with or without double quotes) and when we have two double quotes (meaning a single double quote as result).