This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
I'm trying to import several excel files - each that have several tabs. To do this I'm using the CReW Macro Wildcard XLSX. It will successfully load about half of excel files. After running I get the error: "The following 201 sheets did not match the others". The issue seems to be that in the sheets that are not loaded the fields that contain dates are formatted as dates. In sheets that get loaded the fields that contain dates are formatted as general. Is there a way in Alteryx (or within the macro itself) to either accept all the fields that include dates regardless of format OR to change the format of the cells so that they match (i.e. all dates or all general but without losing the actual date)? I'd like to be able to import all of the data without manually changing each of the excel sheets.
Thanks Ben. I have 16 Excel files, each with multiple tabs. From the outer ymxc file you sent it looks like I would need to modify the file name 16 times - once for each Excel file? Also, the format of the excel files themselves isn't really conducive for data entry - there's multiple header rows that need to be stripped.
The CReW Macro Wildcard XLSX does a great job of a) running through all 16 excel files b) keeping in the file path c) marking each row with a record number - etc. all of which make it a lot easier to line up this less than perfect data.
I just did a mini test and it looks like even if the date formatting is fixed, it looks like it still won't accept the other sheets, so I'm not sure what is causing the macro to say that the sheets are not matching.
Thanks for your macro Ben. It helped me to diagnose the problem, but I'm still having an issue in solving it. When I ran the outer macro I realized that all of those that did not load started in column 2 in Alteryx, whereas those that did load started in column. Looking into my files a bit more, I found that in Row 1 of my Excel file, some of the sheets have an extra space after the title row, others do not. So I guess I have to go through and either add or take away a space, unless there's a way I can tell Alteryx to ignore the space and still line up the sheets to be in the same columns.
Here's a small test file with some fake data. The only difference between these two sheets is that the first column "Test Card" has a space after the phrase in Site B and no space after it for Site A.
I tried adding a Data Cleansing Tool to remove trailing and leading white spaces in field names within the CReW_WildcardXLSXInput.yxmcbatch but that didn't seem to work.
It won't take me long to manually fix this but I fully expect to be getting more data like this in the future and would love to have a longer term workaround. It can take a lot of time to find these types of hidden formatting issues, so it would be great to be able to update the macro (which works well in so many other ways) to deal with these types of issues.