topic Re: Reading an XLS-file into DI Studio (without saving it as CSV) in SAS Data Management

Reading an XLS-file into DI Studio (without saving it as CSV)

TurnTheBacon — Thu, 20 Sep 2012 11:35:00 GMT

I need to import data from an XLS-document into DI Studio. I tried saving it as a CSV-file and importing it as a delimited external file, which worked well until I discovered that the CSV-file removes all unique Norwegian characters such as Æ, Ø, and Å. So, I figure I'll need to import the data directly from the XLS-file. Do you have any advice on how I might do this?

Thanks for your time.

Re: Reading an XLS-file into DI Studio (without saving it as CSV)

shivas — Thu, 20 Sep 2012 12:18:18 GMT

Hi,

Try this link...

http://sascodeblog.wordpress.com/read-in-an-excel-sheet-in-sas-data-integration-studio-4-2/

Thanks,

Shiva

Re: Reading an XLS-file into DI Studio (without saving it as CSV)

Patrick — Sun, 23 Sep 2012 02:35:52 GMT

The way as proposed in the link shivas posted would be the most direct way of doing things - but you need SAS/Access to PC files licensed.

In case you don't have this module licensed:

I just made a test with MS Excel 2010 under Win7: My encoding is English but the characters you mentioned are part of my "Character Map". I've added these characters to an Excel spreadsheet and then saved it as csv. The characters were still there. Just make sure you save as "CSV (Comma delimited)(*.csv)" and not as "CSV (MS-DOS)(*.csv)"

Re: Reading an XLS-file into DI Studio (without saving it as CSV)

CameronLawson — Tue, 25 Sep 2012 08:57:58 GMT

At work we have used the OLEDB library engine for xls and xlsx files. We don't set them as libraries however like the linked blog post. We script the connection in DI as a custom file. This has the advantage of not persisting the connection to the file while still maintaining a reference in Metadata for the object.

Cameron