How can I convert this excel file so that it is not only one row?

debugcn Published at Dev

PollPenn

I've got an Excel file, shown in the picture below, and available for download here. What I need is to extract the variables under Item (Column B) and the values in column G. As a start, I tried saving the Excel file as a comma-delimited .csv file, but when I check the number of rows in the Mac OS X Terminal, it tells me that the CSV file is just one row:

$ wc -l Layout.csv
0 Layout.csv

Any idea for why this might be the case?

Excel file

Here is the CSV file opened in a text editor, showing that it has multiple lines:

csv version of file

You can download that file here.

Warren Young

After seeing your CSV output, the problem is clear: you told Excel to use CR line endings, probably because it informed you that they are "Macintosh" style. That is badly outdated information, not true for over a decade now.

There are three main line ending styles:

LF: The style used by Unix and all its primary derivatives, including Mac OS X.
CR: The style chosen by "classic" Mac OS, abandoned by Apple in 2001 with the move to Mac OS X. Since classic Mac OS is the only popular OS to ever use this style, it is almost never seen any more in practice. The CSV file you have linked to is one of these rare examples.
CR+LF: The DOS/Windows style of line ending. Technically, this style is truer to the history of ASCII, and therefore "more correct," but it is uncommon to see outside of the Microsoft world.

The best way to fix this is to get Excel to use LF line endings, that being the native form for OS X, which will make wc and other command line Unix tools happy. But, that is outside the scope of this forum. (Try Super User if you really can't work it out on your own.)

An on-topic Unix command line way to fix it is:

$ tr '\r' '\n' < Layout.csv > Layout-LF.csv

(This is one of those sorts of problems that has about as many different solutions as there are people offering them.)

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at2021-06-24

Comments

0 comments

From Dev

In rails, how to select one specific column's data from a table? (there is only 1 row so I want only a specific result but not an array)

From Dev

How can I adjust this code so it only has one array but you make it into an object (so you can work with it as a JObject in the front end)?

From Dev

I have a number for the last row of the excel sheet. How do I incorporate this with the range function so that I can autofill some data?

From Dev

Related Related

Article