Input XLS / XLSX to output XLSB Template

[caltang]

Hi all, 

As the title suggests. I’m looking to make a post here after discussions with Alteryx and also some friends on this matter. I’ve exhausted the search on the community for an answer and my use case is this: 

I have data streaming in from various sources, but in this instance, I will keep it simple as XLS or XLSX files. Then, I have an XLSB template that requires me to output certain values to certain cells in that XLSB. 

For example, I have the data from XLSX:

Date | Value

2023-08-08 | 78

2023-07-09 | 124

The | is the delimiter between data from XLSX. 

My XLSB template requires to summarize - group by Date and Sum by Value, then output to Cells C3:C

So C3:C means that it will always start at C3 and go all the way down dynamically as some datasets have more or less dates to group by. 

At the moment, let’s assume that in the XLSB, the C3:C cells are painted yellow and formatted on Excel as numeric. 

I have been told that Blob Input and Blob Output may not work the best here as it’s not an XLSX template. But I am not sure if this is the case - can anyone also confirm this?

Appreciate all brainstorm on this and hoping to raise this so that it appears on SEO!

[apathetichell]

It's not a blob input/output issue - but an issue of if overwriter range/retain formatting works as well for .xlsb. I'm not sure it does - if I have time I'll play around with it.

[caltang]

I actually raised this with a few ACEs and others at Inspire 2024. I’ve gotten some feedback to go the Python route so it plays well. Just looking to see if it can be done via Alteryx before that. 

@apathetichell appreciate your response as always!

[flying008]

Hi, @caltang 

1- If you can specify a Sheet name with 'Data' in the abc.xlsb file to receive your Output, and then use formulas to reference that data, then this formatting problem will be much easier in the future.

2- Another option is to convert your abc.xlsb to abc.xlsx first, then output the data normally, and finally use a script to convert the abc.xlsx to the abc.xlsb file format.

[caltang]

I think forcing it with a sheet name call is a good idea. Though need to make it dynamic if the sheet names change.

[caltang]

@flying008 I tried your idea and kept getting an error. It says Syntax Error to CREATE TABLE - I've tried all four output options with the output tool, and using the Formula tool to declare the path. But nothing works. Does it work on your end? 

[flying008 · Accepted solution]

Hi, @caltang 

There is a trick by within excel and output option, haha.

1- abc.xlsb

2- workflow:

Spoiler

Note that the file format of your output is abc.xlsb, but your file format options must be selected Microsoft Excel 1997-2003 (*.xls), otherwise your output will get an error like error to CREATE TABLE.

3- Output:

4- You can also select the output options like 'Overwrite Sheet (Drop)' as your want.

5- now, please do it yourself.

[caltang]

This is awesome. Can I ask you about the Output formatting? Did you change the entire file path? 

From your GIF, your output is actually writing the data to a certain cell in column D - I don’t see that being declared in your Path in the formula? 

Thanks my friend @flying008 - I’m doing it your way but still running into errors for Creating Tables even with XLS outputs. Not sure if it’s an engine issue.

[flying008 · Accepted solution]

Hi, @caltang 

This is awesome. Can I ask you about the Output formatting? Did you change the entire file path? 

From your GIF, your output is actually writing the data to a certain cell in column D - I don’t see that being declared in your Path in the formula? 

The content of my path field column does not declare a cell range such as C3:C100, but please take a closer look at the GIF, the value of the D5 cell in the Sheet name Data is 'Input', and the output field column name of the alteryx is also 'Input', so they can be automatically matched to find the corresponding position when output.

BTW, maybe you can try output a named range for your case, Maybe this path will be more flexible, good lucky for you.

Also, I never use AMP, and because the differences in versions, you may not get the same results as me when using the new version like v2024.1, there are so many twists and turns that we can't control.

[caltang]

This works on older versions but not with v2023 and above I’m afraid. But it does solve the need. The alternative is to go the Python route to change the file, or have the file output to a flat file and have the XLSB file read the input from said file.