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
Solved! Go to Solution.
Hi Artyom,
I took a look at this, and a couple of things to note.
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$'"
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.
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!
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.