Solved How can I create a Batch to remove extra header rows?

June 17, 2015 at 15:06:27
Specs: Windows 7
Hi,

I have a batch file that combines multiple CSV files that have similar file names. How do I add a script to remove the extra header rows?

This is what I currently have. It runs with no issues, I want to keep the 1st row as header, but remove the extra header rows that result from the multiple CSV files.

copy \\MI\Master\Export\recondata*.csv "\\MI-MKE-GOV\RCP_Master\Export\recondata all import.csv"
copy \\MI\Master\Export\aged_items*.csv "\\MI-MKE-GOV\RCP_Master\Export\aged_items all import.csv"

Pause


See More: How can I create a Batch to remove extra header rows?

Report •


#1
June 17, 2015 at 20:11:03
If the header rows all have something in common that the data-rows don't have, you could run the existing batch, then capture the header-line into a work-file, then remove ALL the headers, using "FIND /V" or "FINDSTR /V" (output appended to the workfile), leaving you with one header-line and the rest data-content. f/e, your header looks like:
TRACT-NAME,CO-ORDINATES,DISTRICT
Now run your existing batch:
copy \\MI\Master\Export\recondata*.csv "\\MI-MKE-GOV\RCP_Master\Export\recondata all import.csv"

next:
::create a one-line WORKFILE file consisting of the header:
FIND "TRACT-NAME" <"ALL IMPORT.CSV">workfile
set /p x=<workfile
>workfile echo %x%
:: OR, if the first line is always the header:
set /p x=<"all import.csv"
>workfile echo %x%
:: remove all headers from the bulk-file and append to the one-line header-file (workfile):
FIND /V "TRACT-NAME" <"ALL IMPORT.CSV">>workfile

message edited by nbrane


Report •

#2
June 18, 2015 at 08:23:35
Below is what I have now. The all import.csv file gets created, but it still has extra header rows. Does this look correct?

TRACT-NAME,PRCSMO,CONO,CONAME,CCNO,CCDESC,ACCTNO,ACCTDESC

copy \\MI\Master\Export\recondata*.csv "\\MI-MKE-GOV\RCP_Master\Export\recondata all import.csv"

FIND "TRACT-NAME" <"ALL IMPORT.CSV">workfile
set /p x=<"all import.csv"
>workfile echo %x%
FIND /V "TRACT-NAME" <"ALL IMPORT.CSV">>workfile


Report •

#3
June 18, 2015 at 18:41:16
✔ Best Answer
Is this your actual header?:
TRACT-NAME,PRCSMO,CONO,CONAME,CCNO,CCDESC,ACCTNO,ACCTDESC

("TRACT-NAME" was just an example I was working with, you need to use an actual header snippet/sample for the filter.)
I ran this as a short test, replacing TRACT-NAME as the tag, and it worked ok:
@echo off & setlocal
set headertag=PRCSMO
:: following correspondes to your: copy \\MI\Master\Export\recondata*.csv "\\MI-MKE-GOV\RCP_Master\Export\recondata all import.csv"
:: test4 is the counterpart of "all import.csv"
copy test+test2+test3 test4
:: this gets ALL the headers...
find "%headertag%"<test4>work
:: now just capture one header into "work"
set /p x=<work
echo %x%>work
:: now append just the data-content, sans headers, from the combined file to "work" find /v "%headertag%"<test4>>work
:: maybe this was the 'missing step'? "work" was the final destination of my solution.
move /y work "all import.csv"
::======== end batch
I think my solution is somewhat awkward, but it worked in my test.


Report •

Related Solutions


Ask Question