Desktop productivity for business analysts and programmers

Extract & Append Excel files from Network Drive into EG

Accepted Solution Solved
Reply
Contributor
Posts: 72
Accepted Solution

Extract & Append Excel files from Network Drive into EG

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


Accepted Solutions
Solution
‎10-16-2017 08:49 PM
Super User
Posts: 23,928

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

Posted in reply to OscarBoots1

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

 

 

View solution in original post


All Replies
Super User
Posts: 23,928

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

Posted in reply to OscarBoots1

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. 

Contributor
Posts: 72

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

Thanks Reeza.

 

Where do I find the macro Appendix?

 

Cheers

Solution
‎10-16-2017 08:49 PM
Super User
Posts: 23,928

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

Posted in reply to OscarBoots1

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

 

 

Contributor
Posts: 72

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

Thanks Reeza,

 

Useful stuff for other projects as well.

 

Cheers

Contributor
Posts: 72

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

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));
  
Contributor
Posts: 72

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

Posted in reply to OscarBoots1

Hi All,

 

I'll start a new post for this one.

 

Stay tuned.  :  )

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 366 views
  • 0 likes
  • 2 in conversation