Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I am banging my head on this one.

I have an Excel sheet with 994 lines, but when I export to CSV I get 1880 lines in text format!

And of course, now that I am trying to recreate the original Excel file, I get 1880 lines.

Anyone can tell me what might be going on here?


I just found the source of the problem:

The original file contains carriage return inside the cells, and when exported to text format, these carriage returns are converted to new lines.

Now the fun part: the text file has been translated and I need to re-import the translated file into Excel, recreating the * format. Anyone knows how I could do that?

share|improve this question
    
Can you edit your question to tell us what the additional 994 lines in the CSV say? –  Chris Ting May 25 '11 at 13:21
    
@Chris Ting Additional 886 lines surely? My guess is that there is some data elsewhere on the sheet, possibly just a space character. –  James May 25 '11 at 13:33
    
@Chris: I wish, but I have no clue either, it's in Latvian so it's pretty hard to figure out. All I know is that the first line and the last line of the file are identical in both the excel file and the text file. I can't post the content of the file because of the confidentiality agreement. –  Sylverdrag May 25 '11 at 13:34
    
@James: It doesn't look like no space character to me. –  Sylverdrag May 25 '11 at 13:35
    
Are those extra lines blank, or do they contain duplicates? Have you tried setting the encoding to UTF-8? –  Ellesa May 25 '11 at 13:36
show 1 more comment

1 Answer 1

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.