BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
PaigeMiller
Diamond | Level 26

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

Is that correct, or not?

--
Paige Miller
art297
Opal | Level 21

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

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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
Diamond | Level 26

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 Smiley Happy

--
Paige Miller
PaigeMiller
Diamond | Level 26

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)

--
Paige Miller
jakarman
Barite | Level 11

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.

---->-- ja karman --<-----
jakarman
Barite | Level 11

@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.  

---->-- ja karman --<-----
art297
Opal | Level 21

: 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
Diamond | Level 26

That's interesting, , you have a way of finding everything out there Smiley Happy 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.

--
Paige Miller
data_null__
Jade | Level 19

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

jakarman
Barite | Level 11

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.

---->-- ja karman --<-----

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 25 replies
  • 5712 views
  • 0 likes
  • 8 in conversation