3

I am working with a CSV file that is approximately 500 MB.

When I open this file in Excel, the wrong data is displayed under the column headers. I suspect this may be due to the large size of the CSV file involved. Opening with the EM editor neither allows me to align the column data nor to remove entire columns from the CSV file.

What is the best way to use this CSV to align the data in columns and to delete columns that are not of interest?

  • 1
    possible duplicate of How can I work with a 4GB csv file? – philshem Dec 19 '14 at 19:34
  • The editors may be getting confused due to internal commas and quotation marks in your data. It will try to delimit appropriately, but with a lot of (text?) data it may be finding commas and quotes that are causing it to go "off". (Typically comma delimited files will rely on a combination of comma separation and quoting strings that have internal commas.) I am not familiar with that particular data file, and there is no sample, but that is my guess. If this is in fact the issue, it will be very hard to use a programmatic solution to solve it, since those too will rely on a standard delimiter. – Megan Squire Dec 19 '14 at 20:21
4

The csvkit python library is great for transforming big csv datasets in the style of a unix command line tool (like sed). It has many small utilities that do one thing well each so you can compose them in helpful ways. In your case, csvcut can extract certain columns from a csv.

From their docs:

Extract columns named “TOTAL” and “State Name” (in that order): 

$ csvcut -c TOTAL,"State Name" examples/realdata/FY09_EDU_Recipients_by_State.csv

Good luck!

2

Try first managing a smaller version of this file first. And this is where the classic techniques never die.

Run cmd.exe which will be available on Windows

Navigate to the directory containing the file (you can do this by typing "pushd" and then right-clicking and pasting the directory).

Use the head command to create a version of the file that will contain a specified number of lines from the original file. (example: head -n1000 original.txt > header.txt)

In this example the -n1000 specifies the number of lines taken from the original file, which in this case is 1000 because I specified 1000; likewise, if you want only 100 lines, change it to -n100.

The original.txt would be the name of your original file which be the name and then the extension which is .csv.

The ">" symbol specifies to send the information to the new file, which in this example I've named header.txt, which will write the new file in the same directory.

This should make it MUCH easier to handle with and explore this .csv.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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