DATA Step, Macro, Functions and more

Import multiple Excel files(xlsx)

Accepted Solution Solved
Reply
Contributor
Posts: 69
Accepted Solution

Import multiple Excel files(xlsx)

[ Edited ]

Hi,

 

I am trying import multiple files from a folder. I try every code in others topics and never made it. All files in that folder have the extension xlsx

 

For save the name files the basic code it is not working. I don't know why this happen.

 

filename tmp pipe 'dir "C:\Users\Documents\incidentes" /s /b';

data test;
	infile tmp dlm="¬";
	length buff $2000;
	input buff $;
	fname=scan(buff, countw(buff, "\"), "\");

	if index(upcase(fname), ".XLSX")>0 then
		call execute(cats('proc import datafile="', buff, '" out=', fname, '; run;'));
run;

 

NOTE: The infile TMP is:
      Unnamed Pipe Access Device,
      PROCESS=dir "C:\Users\Documents\incidentes" /s /b,
      RECFM=V,LRECL=32767

Stderr output:
File Not Found
NOTE: 0 records were read from the infile TMP.
NOTE: The data set WORK.TEST has 0 observations and 2 variables.
NOTE: DATA statement used (Total process time):
      real time           0.04 seconds
      cpu time            0.01 seconds

 

Some has a answer? Any good macro of this?

Regards

 


Accepted Solutions
Solution
‎02-07-2018 10:28 AM
Super User
Posts: 23,700

Re: Import multiple Excel files(xlsx)

JPGs are fine, insert them using the Photos option.

 

The Import Task is a different method, it can access data locally which you can't do via code. I'm assuming you're using EG or Studio? For this to work you'll need to move your files first to the server and then run the code you were trying to run with a path to the server folder.

 

 

 

 

 

 

 

 

View solution in original post


All Replies
Super User
Posts: 23,700

Re: Import multiple Excel files(xlsx)

Split your code and isolate where you're having the issue. 

 

Is it creating the TEST data set first with the list of XLSX files correctly? <- In this case fix this first.

Then add back the CALL EXECUTE. 

Note that you should also specify the DMBS in the export. 

 

Before you write a macro or CALL EXECUTE you need to have your base program working first, and then you make the CALL EXECUTE match that format. 

 

This is the code you need to get working first - what's the delimiter there? I don't think it should be the symbol you have listed...

 

filename tmp pipe 'dir "C:\Users\Documents\incidentes" /s /b';

data test;
	infile tmp dlm="¬";

	length buff $2000;
	input buff $;
	fname=scan(buff, -1 , "\");

run;

 

Super User
Posts: 23,700

Re: Import multiple Excel files(xlsx)

PS. Your pipe code works fine for me, are you sure your path is correct?

 

Contributor
Posts: 69

Re: Import multiple Excel files(xlsx)

[ Edited ]

Now in the new code the result is:

 

NOTE: The infile TMP is:
      Unnamed Pipe Access Device,
      PROCESS=dir "C:\Users\Documents\incidentes" /s /b,
      RECFM=V,LRECL=32767

Stderr output:
File Not Found
NOTE: 0 records were read from the infile TMP.
NOTE: The data set WORK.TEST has 0 observations and 2 variables.
NOTE: DATA statement used (Total process time):
      real time           0.06 seconds
      cpu time            0.06 seconds

 

I am sure that the pass is correct. I have a program with an import file(xlsx) from that folder.  

 

thanks for your answer

 

EDIT: When i try to delete the path for C:\Users\Documents\  the result is other folders and not excel type

Super User
Posts: 23,700

Re: Import multiple Excel files(xlsx)

So if you copy and paste that path into a Windows pane it goes to the folder?

 

This macro lists CSV files, but you can try changing it for XLSX and see if it works. Or at least the portion about getting the file names.

 

 

http://documentation.sas.com/?docsetId=mcrolref&docsetTarget=n0ctmldxf23ixtn1kqsoh5bsgmg8.htm&docset...

 

Contributor
Posts: 69

Re: Import multiple Excel files(xlsx)

I know that link. I try it 30 minutos ago. With no results.

 

NOTE: Writing TAGSETS.SASREPORT13(EGSR) Body file: EGSR

.......

c:\Users\Documents\incidentes cannot be open.

 

Super User
Posts: 23,700

Re: Import multiple Excel files(xlsx)

Is SAS running on a server or your desktop?

 

Either way it looks like it doesn't have access to that location. There isn't anything wrong with either code. The macro doesn't required XCMD so it's not that, so the only thing that it looks like to me is a path issue. 

 

If you run the DIR command outside of SAS does it work?

 

 

Contributor
Posts: 69

Re: Import multiple Excel files(xlsx)

SAS is running on server outside.

 

I am using sas enterprise guide. The folder C is on a server, but i also tried on desktop H: and it doesn't work.

 

I am confuse because i can programming with one file xlsx with the same path and works fine for IMPORT data from de excel.

 

Thanks your help

Super User
Posts: 23,700

Re: Import multiple Excel files(xlsx)

Can you post the log from that successful PROC IMPORT step?

Contributor
Posts: 69

Re: Import multiple Excel files(xlsx)

I am not using code. I am using the functions of enterprise:

 

28         DATA WORK.TTag_alterado;
29             LENGTH
30                 Data             $ 23
31                 Descritivo       $ 84 ;
32             FORMAT
33                 Data             $CHAR23.
34                 Descritivo       $CHAR84. ;
35             INFORMAT
36                 Data             $CHAR23.
37                 Descritivo       $CHAR84. ;
38             INFILE 'F:\saswork\*********************
39 LRECL=108 40 ENCODING="WLATIN1" 41 TERMSTR=CRLF 42 DLM='7F'x 43 MISSOVER 44 DSD ; 45 INPUT 46 Data : $CHAR23. 47 Descritivo : $CHAR84. ; 48 RUN;

 

NOTE: The infile 'F:\saswork\*********************is:
      Filename='F:\saswork\*****************,
      RECFM=V,LRECL=108,File Size (bytes)=52812,
      Last Modified=05Feb2018:15:32:56,
      Create Time=05Feb2018:15:32:56

NOTE: 490 records were read from the infile 'F:\saswork\*********
      The minimum record length was 1.
      The maximum record length was 108.
2                                                          The SAS System                             14:59 Monday, February 5, 2018

NOTE: The data set WORK.TTAG_ALTERADO has 490 observations and 2 variables.
NOTE: DATA statement used (Total process time):
      real time           0.03 seconds
      cpu time            0.01 seconds

 

I have the query of 490 observations

 

regards

Super User
Posts: 23,700

Re: Import multiple Excel files(xlsx)

Ah...that's a different path than you were using previously, F:\ , I assumed it was the same C:\ path.

Contributor
Posts: 69

Re: Import multiple Excel files(xlsx)

[ Edited ]

It is not a different path. I don't know why SAS assume that path F:

 

I change the location Analises_incidentes to incidentes because of the problem '_' under score.

 

And with this method i can import one Excel file, if you see the figure

 

Attachment
Super User
Posts: 23,700

Re: Import multiple Excel files(xlsx)

Did you write that code or did you use a Task to import the data?

 

What's in the Zip file? Why should I download it? Please just include it in the posts, you can use a spoiler entry if you think it gets too long. 

Contributor
Posts: 69

Re: Import multiple Excel files(xlsx)

Yes i use task. That task is in figure(zip file). SAS community don't accept png or jpeg files.

Solution
‎02-07-2018 10:28 AM
Super User
Posts: 23,700

Re: Import multiple Excel files(xlsx)

JPGs are fine, insert them using the Photos option.

 

The Import Task is a different method, it can access data locally which you can't do via code. I'm assuming you're using EG or Studio? For this to work you'll need to move your files first to the server and then run the code you were trying to run with a path to the server folder.

 

 

 

 

 

 

 

 

☑ This topic is solved.

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

Discussion stats
  • 17 replies
  • 341 views
  • 1 like
  • 2 in conversation