1

I wonder why is there a difference for both of the csv files mentioned below.

I got a table.csv file in Linux, after I send it out as attachment in mail then save and open it in my windows excel application, it was messy, the columns were not aligned with data scattered around.

But when I open an excel file and go to "Data" tab and click on "From text" from "get external data" category, then select the same csv file I used just now, then the output in table form is very well aligned and clean.

My endgoal is to get the nicely aligned output directly from linux to window excel by attaching it in mail without taking extra step to go excel and select the get external data from text.

I can't really figure out why the two same csv file has such difference, to overcome this issue, do I have to write a script to manipulate the excel?

Thank you for any advice. I am really curious about this.

first the data is only in the first column (colA)

second
this is very well aligned in each column

comparison

Improve this question
12
  • 4
    What application are you using to open it in Windows when it looks scrambled? You describe Excel handling the file fine, and your goal is to open it in Excel, so I'm missing what the problem is. Windows uses CR+LF for end-of-line while Linux uses just LF. If this is being prepared in Linux, any chance that the end-of-line is what's making the file appear scrambled?
    – fixer1234
    Commented Apr 1, 2017 at 3:46
  • Hi @fixer1234, both are open using excel application in windows. Thank you
    – ExcelNovice
    Commented Apr 1, 2017 at 5:27
  • It was mentioned in my endgoal? hmm.. I want to just click on the attachment file in my mail and it would be nicely formatted. Without extra step to open an excel first, then click those buttons. Thank you
    – ExcelNovice
    Commented Apr 1, 2017 at 5:38
  • The first example is putting all four values in a single cell. It looks like a delimiter problem. Maybe it is tab delimited instead of comma delimited and Excel isn't guessing correctly. The email attachment shouldn't change anything, but maybe getting it into an email wrapper in Linus and extracting it in Windows is affecting it. Try opening both files in a hex editor and compare the first few records.
    – fixer1234
    Commented Apr 1, 2017 at 5:40
  • Hi @fixer1234, I used Notepad++ to open both files. Surprisingly, the first one shows very well aligned data in invisible columns. But the second's shows garbled text. I'm not sure if it's had to do with the fact that I get the external data then save the file in .xlsx extension before I open it in notepad++. Thank you
    – ExcelNovice
    Commented Apr 1, 2017 at 6:14

1 Answer 1

Reset to default
1

As fixer1234 mentioned, this most likely has to do with line endings in Linux vs Windows. It depends on what the file is prepared in. There's several questions on stack overflow and other sites asking how to fix this. I imagine, if you save the file in windows, and then send it back to linux, and open it in vi, you'll see strange characters on that end as well (^M at the end of lines).

Without more details on what you're seeing, I can't really tell you how to fix it, but there's a few examples of how to fix it on one side or the other here or here

If you provide a detailed example of what's happening, I can give a much clearer example on how to fix your problem.

Improve this answer
2
  • Hi @trueCamelType, I have attached some screenshot for the worksheet, hope you could have a look at it. I'm not sure if I can fixed this by writing perl or shell script (to execute it in linux-where my original csv file is from), I wouldn't mind, since I have to add an extra column "Overall" to calculate the average marks. Thank you
    – ExcelNovice
    Commented Apr 1, 2017 at 5:31
  • @ExcelNovice If the only problem you are having, is when you default open a document in excel, that it isn't formatting correctly, that's not a problem at all. Excel knows which column things should go in by being separated by commas (.csv files). If you just put commas between he value, it will put a new column instead of a tab. Without commas, all of it being in a single column is the expected outcome.
    – trueCamelType
    Commented Apr 2, 2017 at 2:50

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .