topic bulk import into EG. in SAS Enterprise Guide

bulk import into EG.

wkossack — Mon, 21 Jun 2010 21:15:24 GMT

I need to import a large number of spreadsheets into EG from a directory into a single dataset as part of a routine report.

The number and names of the spreadsheets may change.

Is there a way to do this with EG?

Re: bulk import into EG.

RichardH_sas — Tue, 22 Jun 2010 13:50:46 GMT

You're probably going to need SAS code to automate this process. Some good ideas are listed here, though these solutions do require you have SAS/ACCESS to PC Files licensed:

http://support.sas.com/kb/13/348.html

Others on this site might have additional suggestions or programs that could read a large number of Excel workbooks into a single SAS data set. If you need help with the above sample, it also doesn't hurt to check with Tech Support for ideas: http://support.sas.com/techsup/contact/

Re: bulk import into EG.

wkossack — Wed, 23 Jun 2010 14:52:24 GMT

the added problem is we are using a unix sas server and the file servers are windows

Re: bulk import into EG.

LinusH — Wed, 23 Jun 2010 15:16:10 GMT

As you can find in the documentation, the ACCESS to PC Files Formats comes with a PC Files Server which let you access Excel-files in a Windows files system.
/Linus

Re: bulk import into EG.

Adrian_Mc — Fri, 06 Aug 2010 15:54:41 GMT

In the past I have often written sas code which runs on our unix server and uses ftp to get a list of the files in a directory on a Windows server (assuming the windows server is accepting ftp connections).

Assuming the spreadsheets are all in the same format, I would use the Import Data task in EG once, to set up the code to create the output table.

Then I take the list of files I got above and ftp the files one by one (in sas code) from a windows server. I use my import data code to read the file into a data table and then insert that into a final table, which has the data for all the files.

If that's of some use to you I could send on some sample code?