How do I import Excel files in Data Integration Studio?

[dirks]

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

[LinusH]

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.

[Reeza]

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 · Accepted solution]

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.