3 Replies Latest reply on Jul 22, 2016 3:23 PM by Dmitry Chirkov

Collating lots (40+) of big excel files (500K+ rows) into a single source…

Michael Carpenter Jul 19, 2016 3:47 AM

I have some (currently 8, but eventually 40) big Excel files I need to collate data from. Each file has...

One big main data sheet (with 500,000+ rows, called DATA , which is the data I’m interested in)
Two other sheets with some “look-up” information (which I currently join to the DATA sheet on the Tableau import screen)

I currently import and extract one file, then use “Data/Extract/Append from file…” to append the rest one at a time. This takes a long time (3-8 mins per file) and is becoming a pain as the number of files increases.

Each excel file is around 50MB so I have to keep them separately (so I don’t think the new ‘union’ facility will help).

I hoping there’s a better way to do this – any thoughts..?

Also – am I going to run into problems as my number of import files increases (the extract is currently about 120MB)

Thanks

Michael

1. Re: Collating lots (40+) of big excel files (500K+ rows) into a single source…

Dmitry Chirkov Jul 22, 2016 1:17 AM (in response to Michael Carpenter)
Just as an experiment I'd try legacy Excel connector with Custom SQL (see my old post here)

SELECT [DATA$].[ID] AS [ID], [DATA$].[Name] AS [Name] FROM [DATA$] UNION ALL SELECT [DATA$].[ID] AS [ID], [DATA$].[Name] AS [Name] FROM [C:\files\another-file.xlsx].[DATA$] UNION ALL ...

It will probably be just as slow but at least you can fire up extract creation and go get lunch.
Like Show 0 Likes(0)

Actions
2. Re: Collating lots (40+) of big excel files (500K+ rows) into a single source…

Michael Carpenter Jul 22, 2016 2:42 AM (in response to Dmitry Chirkov)

Thanks for getting back to me. Can you tell me how I get to the option to
enter a custom SQL statement - I can't see that as an option on the
'Connect .. to a file' screen?

Michael

On 22 July 2016 at 09:18, Dmitry Chirkov <tableaucommunity@tableau.com>
Like Show 0 Likes(0)

Actions
3. Re: Collating lots (40+) of big excel files (500K+ rows) into a single source…

Dmitry Chirkov Jul 22, 2016 3:23 PM (in response to Michael Carpenter)

It's an option you pick when you connect to the file for the first time. Available on Windows only though.
Like Show 0 Likes(0)

Actions

Go to original post

Tableau Community Forums

Collating lots (40+) of big excel files (500K+ rows) into a single source…

1. Re: Collating lots (40+) of big excel files (500K+ rows) into a single source…

2. Re: Collating lots (40+) of big excel files (500K+ rows) into a single source…

3. Re: Collating lots (40+) of big excel files (500K+ rows) into a single source…

Actions

More Like This