Hi,
I have thousands of excel files which is in the same structure but some of them may be in .xls and some in .xlsx
I tried to use directory to import all excel files but i come across some errors. I have a macro to break down the excel into rows and colums but it only works for xlsx not xls.
it about unable to open archive for unzipping.....
sorry i didnt cap any screen shots but i saw some discussion on it.
What should I do to read all of them?
thanks
Kenneth
Solved! Go to Solution.
@kennethli give the following a try. Wherever you unzip it, it should run out of the box. I'm using a directory tool to get the filepaths. The macro feeds the filepaths into an input tool, as well as change the filetype to either xls (8) or xlsx (25). While I'm just reading Sheet1 as an example, you could obviously change the sheet name or pass it to the macro for your situation!
Thanks @patrick_digan, that's a easy trick to make it works. Just wondering how do you know the filetype is 8 and 25 before looking into the configurations.
One further question, is it possible to unlock the excel sheets with alteryx? I found some of my xlsx files are locked...which causes error.
There is an advanced option to display xml in properties window. When I add any old xls as an input tool, I checked the xml to see that it corresponds to fileformat 8:
I've never looked into unlocking excel sheets.
Whenever I get the error in your Capture.PNG file, it's usually because the Excel file is really HTML and Alteryx doesn't support that specific format. I haven't figured out a nice way of getting Alteryx to open them up. To tell if you have a HTML file, open your Excel file in notepad or any ASCII editor, if it looks like this you will know it's HTML