topic Re: Extract & Append Excel files from Network Drive into EG in SAS Enterprise Guide

Extract & Append Excel files from Network Drive into EG

OscarBoots1 — Mon, 16 Oct 2017 22:38:02 GMT

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

Re: Extract & Append Excel files from Network Drive into EG

Reeza — Mon, 16 Oct 2017 23:56:16 GMT

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.

Re: Extract & Append Excel files from Network Drive into EG

OscarBoots1 — Tue, 17 Oct 2017 00:11:03 GMT

Thanks Reeza.

Where do I find the macro Appendix?

Cheers

Re: Extract & Append Excel files from Network Drive into EG

Reeza — Tue, 17 Oct 2017 00:28:59 GMT

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

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

Re: Extract & Append Excel files from Network Drive into EG

OscarBoots1 — Tue, 17 Oct 2017 00:49:55 GMT

Thanks Reeza,

Useful stuff for other projects as well.

Cheers

Re: Extract & Append Excel files from Network Drive into EG

OscarBoots1 — Tue, 17 Oct 2017 01:11:37 GMT

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

Re: Extract & Append Excel files from Network Drive into EG

OscarBoots1 — Tue, 17 Oct 2017 01:19:40 GMT

Hi All,

I'll start a new post for this one.

Stay tuned. : )