Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Importing data from xlsm format

mridulasori
6 - Meteoroid

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

10 REPLIES 10
s_pichaipillai
12 - Quasar

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
13 - Pulsar
13 - Pulsar

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

 

1.JPG

 

2. Select the .xlsm file you want

 

3. Selecting .xlsx as the built in type

 

2.JPG

 

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

 

3.JPG

 

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

 

Joe Lipski
s_pichaipillai
12 - Quasar

Joe,

 

this is excellent trick J

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

mridulasori
6 - Meteoroid
Thanks Joe. This worked

Regards
Mridula
Joe_Lipski
13 - Pulsar
13 - Pulsar

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

Joe Lipski
mridulasori
6 - Meteoroid

done! Thank you.

Mais
5 - Atom

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
7 - Meteor

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
7 - Meteor

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

Labels