2

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?

  • 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_richie Mar 15 at 10:43
  • how are you importing it into excel ? – PeterH Mar 15 at 10:45
  • @PeterH I'm not, it's too big, hence the question. – dumbledad Mar 15 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 – PeterH Mar 15 at 10:47
2

I have only tried this with a CSV that is way smaller than 70 mill rows.

Go to data - get data, choose your CSV.

enter image description here

Click on the EDIT button to load it into power query editor. Under the Add Column Head add an index column.

enter image description here

Then filter on this column, using number less than, and choose your desired number of ROWS.

enter image description here

Return to the Home tab, and click close and load.

enter image description here

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 – dumbledad Mar 15 at 11:28
  • glad it worked for, hate to think what a 70 million row data set would be like to work with – PeterH Mar 15 at 12:31

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.