4

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?

2
  • 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. Dec 19 '14 at 20:21
5

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!

3

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.

1

I'd suggest you look at TextQ (disclaimer - I'm its developer). It can import a big CSV file and allows you to manage its schema/structure:

  • parse date and numbers in various formats;
  • rename or hide columns;
  • index columns for faster queries;

You can filter rows via a UI Query Builder.

For advanced users, TextQ supports SQL - select, join, group by, etc. The UI Query Builder can convert to SQL with a single button click.

You can export any query result (filtered rows) to a CSV file, which can be imported in MS Excel or others.

You can get it from the Mac App Store or Microsoft Store (coming soon).

New contributor
Geo Systems is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.

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.