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

I am using Data Integration Studio 4.9 and I need to import an Excel file.

 

I ve seen that my predecessor did it via -> New -> External File -> User Written.

 

Sometimes he used something like this:

Libname Excel3 XLSX "D:\TimeMattersDWH\Import\Startrax\Steuertabelle.xlsx";

data &_Output.(where=(not missing(partner_no_)));
length
station $ 100
Location_Code $ 10
partner_no_ $ 25
partner_name $ 100
flat_km_limit 8
courier_flat 8
km_surcharge 8
km_surch_nwh 8
late 8
weekend 8
currency $ 10
valid_from 8
valid_to 8
time_from 8
time_to 8
days 8
Last_Updated 8
Updated_By $ 6
Schriftliche_Vereinbarung_abgele $ 3
Status_Comment___Bearbeiter $ 70 ;
set Excel3."Courier Partner"n ;
partner_no_ = VAR3;
partner_name = Partner__Name;
flat_km_limit = VAR5;
courier_flat = VAR6;
km_surcharge = km__surcharge;
km_surch_nwh = VAR8;
valid_from = Valid__From;
valid_to = Valid__To;
time_from = Time__from;
time_to = Time__to;
keep station 
Location_Code
partner_no_
partner_name
flat_km_limit
courier_flat
km_surcharge
km_surch_nwh
late
weekend
currency 
valid_from 
valid_to 
time_from 
time_to 
days 
Last_Updated 
Updated_By 
Schriftliche_Vereinbarung_abgele 
Status_Comment___Bearbeiter ; 
run;
Libname Excel3;

Sometimes just this:

 

Libname XLSTRAPT XLSX "D:\TimeMattersDWH\Import\Startrax\Tracking_Punkte.xlsx";

Proc SQL;
	Create Table &_Output. As
		Select *
			From XLSTRAPT.DisplayOptions
			Where not missing(TrackingPointID);
Quit;

Libname XLSTRAPT;

My first question is,

when do I use XLSTRAPT and when Excel3? What is the difference even?

 

Sometimes there is every field listed, with FORMAT, LENGTH, etc. and sometimes its just a simple PROC SQL SELECT *...

How do I know which one to use?

 

If I put an Excel sheet into Enterprise Guide, I think I can use the generated code as file parameter in the external file's properties, but I cant get that to work either.

 

I am really lost here and I have no clue where to start...

 

Could someone point me please to the solution?

I ve attached the Excel file to this thread.

 

Best regards

Dirk

 

1 ACCEPTED SOLUTION

Accepted Solutions
Patrick
Opal | Level 21

With DIS you need stable table structures which is something Excel doesn't provide (i.e. defined lengths for character variables). I therefore always advise against using Excel as data source; but I know of course that sometimes things just are as they are...

 

What I'm normally doing with Excel sources is to first convert them to a text file and if there are multiple sources then I'd implement this conversion as a custom transformation where I then just pass in input path, workbook, sheet name and eventually Range, output pathname and filename (using Proc Export in the code section of the custom transformation).

I also tend to implement this conversion step in a DIS job on its own to keep "the mess" separated from my "clean" DIS processes.

 

Once you've created the text file you can create external file metadata with all the variables and attributes well defined on SAS Metadata level.

 

View solution in original post

3 REPLIES 3
LinusH
Tourmaline | Level 20
First, I usually try as hard as possible let not to involve Excel in DI due to their flexible nature, which causes headache for maintenance.
That said, I would prefer not to use an External File, rather an Excel Library registration. I don't have DIS at my fingertips so I can't give you the exact steps, but it's covered in the documentation as well in several papers.
Data never sleeps
Reeza
Super User

Idea is you create a library to point to the Excel file. Because there are many different file types you specify the DBMS= XLSX  so SAS know you're working with an Excel file. 

 

The format for a libname is 

 

LIBNAME pointer DBMS 'path to file';

 

Pointer can be anything less than 8 chars, not starting with a number. In your case they used Excel3 and xlstart. It could easily be any text, that's just the library name. 

 

Libname vs GUI - code and libname are easier to control, change and use in other SAS programs. 

Patrick
Opal | Level 21

With DIS you need stable table structures which is something Excel doesn't provide (i.e. defined lengths for character variables). I therefore always advise against using Excel as data source; but I know of course that sometimes things just are as they are...

 

What I'm normally doing with Excel sources is to first convert them to a text file and if there are multiple sources then I'd implement this conversion as a custom transformation where I then just pass in input path, workbook, sheet name and eventually Range, output pathname and filename (using Proc Export in the code section of the custom transformation).

I also tend to implement this conversion step in a DIS job on its own to keep "the mess" separated from my "clean" DIS processes.

 

Once you've created the text file you can create external file metadata with all the variables and attributes well defined on SAS Metadata level.

 

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 connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 3 replies
  • 9061 views
  • 8 likes
  • 4 in conversation