community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Upgrade Alteryx Designer in 10 Steps

Debating whether or not to upgrade to the latest version of Alteryx Designer?

LEARN MORE

CReW Macro Wildcard XLSX "The following sheets did not match the others"

Meteoroid

Hi All,

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! 

Alteryx Certified Partner
Alteryx Certified Partner

Hi @sarah_burnett ,

 

Sure it's possible to union all sheets regardless of their structure; you will need a batch macro to do this!

 

I've built this a few times though, so to save you some time, please see the attached!

 

You will need to import the macro into Alteryx, and then plug your list of excel files (probably through a directory tool), into the macro 'Outer'.

 

Ben

Meteoroid

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. 

Meteoroid

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. 

Alteryx Certified Partner
Alteryx Certified Partner

Hi sarah,

 

That's not true.

 

Create a brand new workflow, and use the directory tool to get a list of your 16 files, then input the 'outer' macro onto the canvas, and connect your directory input to this.

 

Configure the macro to give the full file path (location and name +xlsx extension) and hit run, and it will do all.

 

No need to manually edit the macro.

Alteryx Certified Partner
Alteryx Certified Partner

Okay,

 

That makes the issue slightly more complicated.

 

If you could post examples of your worksheets then perhaps I could have a crack, but otherwise, if it's only a one off task to delete the row, then I'd probably just go ahead with that!

 

Ben

Meteoroid

Hi Ben, 

Thanks so much for your offer. 

 

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. 

Labels