filter CSV file before importing into Excel

[vacation]

Hi,

I am on Windows 2000 using Excel 2000.
I receive 15 CSV files on a daily basis.
These CSV files are auto-generated by some non-Excel software.
Each CSV file contains over 100 rows of data.
I import these CSV files into Excel and run reports on them.
About 60% of the data in these CSV files is of no use to me.
I would like to get that 40% data extracted from CSV files *before* importing into Excel.
(I have tried doing this within Excel with Autofilter and such but it takes too long.)
But since these are CSV files, they can be trimmed at the command line via BAT files.

Here is what the CSV data looks like:
----------------------------------------
|,idnum,|,typenum,|,56,|,locnum,|, etc.
|,idnum,|,typenum,|,22,|,locnum,|, etc.
|,idnum,|,typenum,|,01,|,locnum,|, etc.
|,idnum,|,typenum,|,88,|,locnum,|, etc.
|,idnum,|,typenum,|,41,|,locnum,|, etc.
|,idnum,|,typenum,|,22,|,locnum,|, etc.
|,idnum,|,typenum,|,09,|,locnum,|, etc.
|,idnum,|,typenum,|,61,|,locnum,|, etc.
----------------------------------------

Where:
The | char is the delimiter.
The idnum, typenum, locnum are non-uniq and non-sorted.
The etc. represents additional data in the row which is not shown here.

I am interest in the 3rd column which contains 56,22,01,88,41,22,09,61

I would like to:
1. Have a BAT script that grabs all the rows where the 3rd column is greater than 60.
2. So in this case ONLY rows 4 and 8 would apply because they contain 88 and 61 respectively.
3. Copy these 2 rows to a file called INPT.CSV

I am not a batch file expert. I have tried doing this on my own but I am struggling and running out of time.
Can you give me the batch script that does this trimming?

I recognize that this is not really an Excel question.
But I thought CSV files are widely used so I could get help here and others might find this useful too.

Thank you.

 

[Nimrod]

Please note that this macro assumes all CSV files are located in a directory call c:\Battest

This Bat file will take ALL CSV files located in c:\Battest\*.csv and transfer any lines with value 60 or better to one file .... c:\Final.csv

:: ------------------ BAT start here -----------
@echo off
del c:\Total.csv
Del c:\Final.csv

Type c:\BATTEST\*.CSV > c:\Total.csv

FOR /F "eol=- delims=, tokens=1-5,6,7*" %%1 IN ( c:\Total.CSV ) DO IF %%6 GEQ 60 ECHO %%1 %%2 %%3 %%4 %%5 %%6 %%7 %%8 >> c:\Final.csv

:: ------------------ Bat end here --------------------

PLEASE NOTE: That the from "FOR" to ">> c:\Final.csv" is ALL ON ONE LINE

The final CSV file is call "Final.csv" and is found at the root of C drive eg (C:\Final.csv) ........ OK ??

 

[Andrew Poulsom]

You might find this quicker than creating a stripped down CSV file:

Sub Test()
    With ActiveSheet
        .Range("A1").EntireRow.Insert
        .Range("C1").Value = "ID"
        With .UsedRange
            .AutoFilter Field:=3, Criteria1:="<=60"
            .SpecialCells(xlCellTypeVisible).EntireRow.Delete
        End With
    End With
End Sub

 

[Nimrod]

Andrew ...Hi ;
Does your solution take care of all 15 CSV files ? Or will the Op have to import 15 times ?

 

[Andrew Poulsom]

Andrew ...Hi ;
Does your solution take care of all 15 CSV files ? Or will the Op have to import 15 times ?

No it only does one file.

I didn't see your solution when I posted and I was unaware you could do all that in a BAT file. Clever.

 

[vacation]

Thank you Nimrod and Andrew.
This helped me.

 

[Nimrod]

Hi Andrew:
It's surprising what you can do in Bat files.... In the old days I saw entire network soluitions writen in this stuff ... including Backup systems and Big Brother systems.