Importing data from xlsm format

[mridulasori]

Hi,

Is it possible to import data from xlsm formats ? I am using version 10.0 but can't see this option?

 Any help will be appreciated.

Regards

Mridula

[mridulasori]

Is it possible to import data from xlsm formats ? I am using version 10.0 but can't see this option?

 Any help will be appreciated.

Regards

Mridula

[s_pichaipillai]

i don’t think it’s possible for both .(.xlsm) and (.xlsb) of excel formats 

What you can do is,

1. Manually convert to excel then import

2. Write some external code/application to convert your xlsm file to excel. And you can call the application using run command tool to convert your file to excel then import your data

Thanks

Sar

[Joe_Lipski · Accepted solution]

Hi @mridulasori & @s_pichaipillai

I have been able to read in .xlsm file by:

1. Selecting 'All Files (*.*)' at the botton of the files of type list on the input tool

2. Select the .xlsm file you want

3. Selecting .xlsx as the built in type

4. You will then need to select the sheet you wish to read in via the '...' (highlighted below) rather that the pop-up window that would appear with a standard excel format

I don't use .xlsm sheets very often, but I'm assuming this may not read in macros and other complex details, but if it's just the data you need then this should be fine. Be sure to check what Alteryx inputs is what you would expect.

Thanks,

Joe

[s_pichaipillai]

Joe,

this is excellent trick J

Thanks, I thought it’s not possible but you made it :)

[mridulasori]

Thanks Joe. This worked

Regards
Mridula

[Joe_Lipski]

Brilliant, glad I could help @mridulasori

NB - I think you marked the wrong post as the solution though, could you please correct for the benefit of others looking at this post.

Thanks,

Joe

[mridulasori]

done! Thank you.

[Mais]

Hi

I had the same problem in my workflow and it worked fine with your solution.

But now I wanted upload an xlsm file in an analytic app (File Browse --> Action Tool --> Input Data) which I start from the Alteryx Gallery. Is there anything I can do to accept an xlsm file?

Thank you very much

Marisa

[siddhartha_s]

Either with .xlsm or with .xlsb another way to do it is treating excel file as database and establishing an ODBC connection with it. Then in the SQL section write the SQL to read the data:

1. Select * from `Sheet1$`

2. Select * from `Sheet1$B9:AZ450000`

In case the data is in multiple sheets following the same format you may do your SQL like:

1. Select * from `Sheet1$` UNION ALL 

    Select * from `Sheet2$` UNION ALL 

    Select * from `Sheet3$`

In case there are multiple such files - write a batch macro and pass a directory input to the macro...

[siddhartha_s]

Dear S

Either with .xlsm or with .xlsb one way to do it is treating excel file as database and establishing an ODBC connection with it. Then in the SQL section write the SQL to read the data:

1. Select * from `Sheet1$`

2. Select * from `Sheet1$B9:AZ450000`

In case the data is in multiple sheets following the same format you may do your SQL like:

1. Select * from `Sheet1$` UNION ALL 

    Select * from `Sheet2$` UNION ALL 

    Select * from `Sheet3$`

In case there are multiple such files - write a batch macro and pass a directory input to the macro... Here ODBC needs to be managed in a templatized manner