Results 1 to 7 of 7

filter CSV file before importing into Excel

This is a discussion on filter CSV file before importing into Excel within the Excel Questions forums, part of the Question Forums category; Hi, I am on Windows 2000 using Excel 2000. I receive 15 CSV files on a daily basis. These CSV ...

  1. #1
    Board Regular
    Join Date
    Dec 2003
    Posts
    56

    Default filter CSV file before importing into Excel

    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.

  2. #2
    MrExcel MVP
    Join Date
    Apr 2002
    Location
    Vancouver BC , Canada
    Posts
    6,259

    Default Re: filter CSV file before importing into Excel

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

    <MARQUEE>...........Never be afraid to try something new. Remember, amateurs built the ark, professionals built the Titanic...............The easiest thing to find is fault, don't be easy !.. --Anonymous--...</marquee>

  3. #3
    MrExcel MVP
    Moderator
    Andrew Poulsom's Avatar
    Join Date
    Jul 2002
    Posts
    68,835

    Default Re: filter CSV file before importing into Excel

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

    Code:
    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

  4. #4
    MrExcel MVP
    Join Date
    Apr 2002
    Location
    Vancouver BC , Canada
    Posts
    6,259

    Default Re: filter CSV file before importing into Excel

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

    <MARQUEE>...........Never be afraid to try something new. Remember, amateurs built the ark, professionals built the Titanic...............The easiest thing to find is fault, don't be easy !.. --Anonymous--...</marquee>

  5. #5
    MrExcel MVP
    Moderator
    Andrew Poulsom's Avatar
    Join Date
    Jul 2002
    Posts
    68,835

    Default Re: filter CSV file before importing into Excel

    Quote Originally Posted by Nimrod
    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.

  6. #6
    Board Regular
    Join Date
    Dec 2003
    Posts
    56

    Default Re: filter CSV file before importing into Excel

    Thank you Nimrod and Andrew.
    This helped me.

  7. #7
    MrExcel MVP
    Join Date
    Apr 2002
    Location
    Vancouver BC , Canada
    Posts
    6,259

    Default Re: filter CSV file before importing into Excel

    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.

    <MARQUEE>...........Never be afraid to try something new. Remember, amateurs built the ark, professionals built the Titanic...............The easiest thing to find is fault, don't be easy !.. --Anonymous--...</marquee>

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com