How to merge two excel files?

apugachev_mba2015

Hi,
I know the question has already been asked, but as of now there was no clear answer which I could follow. Hence after hours of trial & error and internet research, I call for community help.

My objective is to merge two external excel files with exactly the same structure. I have used directory function with dynamic input, yet to know avail. Can please someone explain it on a "dummy" level?

- Directory: select the folder, file specification .xlsx
- Dynamic input: I point template to the same folder with *.xlsx file extansion, in SQL editor I put `Sheet1$`
- Modify SQL: Update WHERE clause

My result:
- Out of two files, only one is added for some reason twice.
- The 2nd file is skipped with error that "schema" is different. I took the first file, just renamed it and pasted new values in it with exactly the same structure. After one run the data was appended correctly, but then the "schema" error. Returned.

Thank you very much in advance. If you know a good place, where I can read about it, I would be curious to know besides how to do the same manipulation but with different sheet structure in the files.

Best,
Artyom

Join

Accepted answers

kane_glendenning

Hi Artyom,

I took a look at this, and a couple of things to note.

In the Dynamic Input tool, try using a single file as the template instead of wildcards. I usually produce a 3 line sample of my data and use that as the sample but if your files are only a couple of MB then you can just use one of your files as the sample.
You have selected to update the WHERE clause in the SQL, however, there is no real WHERE clause specified.

I usually just use Replace Entire Filepath, however it can always be a little funny with Excel files. In the situation that you have posted, I would recommend putting a formula tool after the input specifying a new filename with the formula:
NewFileName = [FileName]+"|'Tableau$'"

rtaImage (0).png

and then configure the Dynamic Input tool with Modify SQL Query:

Another resource to look at would be Ned's blog on XLSX Wildcards. It refers to a tool that Ned Harding built (Included in CReW Macro Pack or available separate from Inspiring Ingenuity). The tool that is referred to in the aforementioned blog post will bring in multiple files from a directory, and essentially Union them.

kane_glendenning

Hi Artyom,

I have packaged my workflow on your sample data using the headings as headings not data:

https://drive.google.com/folderview?id=0B-hV6uaPAkZnVFMtUDJPMURhdFU&usp=sharing

As for the rows, if you're talking about the Null() rows, I don't have an answer, I would just filter them out, it's easier than investigating...

Let me know if you can't access

All comments

apugachev_mba2015

I have recreated the flow with different files and it seemed to work.However, I could solve my original challenge only by treating the column names as data and working out to clean an extra row afterwards. If you by chance know why, I get a schema error by including the titles in dynamic input, I would very much appreciate your input. So would I welcome some information on working with multiple files & multiples sheets.

Thank you.

I am posting the result here, because I saw a few people asking the same question. The link to the files on google drive: https://drive.google.com/folderview?id=0B0IJhnsVZlMifkRhZW1rM0NWaFhlU09yMjQ1bGdESXhrNW85bFNyLVdINHBWNjNkR1lTTlE&usp=sharing

kane_glendenning

Hi Artyom,

I took a look at this, and a couple of things to note.

In the Dynamic Input tool, try using a single file as the template instead of wildcards. I usually produce a 3 line sample of my data and use that as the sample but if your files are only a couple of MB then you can just use one of your files as the sample.
You have selected to update the WHERE clause in the SQL, however, there is no real WHERE clause specified.

apugachev_mba2015

Hi Kane,

Thank you for your reply, your solution is clearly more elegant. I have just tried it on my original dataset and it indeed eliminated the issue with appending twice the same file. So I could get rid off the Unique tool in my flow. However, I still have to major problems, one of them I can't recreate with the set I posted for some reason:

1) Even when I delete some rows from the Excel files, they magically reappear in Alteryx.
2) If I do not treat the first row as data, I receive incongruent schema error. I have tried all thinkable to me tricks with no success.

Do you have an idea how to address them?

kane_glendenning

apugachev_mba2015

Hi Kane,

I have pasted my original files into your solution workflow and keep getting the same schema mistake. The only way I can solve it is by treating the first row as data. But I agree with you, it is easier to build a workaround than to investigate.

Issue #1 - null rows they were, thanks!

If you don't have an idea about the titles, let's just close it. It is minor. Thanks you for helping me out!

commana

Greetings Kane,

The workflow seems to pickup the same file thrice for me. I have three files that I want to merge.

Thank you so much for your solution.

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15865

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703