Can PivotTables Use Delimited Text Files as a Source?

JohnDiddy77
JohnDiddy77 used Ask the Experts™
on
Hi Experts,
Quick question.

I’m currently developing a report model using PivotTables with VBA to hide the filter functionality as majority of end-users are uncomfortable with PivotTables, but I must include drill-down ability for the more experienced end users.

End-Users will typically have low end machines and poor internet connections. So, source that is transferred as tiny Pipe Delimited Text files that are downloaded from a remote server.
Is it possible for Excel 2003 to access these text files as a direct source for PivotTables? At present, the data lives a worksheet and I'm aiming to keep filesize to the absolutely minimum.
Any thoughts?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2011
Awarded 2010

Commented:
Hello,

a few tests with pipe delimited TXT files shows that Excel does not accept this format as a valid connection to create a pivot table. Both Excel 2003 and 2010 allow external data sources as a pivot table source, and you can select the txt file. Excel even starts the wizard for data parsing, but then throws an error.

So, as far as I have seen it, the answer is: no, you cannot use a pipe delimited text file as a direct data source for a pivot table.

cheers, teylyn
Most Valuable Expert 2011
Top Expert 2011

Commented:
It is possible to use pipe delimited files but csv would be simpler if it's an option.

Author

Commented:
@Teylyn: I don't want any form of wizards to pop up. The design needs to be completely seamless so the end-user doesn't know what's happening in the background. I intend to disable some of the filters to make the report "easy" for beginner Excel users.

@Rorya: 1) CSV is not an option. The source file is generated by C# code and the development team chose Pipe Delimited as standard. 2) What methods would I use to get the PivotTable to use Pipe Delimited without wizards, error messages etc... i.e. "seamlessly"
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Most Valuable Expert 2011
Top Expert 2011

Commented:
Are the users creating the pivots or will they be prebuilt?

Author

Commented:
@ Rorya: Prebuilt.

DATE & ACCOUNT will be captured from a UserForm. This will retrieve a subset of data from the Server and download the Pipe Delimited Text File.

The text file will then populate the PivotTable.
Most Valuable Expert 2011
Top Expert 2011
Commented:
In that case you should just be able to create the pivot table using the external data option, choosing the MS Text driver, and then you need to uncheck the Use Current Directory option, specify a location, press the Options>> button and then define the format you want including the pipe separator and field types. This will create a schema.ini file in that folder for the pivot to use.

Author

Commented:
Excellent! Thank you very much...

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial