Solved a batch file that will combine multipule .csv files into one

May 22, 2012 at 07:36:37
Specs: Windows 7
i have found a .bat file that is really close however being new to .bat files i can't figure out how to remove the filename off the end. i am needing it to combine all the files in a folder, and remove the headers. a little snag is that a couple of the files have certain headers but a few have a couple more.
i can post the file if needed

See More: a batch file that will combine multipule .csv files into one

Report •

✔ Best Answer
August 22, 2012 at 19:30:57
I didn't try to use the script you posted - it was way too confusing for me. I tried from "ground up", and this seems to work with my limited testing:
@echo off >mainfile & setlocal enabledelayedexpansion
for /f %%z in ('dir /b /x *.csv') do (
echo FILE:%%z
set /p test=<%%z
echo header line var test:!test!
for /f "tokens=1-14 delims=," %%a in ("!test!") do (
if "%%n" equ "" (call :twelve %%z) else (call :14 %%z)
)
)
goto :eof
:twelve
echo %1:twelve
for /f "skip=1 tokens=*" %%a in (%1) do >>mainfile echo %%a
goto :eof

:14
echo %1:at fourteen
for /f "skip=1 tokens=1-3,5-8,9* delims=," %%a in (%1) do >>mainfile echo %%a,%%b,%%c,%%d,%%e,%%f,%%g,%%i
goto :eof
:end
this sets all lines to 12 elements, and of course skips all the header lines (you might need to put the first one back in). No account taken for char.s hazardous to batchscript health and stability in the filestream.
ps: left a lot of debugging turds in the script. filter them out when things are working...



#1
May 22, 2012 at 12:27:03
Post a couple of the CSV files. Try to post the ones that are different.

Tony


Report •

#2
May 25, 2012 at 06:37:15
API#,Operator,County,Lease,Well#,Date Issued,Permitted Depth,Images,Total Depth,Well Type,Well Status,Lat,Long

API#,Operator,County,Legal,Lease,Well#,Date Issued,Permitted Depth,Permit TVD, Images,Total Depth,Well Type,Well Status,Lat,Long

so i would need the file to produce for all columns in the.csv or delete the extra columns (ie Lease and Permit TVD). these are just the headers if you need the entire data set i can give more. here is the .bat file that is close...two things i would need different are the extra columns and removing the filename at the end.

@Echo off
::Set Output file name here
Set _f{1}=combined_files.csv
If EXIST "%_f{1}%" Del "%_f{1}%"
::Gets first filename in alphabetical order, excluding the batch file
For /F "tokens=*" %%A In ('dir /b /a-d /o:-n ^|c:\windows\system32\Find.exe /I /V "%~nx0"') Do Set _t0=%%A
::Read Header from first line in first file
For /F "usebackq tokens=*" %%A In (`c:\windows\system32\Find.exe /V /N "" "%_t0%" ^|c:\windows\system32\Findstr.exe /B /C:"[1]"`) Do Set _t1=%%A
::Output Header to temp file
>%temp%\_f{0} Echo.%_t1:~3%:"Filename"
::Read lines from each file excluding the batch file and excluding the header line
::Output to temp file adding :"filename" to end of line
For /F "tokens=*" %%A In ('dir /b /a-d /o:n ^|c:\windows\system32\Find.exe /I /V "%~nx0"') Do (
For /F "usebackq skip=2 tokens=1* delims=]" %%B In (`c:\windows\system32\Find.exe /V /N "" "%%A" ^|c:\windows\system32\Findstr.exe /I /V /B /C:"[1]"`) Do @Echo %%C:"%%A">>%temp%\_f{0}
)
Move %temp%\_f{0} "%_f{1}%"
For /L %%A In (0,1,1) Do Set _t%%A=


Report •

#3
August 21, 2012 at 05:41:54
copy all csv files into one folder

open notepad and type follow command

copy *.csv test.csv

and save any name.bat (test.bat) in same folder and double click
all csv files copy into test.csv file


Report •

Related Solutions

#4
August 22, 2012 at 19:30:57
✔ Best Answer
I didn't try to use the script you posted - it was way too confusing for me. I tried from "ground up", and this seems to work with my limited testing:
@echo off >mainfile & setlocal enabledelayedexpansion
for /f %%z in ('dir /b /x *.csv') do (
echo FILE:%%z
set /p test=<%%z
echo header line var test:!test!
for /f "tokens=1-14 delims=," %%a in ("!test!") do (
if "%%n" equ "" (call :twelve %%z) else (call :14 %%z)
)
)
goto :eof
:twelve
echo %1:twelve
for /f "skip=1 tokens=*" %%a in (%1) do >>mainfile echo %%a
goto :eof

:14
echo %1:at fourteen
for /f "skip=1 tokens=1-3,5-8,9* delims=," %%a in (%1) do >>mainfile echo %%a,%%b,%%c,%%d,%%e,%%f,%%g,%%i
goto :eof
:end
this sets all lines to 12 elements, and of course skips all the header lines (you might need to put the first one back in). No account taken for char.s hazardous to batchscript health and stability in the filestream.
ps: left a lot of debugging turds in the script. filter them out when things are working...


Report •

#5
August 23, 2012 at 06:15:03
FIrst off - thank you for spending time on this.
The test that I did on this did not remove the rows that are not needed, but it did bring everything in. I will work with what i have. Thanks again

Report •

Ask Question