6

I have some external csv/txt files and I'd like to use them for a pivot table. However, after I selected my csv file as the external data source, at the end of the guided procedure (header, seperators, etc.) Excel throws an error saying something along the lines of: it's impossible to use the selected type of connection for a pivot table.

Now, I know how to do it with another excel/db table - here it would come very handy to use a csv/txt file. Can this be done natively, without external plugins?

ThinkTank
  • 194
  • 1
  • 7

1 Answers1

4

Use the keyboard shortcut Alt, D, P (not all at once like Alt+D+P, but press each one seperatly). This brings up the old-style pivot table wizard.

Select External Data Source

pivot table wizard

Click Get Data

get data

Choose and click OK.

new data source

Name your data source and choose Microsoft Access Text Driver

choose text driver

Click Connect, uncheck Use Current Directory (unless that's what you want), and Select the Directory you want.

enter image description here

If you don't identify the file when you get back to the "Select a default table..." text box, you'll get prompted to select one.

At that point, click OK back through the dialog boxes. Eventually you'll get thrown into MSQuery where you can build the query you want. From there Return Data to Excel and you can build your pivot table.

Dick Kusleika
  • 32,673
  • 4
  • 52
  • 73
  • Wow, that seemed like a great hint.. unfortunately Alt,D,P (or Alt+D, P) is not working. I'm using Excel 365. Is this option still available on my Excel version? – ThinkTank Dec 02 '19 at 19:25
  • 2
    In Office 365 it's still there. If the keyboard shortcuts don't work, you may also customize your 'quick access toolbar' and add an icon to the 'PivotTabel and PivotChart Wizard' there. – Asger Dec 03 '19 at 14:53