Extract & Append Excel files from Network Drive into EG

[OscarBoots1]

Hi Forum, I was wondering if anyone has dine this with EG & how so?

I'm getting Excel files dropped Daily into a Network Drive folder.

They have a date stamp but the first 10 or so characters are the same each time.

I want a process that checks the folder & brings a Range of cells from a spreadsheet & Appends to a table each day.

I know that's a lot to do but maybe someone has done something similar before?

Thanks

[Reeza]

1. List of files from the folder - see Macro Appendix for an example

2. Process the list and find the latest data you need to import

3. Import the data - this should be straightforward

This is a fairly common task and you should find a lot of examples online. 

[OscarBoots1]

Thanks Reeza.

Where do I find the macro Appendix?

Cheers

[Reeza · Accepted solution]

https://communities.sas.com/t5/SAS-Communities-Library/SAS-9-4-Macro-Language-Reference-Has-a-New-Ap...

Here's an example of the full code for Excel files. But a warning, you'll run into issues. Excel files have no define structure so when SAS reads it, it guesses at types. This means some files will be processed with a variable as character and others as numeric. You'll need to add a step to ensure that your variables are in the type/format that you want. 

https://github.com/statgeek/SAS-Tutorials/blob/master/Import_all_files_one_type

[OscarBoots1]

Thanks Reeza,

Useful stuff for other projects as well.

Cheers

[OscarBoots1]

Hi Again,

The below is the first part of the code & I'm trying to work out where I give the Directory path & Server etc?

I haven't done a  lot of work with EG Macros.

Cheers

Program Description

Begin the macro definition with two parameters.

%macro drive(dir,ext); 
   %local cnt filrf rc did memcnt name; 
   %let cnt=0;

Create two macro variables:

• &FILRF will contain the fileref (Mydir) to be used within the FILENAME function.
• &RC will contain the results from the FILENAME function. The returned value is a 0 if it is successful.

The FILENAME function assigns the fileref (Mydir) to the directory passed to the macro (&DIR).

%let filrf=mydir;
   %let rc=%sysfunc(filename(filrf,&dir));
  

[OscarBoots1]

Hi All,

I'll start a new post for this one.

Stay tuned.  :  )