Input XLS / XLSX to output XLSB Template

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!

Developer

Developer Tools

Output

Macros

Dynamic Processing

Accepted answers

flying008

Hi, @caltang

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

1- abc.xlsb

录制_2024_05_21_09_51_18_903.gif

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.

录制_2024_05_21_09_43_53_327.gif

3- Output:

录制_2024_05_21_10_01_38_40.gif

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

5- now, please do it yourself.

flying008

Hi, @caltang

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

录制_2024_05_21_10_41_06_927.gif

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.

All comments

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

Hi, @caltang

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

1- abc.xlsb

录制_2024_05_21_09_51_18_903.gif

2- workflow:

Spoiler

录制_2024_05_21_09_43_53_327.gif

3- Output:

录制_2024_05_21_10_01_38_40.gif

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

Hi, @caltang

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

录制_2024_05_21_10_41_06_927.gif

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?

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

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.

caltang

Just keen to see a ranged version if you can show an example @flying008 ! Say D3:D7? I’ve tried and it shows up as an error. I might be missing the syntax!

flying008

Hi, @caltang

If you want to use range to output, may .xlsb be converted to .xlsx file format in advance. otherwise, the use of named range is an alternative approach( syntax like '.\abc.xlsb|||`nm_Data`').

At the moment, almost all versions have the most extensive support for .xlsx formats.

Quick Links

This months top contributors

mceleavey 383

AkimasaKajitani 382

mbarone 337

Hollingsworth 335

LanisC 335