topic Re: Error of Excel not v5 when using PROC IMPORT in SAS Programming

Error of Excel not v5 when using PROC IMPORT

imdickson — Sat, 22 Sep 2018 10:56:12 GMT

Hi everyone. I am trying to use PROC IMPORT to import XLS file.

If i use dbms=csv, i will not hit any error.

Here is the thing, if i set dbms = xls, i will get below error:

"Spreadsheet isn't from Excel V5 or later. Please open it in Excel and Save as V5 or later
Requested Input File Is Invalid
ERROR: Import unsuccessful. See SAS Log for details."

So i temporary remain csv for now. However, the file is xls format.

Also another question, is there a way for SAS to read formulated final value that cells have formula in excel? By using csv option(not sure is caused by csv or not), the loaded data is the formula instead of the formulated value.

Re: Error of Excel not v5 when using PROC IMPORT

VDD — Sat, 22 Sep 2018 11:31:38 GMT

have you tried xlsx

Re: Error of Excel not v5 when using PROC IMPORT

Tom — Sat, 22 Sep 2018 11:31:52 GMT

What version of SAS are you using?

Are you running SAS on a Windows machine? Do you have Excel installed on that machine? Did you try using the DBMS=EXCEL?

Why is your XLS file using an old version of the XLS format? Can you just save it as an XLSX file instead?

CSV files are just text. If the value in the CSV is the formula then you created the CSV file with the wrong options.

Re: Error of Excel not v5 when using PROC IMPORT

imdickson — Sat, 22 Sep 2018 16:22:52 GMT

@VDDI tried xlsx but it gave me error. I havent go back to my work laptop now but i can post the error later.

@TomSAS 9.4, running SAS EG and DI on windows machine and the excel is installed on the machine. However, SAS server were hosted/installed on Linux server and excel spreadsheets are all stored in the linux server. I've tried DBMS=excel but it prompt errors. Unfortunately, I am out of town for the next 9 hours. I will try to show the error to you 10 hours later when using DBMS=Excel.

The files are all in XLS format. There are more than 500 of them, maybe even more than 1000 as i havent check folder by folder. There is no way for me to change the format nor do i tell my user to save it manually to xlsx as those are generated by another system handled by another team.

The file that i am reading is xls. Is there a way for me to capture the calculated value? If tweaking the DBMS or other options can work, i would be more than happy to learn about it Tom.

Re: Error of Excel not v5 when using PROC IMPORT

imdickson — Sat, 22 Sep 2018 16:45:11 GMT

Hi Guys. I managed to borrow laptop from others to remote connect.

When i use DBMS = XLSX, i get error below:

ERROR: Error opening XLSX file -> /data/source/ttt/Files/Central/ABBA/Central_ABBA_11_201401.xls.xlsx . It is either not an Excel spreadsheet or it is damaged. Error code=8014900A
Requested Input File Is Invalid
ERROR: Import unsuccessful. See SAS Log for details.

When i use DBMS=Excel, i get error below:

PROC IMPORT OUT= WORK.excelout DATAFILE= "/data/source/ttt/Files/Central/ABBA/Central_ABBA_11_201401.xls"
27
28
29                     DBMS=excel REPLACE;
ERROR: DBMS type EXCEL not valid for import.

The only option that i can use is csv but it will not take in the formulated value.

I hope there is a workaround here.

My Proc Import statement:

PROC IMPORT OUT= WORK.excelout DATAFILE= "/data/source/ttt/Files/Central/ABBA/Central_ABBA_11_201401.xls" 

            DBMS=excel REPLACE;
getnames=no;
RUN;

Re: Error of Excel not v5 when using PROC IMPORT

Reeza — Sun, 23 Sep 2018 01:21:26 GMT

Can you attach a copy of the file that you believe is xls? Sometimes people put an xls extension but the underlying file is HTML or CSV. Excel will still open the files correctly.

Re: Error of Excel not v5 when using PROC IMPORT

imdickson — Sun, 23 Sep 2018 02:18:48 GMT

Sure, you may have a look at it but SAS Forum restrict me from doing so with this error @Reeza

Re: Error of Excel not v5 when using PROC IMPORT

imdickson — Sun, 23 Sep 2018 02:19:39 GMT

@ReezaI managed to zip it. You may have a look at the xls file.

Re: Error of Excel not v5 when using PROC IMPORT

Reeza — Sun, 23 Sep 2018 02:54:28 GMT

The file is XLT, not XLS, so SAS is correct.

You can use VBS to convert the files automatically, or do it manually, but you'll likely have to change the type. Or you can try specifying it as xlt and see what happens.

https://gist.github.com/statgeek/878e585102c14e01581f55dbe972d27e

Re: Error of Excel not v5 when using PROC IMPORT

imdickson — Sun, 23 Sep 2018 03:20:20 GMT

Reeza, I think I uploaded the wrong file as I was trying to save the original excel into many different extension for testing purpose. Let me reupload it back when I got back my work desk. I appreciate for your patience and effort.

Re: Error of Excel not v5 when using PROC IMPORT

imdickson — Sun, 23 Sep 2018 04:27:12 GMT

Hi @Reeza, kindly refer to my new upload.

Re: Error of Excel not v5 when using PROC IMPORT

imdickson — Sun, 23 Sep 2018 06:54:57 GMT

Hi Guys! Problem solved.

I retain dbsm=csv but adding delimiter option of tab.

This way, it can read properly as i suspect the file is in xls but with some xml or scripts in it.

I thanks everyone that gave me guidance and explanation. I really appreciate your effort and I wish you good luck.

Re: Error of Excel not v5 when using PROC IMPORT

Tom — Mon, 24 Sep 2018 00:49:51 GMT

I am glad your problem is solved but this whole conversation makes no sense.

You started off saying you were getting errors importing an XLS file. You ended up posting an XLS file that SAS can read with no problems and then saying that you solved the problem by importing a CSV (delimited text file) with TAB as the delimiter. Does that mean you changed the files somehow? Or that they were never XLS files to begin with?

Re: Error of Excel not v5 when using PROC IMPORT

Reeza — Mon, 24 Sep 2018 14:54:03 GMT

Then you likely never had an xls file. You had a tab delimited file that someone changed the extension on and that Excel happened to open correctly. Note that a CSV file means comma separated variables. Since your delimiter is not a comma or semicolon (french) and the delimiter is tab, specify that alone, not CSV because that's incorrect.

@imdickson wrote:

Hi Guys! Problem solved.

I retain dbsm=csv but adding delimiter option of tab.

This way, it can read properly as i suspect the file is in xls but with some xml or scripts in it.

I thanks everyone that gave me guidance and explanation. I really appreciate your effort and I wish you good luck.