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)?
Best Answer
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