bulk import into EG.

[wkossack]

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?

[RichardH_sas]

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/

[wkossack]

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

[LinusH]

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

[Adrian_Mc]

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?