I finally fixed the problem on my own. Here is the answer for documentation purposes:
Cause:
Some cells contained line breaks. On export to CSV, the line breaks were converted to new lines, which were not restored when the file was re-imported in Excel, resulting in a much larger number of rows than in the original.
Solution:
Write a small application that forces each line to contain the exact same number of TAB characters as the original file. If the line does not contain the expected number of TABs, add the next line to it until the proper number of TABs is reached. Insert a placeholder to mark the places where the internal line breaks were. After processing, open in Excel, check the number of lines, then Find and Replace the placeholder with a line break (Alt+010).
Here is the C# code: It uses 2 richtextbox controls:
public void restoreLines{
int nbTabs = 0;
int nbPrevTabs = 0;
int totalTabs = 0;
int lineNb = 0;
string content = "";
string sSource = rtbSrc.Text;
string[] lines = Regex.Split(sSource, "µ");
foreach (string line in lines)
{
lineNb++;
nbTabs = line.Length - line.Replace("\t", "").Length;
totalTabs = nbPrevTabs + nbTabs;
if (totalTabs == 15)
{
content += line.TrimEnd() + "##µ##";
nbTabs = 0;
nbPrevTabs = 0;
totalTabs = 0;
}
else if (totalTabs > 15)
{
MessageBox.Show("Line #" + lineNb + " contains " + totalTabs + " tabs");
break;
}
else
{
content += line.TrimEnd() + "##InnerCRLF##";
nbPrevTabs += nbTabs;
nbTabs = 0;
}
}
rtbRTF.Text = content;
}
This is obviously a quick and dirty solution, but it does the job and can be relatively easily be adapted to handle other files suffering from the same problem.
