We help IT Professionals succeed at work.

Excel file over 1 million rows to .csv without opening

"Abys" Wallace
"Abys" Wallace asked
on
Hey Experts,

I'm working with Census datasets with over 1 million rows.  The datasets are in Excel format.  I need to convert from Excel to .csv so I can work with them using MySQL.

I do not want to open the Excel file and do a Save As because when I do it ruins the data beyond 1 million rows.  How can I  convert from Excel to .csv without having to open the Excel file?

Thanks
Abys
Comment
Watch Question

View Solution Only

AlanConsultant

Commented:
Is there no way to obtain the dataset in CSV or something similar?

I would hope that most government date would be available that way, but call me naive!

Alan.
mbkitmgrOwner

Commented:
This is one option
Convert XLS To CSV Files - Command Line Able

This too
Convert XLS to CSV on command line

There may also be an option to install MS SQL Express, import the data from Excel then export, though I havent tried this it would require some experimentation.
"Abys" Wallaceself employed

Author

Commented:
I'm retrieving the datasets from the Census Hard To Count site for the state of Virginia and the Tract data is in Excel format.  https://www.censushardtocountmaps2020.us/

Screenshot-2020-01-31-18.05.35.png
"Abys" Wallaceself employed

Author

Commented:
Also, I'm using a Macbook Pro ... I don't have access to Notepad.
mbkitmgrOwner

Commented:
I may be able to convert the data for you.

How do I select an entire state, I can click on a county (I'm from Australia so hope this is the corect name)
"Abys" Wallaceself employed

Author

Commented:
Thank you!   Yes, it's tricky getting the entire state.  To do so, you have to click along the state's border.
mbkitmgrOwner

Commented:
I want to make sure I have the correct data set.  Below is a screen capture, does the file size look correct and does the display of the excel data look correct?

Capture.JPG
mbkitmgrOwner

Commented:
If this isnt the correct data set how big is the excel download you have? (Kbytes/Mbytes/Gbytes)
"Abys" Wallaceself employed

Author

Commented:
Yes, that's the correct dataset
Owner
Commented:
I have converted these to CSV for you (ACS2014-18 TAB and ACS2014-18-PERCENTS Tab).  It has delimited the numerical values with the thousands seperator (, comma) with ""

Make some random checks of the data set to make sure I have all collumns and rows.  Let me know ll is ok or if there are some mods to be made.
ACS2014-18.csv
ACS2014-18-PERCENTS.csv
"Abys" Wallaceself employed

Author

Commented:
Thank you @mbkitmgr!