Import multiple Excel files(xlsx)

[Aleixo]

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

[Reeza]

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;

[Reeza]

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

[Aleixo]

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

[Reeza]

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...

[Aleixo]

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.

[Reeza]

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?

[Aleixo]

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

[Reeza]

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

[Aleixo]

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

[Reeza]

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

[Aleixo]

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

[Reeza]

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. 

[Aleixo]

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

[Reeza · Accepted solution]

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.