Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I have a text file with tab delimited columns, that is longer than can be imported into an Excel worksheet (> 1,000,000 rows). I'm trying to figure out how to create an Excel pivot table from the data in this text file.

I'm not very familiar with pivot tables in excel, but when I try to create one with a text file as the data source (Insert -> PivotTable -> Use an external data source) I'm able to select the text file, but then a dialogue pops up saying;

The type of connection selected cannot be used to create a PivotTable.

I would just import the data into excel, then create the pivot table (and the text file import just fine), but it is longer than can fit in a single worksheet.

Is there a method to get the data from the text file to an Excel pivot table, in a way that is easily update-able (as the text file will change periodically in both data and size)?

share|improve this question
Have you tried a 64 bit edition of Excel? It has a larger number of rows –  Akash May 24 '12 at 18:53
Also, refer to superuser.com/questions/69340/… –  Akash May 24 '12 at 18:54
@Akash I need it to work on different machines, and 32-bit is standard for MS Office, so I can't use 64-bit. and thanks for the link, but it's not quite what I'm looking for. –  scae May 24 '12 at 19:19
Not that this answers your question.... But I think you would be better served uploading to a DB and connect to it via excel to extract the data. Access is the most use friendly... but open source options exist if purchasing more software is not feasible –  Pynner May 26 '12 at 0:44
@Pynner That was actually what I was doing before, but for several reasons I want to get away from using Access (one reason being that it was taking a long time to write to the Access database from the program that generated data and writing the same amount of data to a text file is extremely quick), so I wanted to see how importing from a text file worked in excel to see if it was worth it to use text files instead of Access. –  scae May 30 '12 at 17:59
add comment

1 Answer

up vote 2 down vote accepted

Ok, so it is possible to create a pivot table from a large text file directly (without first importing the text file). The following links outline how to do it...

Almost exactly my question: https://groups.google.com/forum/?fromgroups#!topic/microsoft.public.excel.datamap/zf0U-zSeypQ

More Info: http://www.exceluser.com/explore/msquery2_1.htm

share|improve this answer
add comment

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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