Forum Discussion
Error while combining different excel files
Looking at the data i'm expecting you will have a number of problems to resolve once you get each excel file into your Queries section. Have you done any data cleansing? What cleansing have you done?
Here are the things you are likely going to need to do among any other challenges you find in your data:
- Remove the Top X rows to remove the fluff at the top
- Copy Down values in your first couple of columns as the pivot table style data is likely to only exist in the first row that it is printed
- You'll need to run an unpivot operation to get the data where you need it to be before merging
- After merging you would need to run a pivot operation to get each category as a column header.
Hi Ross73312,
Thanks for reply. I have followed the below steps :
1. The excel files are fetched from a folder. So the GetData -> Folder-> Combine& Edit
2. Choose "First File" as an example file. Click OK.
3. I removed top 6 rows from the "Transform Sample file from Test Report". Then "Use First rows as Headers" followed by "Unpivoting the Columns". Now I am getting error related to 2nd excel files and not able to find data related to it.
1st excel sheetI am not sure what step I need to follow to merge/ combine both the files without any errors.
Thanks
My recommandation is to bring each file in one by one. Get each one into the format you are happy with. Do an append only after you have checked each file is now in the desired matching format. This will let you step through the problems more easily if they arise.
Hi,
In future we are expecting more excel files to be updated in the folder. So bringing each file one by one, transform each files and appending will be repetitive task and I am trying to avoid the same.
Thanks
Based on your screenshots, your files differ in same fashion. From what i can see you need to remove the top X rows, but X is different in each file. You might find you have to deal with each file individually to hand code the required transformations.