SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

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

Reply
Frequent Contributor
Posts: 89

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

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.

Super Contributor
Posts: 349

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

Posted in reply to TurnTheBacon
Respected Advisor
Posts: 4,173

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

Posted in reply to TurnTheBacon

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)"

Contributor
Posts: 66

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

Posted in reply to TurnTheBacon

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

Ask a Question
Discussion stats
  • 3 replies
  • 3817 views
  • 3 likes
  • 4 in conversation