Change TypeGuessRows in Program?

[PaigeMiller]

As far as I can tell, guessingrows cannot be used with Excel files.

Is that correct, or not?

[art297]

: You are correct! I wonder why they limited it just the delimited files.

[RW9]

In regards to conversion from Excel to CSV, there a fair few solutions out there.  One from a quick Google is the following:

https://stackoverflow.com/questions/1858195/convert-xls-to-csv-on-command-line

Basically a small script to open Excel and use the functionality within Excel to save as CSV.

[PaigeMiller · Accepted solution]

RW9 wrote:

In regards to conversion from Excel to CSV, there a fair few solutions out there.  One from a quick Google is the following:
https://stackoverflow.com/questions/1858195/convert-xls-to-csv-on-command-line

Basically a small script to open Excel and use the functionality within Excel to save as CSV.

Thanks, I will give this a try

[PaigeMiller]

Dang, , you have the answer!

That works perfectly, now I have a comma delimited file and don't have to worry about some setting in the Windows Registry!

(By the way, at that link, I removed the last line of the vbs file which reads:

WScript.Echo "Done"

since I want these programs to run in batch mode without user interaction)

[jakarman]

Yep it is there for delimited file-types Base SAS(R) 9.2 Procedures Guide

With Base SAS(R) 9.3 Procedures Guide, Second Edition This one maintained in the SAS registry not to be confused with the Windows registry.

The sas registry is as the same location as the personal SAS profile dataset (catalog type).  An xls file is not a delimted file isn't it.

Also delimited files have the question is the field numeric (converted to floating IEEE) or is it character.  Using the limited ranges of the characters 0-9 is more like a constraint and not having the same meaning as a measurement. Those intention in delimited file is not defined, for this shortcoming that is why xml has been designed.

[jakarman]

@Arthur your question can have a logical answer as the conversion is not done by SAS but by Microsoft (ACE driver). That dependency is seeing as the instructions of SAS are telling you to office/ms registry settings. The ACE driver is coming in with MS-office.  Avoiding that ACE driver dependency has other effects on accesability.  

[art297]

: One more thing for you to try. You can programmatically change the typeguessingrows setting. Take a look at:

LISTSERV 16.0 - SAS-L Archives

: Correct, but I'm not sure if DBMS=XLS and/or DBMS=XLSX use either the Ace or Jet engines. I can't find sufficient documentation regarding either.

[PaigeMiller]

That's interesting, , you have a way of finding everything out there Thanks!

However, in keeping with the idea expressed by in reply #7 of this thread, I would definitely like to have everything needed in one program (or one program plus subroutines and %include programs), instead of relying upon changing something in the registry (that might screw up other programs).

Now I suppose I could go and change every single program I have to modify the registry as needed ... or I can just use GUESSINGROWS from now on. I'll use GUESSINGROWS on CSV files, it's fast and easy.

[data_null__]

Does that even work anymore?  What with changes to Winders versions and the rest.

[jakarman]

data_null_ you are right with your remark to Arthur. as that L-archive changing the number is actually changing the windows registry HKLM\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel\TypeGuessRows  (jet 4.0) that is coming form an era it was common to do that kind of work in open desktops. Today you are supposed to us a restricted user not capable to do that and an admin user for updating your machine. The last not commonly allowed as result of desktop policies. All office versions are using a different setting 

Arthur Tabachneck I was confronted with all desktop problems where this was not working correctly. (closed ones with a repackage requiement). The surprises of inconsistent behavior (MDAC JET ACE) at the different SAS version have brought met to hate what is behind the screens there. Most can be found as MS issues then seeing the same with SAS. No, SAS is not telling everything what they have done behind that, but I a lot van be deduced. Some notes are more clear like 37412 - Errors occur when you import Microsoft Excel or Microsoft Access files into SAS® 9.2 or 37612 - The SAS® System can only import and export 255 variables when you access a Microsoft Excel 2... or 45496 - An error occurs when you create an Excel sheet with a string greater than 255 characters usi... for Eguide 50661 - An error might occur when you import Microsoft Office data using SAS® Enterprise Guide® 5.1 ... that 255 limitation can be found as a generic ACE limitation the bitness foro DLL differences.