I have a large CSV file with approximately 70,000,000 rows. I have read how to import it into Excel as a data model and then pivot table here. Is there a way in Excel to just load a certain number of rows directly into a sheet, akin to SQLs TOP?
4
-
You'll most likely need some VBA with an inputbox() asking you how many to import, then use FileSystemObject to read the data in from the .CSV. This might get you started stackoverflow.com/questions/1719342/…– spikey_richieMar 15, 2019 at 10:43
-
how are you importing it into excel ?– PeterHMar 15, 2019 at 10:45
-
@PeterH I'm not, it's too big, hence the question.– dumbledadMar 15, 2019 at 10:46
-
@dumbledad apologies I miss read your question, I will look into this now, you should be able to truncate data in the power query editor– PeterHMar 15, 2019 at 10:47
Add a comment
|
2 Answers
I have only tried this with a CSV that is way smaller than 70 mill rows.
Go to data - get data, choose your CSV.
Click on the EDIT button to load it into power query editor. Under the Add Column Head add an index column.
Then filter on this column, using number less than, and choose your desired number of ROWS.
Return to the Home tab, and click close and load.
AS I say I have never used this with a CSV of that size so let me know how you get on.
-
It worked :) And was even easier in the version of Excel I'm using. Instead of 'edit' I get 'transform data' and then there's a drop-down called 'keep rows' and one of the options under that is to specify the number of rows to keep. Perfect Mar 15, 2019 at 11:28
-
glad it worked for, hate to think what a 70 million row data set would be like to work with– PeterHMar 15, 2019 at 12:31
Check out TextQ (disclaimer - I'm its developer). It can import a big CSV file and allows to query and filter it via a UI Query Builder or SQL (select, join, group by, etc). The UI Builder can convert to SQL with a click.
Query results can be exported to CSV.
You can get it from the Mac App Store or Microsoft Store (soon).
