07-12-2015 08:04 PM
Hi, I have this situation. I have to pull data for about six months, and the data is in csv but the way they are saved, it is not that straightforward. There is a folder for each date, and in them there are several csv files. CSV filenames start with date and some extension. I need one of these files going back about six months. I found macros which import CSVs from folders which are named this way, but this macro was pulling all the files, not just one,so kind of using wildcard. Any help will be greatly appreciated. I can probably write a macro to do this, but it will take me longer than I have.
07-12-2015 09:11 PM
Create a list of file names. If you know the structure of the naming system create a dataset with the names and then use CALL EXECUTE to import the files. If you don't know the data set names ahead of time, use an OS command to recursively list all the files and then filter that out to generate the list you need. Then use a basic macro to import.
Or use the filename option and a data step import - assuming all files have the same structure.
If you post more details you can get more help.
07-13-2015 04:54 AM
Yes, clarify what you have, how you identify each file. Its pretty simple once you have the logic, say for instance you want all files which have XYZ in the filename:
filename tmp pipe 'dir "c:\the_directory" /b /s';
length buffer $2000;
input buffer $;
if index(buffer,"csv") > 0 and index(buffer,"XYZ") > 0 then call execute('data '||strip(scan(buffer,1,'.'))||'; infile ... '); /* add in your import code after infile */
07-13-2015 05:53 PM
So my folders are named by dates and the CSVs have similar naming.
07-13-2015 07:41 PM
You need first to create a directory listing with the files you're after. Let's say it's always the first of the month then a DIR command as below would work:
dir /b/s C:\temp\datefolders\*my_stuff.csv
You then use this dos command in a pipe, eg. as below, to create a directory listing
filename dirlist pipe 'dir /b/s C:\temp\datefolders\01*my_stuff.csv';
There are many examples in this community how to create a directory listing and then process the files found. A search will bring them up.
07-14-2015 07:30 AM
There's a few examples on the web. Check the sample on the link bellow: