Recurse a Directory of CSV Files and get Row Counts

[JeffreyLowe]

Greetings......

I need to loop through a folder of CSV files to get the number of rows from each file (excluding the header row) insert the file name and row count into an existing dataset and lastly delete the file.

For Example:

File1 250

insert fname rowcount into CSVRowCounts

delete File 1

File2 95

insert fname rowcount into CSVRowCounts

delete File2

and so on.....

Any assistance is greatly appreciated.

[JerryV]

If this is on Linux then in a terminal session I would cd to the folder in question and:  

wc -l *.csv 1>csvRowCounts.txt

rm -f *.csv

Then in SAS you can read csvRowCounts.txt into SAS and subtract 1 from each count.  But I wouldn't rm the files until you are sure of your result.

[JerryV]

And if it's Windows, at a command prompt cd to the directory in question and:

find /c /v "^$" *.csv >csvRowCount.txt

Read csvRowCount.txt into SAS and don't forget to subtract 1 from the count.

[JeffreyLowe]

Good solutions, I should have added this is for an automated process that will run weekly.

[JerryV]

Perhaps put the above OS commands, first, in the same script that launches the SAS session?

[Kurt_Bremser]

Even simpler: use a pipe.

data size_and_names;
infile "cd /path;wc -l *.csv" pipe truncover;
input size fname $200.;
run;

From that dataset, it is possible to get the counts and run the fdelete.

[ghosh · Accepted solution]

This method linked below had worked for me for csv file (as well as xlsx with slight modification), you can insert code to do a row count.  Seriously I wouldn't delete the the csv files in code, I would do it manually once I am satisfied the import worked

https://documentation.sas.com/?docsetId=mcrolref&docsetTarget=n0ctmldxf23ixtn1kqsoh5bsgmg8.htm&docse...

There is also a post by @Tom that looks pretty good 

https://communities.sas.com/t5/SAS-Enterprise-Guide/quot-Example-1-Import-All-CSV-Files-That-Exist-w...

[Ksharp]

Firstly get all the pathname of these CSV file by DIR or LS command, and Run the following code. 

Good Luck.

data want;
infile cards truncover;
input path $ 100.;
n_obs=0;
infile dummy filevar=path end=last;
do while(not last);
 input;
 n_obs+1;
end;
file=path;
cards;
c:\temp\have.csv
c:\temp\x.csv
c:\temp\xx.csv
;

[JeffreyLowe]

Thank you everyone for your responses!