Combine CSV Files and remain the name

July 30, 2019 at 19:41:03
Specs: Windows 10
Hello dear people,

I need to combine .csv files but remain the original name. I'll explain

I have various .csv files with the following names

01-15 Codes.csv
01-85 Codes.csv
01-30 Codes.csv

I made a search and I can achive it easly using the following code in a Batch File


@echo off
copy *.csv Combined.csv


But I need something more complex. Because I need the code respect the name of the combined files.
For example, If I use the code


@echo off
copy *.csv 01.csv


For the files above in the example, all the file will combine in one file called "01.csv" but this name is because I edited it in the code. I need a code that rename the combined file based on the group of files.

For exmaple, I have the following files:
02-15 Codes.csv
02-85 Codes.csv
02-30 Codes.csv

I need the resulting file is called "02.csv"

Or if I have the following files:
07-15 Codes.csv
07-85 Codes.csv
07-30 Codes.csv

I need the resulting file is called "07.csv"

In other words, I need that the combined file remain the first two characters of one of the initial files.

I cand edit the batch code each time I'll combine the .csv files based on the name of the group of file, but that's not the idea.

I hope you can help me.

Thank you so much!

message edited by Santy4Tas


See More: Combine CSV Files and remain the name

Reply ↓  Report •

#1
July 30, 2019 at 21:31:36
If you don't mind submitting the base in the commandline:
@echo off & setlocal
copy "%1-*.csv" %1.csv
::-------- end snip "test.bat"
f/e: test 07
would get all "07-" csv files into 07.csv.

sort-of tested - seemed to work


Reply ↓  Report •

#2
July 30, 2019 at 21:44:37
Thank you for your answer!

But I'm looking for a code to be coupled for different files.

This because sometime the files will have this name:

01-15 Codes.csv
01-35 Codes.csv
01-18 Codes.csv
.................

and other times the files will have the following name:
02-35 Codes.csv
02-5 Codes.csv
02-16 Codes.csv

I mean, it change the name based on the numeration. I need a code that can conform to all the combinations. It would be a code that remain the first two letter of the original csv files (before combine) because all the csv files has the numeration in the beginning and with two digits, example: 01, 02, 03.....15, 18, 35

message edited by Santy4Tas


Reply ↓  Report •

#3
July 31, 2019 at 00:51:51
ill attempt to make a drag & drop script which works from anywhere, give me like a day to work on it :0

i5-6600K[delid]@4.8GHz@1.456v | 2x4GB Crucial-DDR4-2400MHzCL17@3000MHzCL12@1.45v | Sapphire Nitro+ SE RX 590 8GB@1525Mhz core@1.00v/2235MHz@1500MHz timings BiosModded


Reply ↓  Report •

Related Solutions

#4
July 31, 2019 at 08:28:05
drag & drop one of the .csv files onto merger.bat

but you can also run from the commandline like so
C:\Users\%username%\Desktop\Scripts\Batch\Terminal\Computing\test21\merger.bat "C:\Users\%username%\Desktop\testing21\01-18 Codes.csv"


example using these files(dragging 01-18 onto merger.bat):

01-15 Codes.csv
01-18 Codes.csv
01-35 Codes.csv

outputfile(will be placed in the same directory as merger.bat):

01-output.csv

csv-merger.bat:

@echo off&title .csv merger&setlocal
for /f "tokens=1 delims=-" %%a in ('echo %~n1') do set var=%%a&cd "%~dp1"
for /f "tokens=1,* delims= " %%a in ('dir/b/A-D ^| find "%var%-" ^| find ".csv"') do type "%%a %%b">%TEMP%\%var%-output.csv&goto simple1
:simple1
for /f "skip=1 tokens=1* delims= " %%A in ('dir/b/A-D ^| find "01-" ^| find ".csv"') do for /f "skip=1 tokens=1 delims=|" %%a in ('type "%%A %%B"') do echo %%a>>%TEMP%\%var%-output.csv
move /y "%TEMP%\%var%-output.csv" "%~dp0"
exit/b

i5-6600K[delid]@4.8GHz@1.456v | 2x4GB Crucial-DDR4-2400MHzCL17@3000MHzCL12@1.45v | Sapphire Nitro+ SE RX 590 8GB@1525Mhz core@1.00v/2235MHz@1500MHz timings BiosModded


Reply ↓  Report •

#5
July 31, 2019 at 08:35:11
i've used the crime records from this site as sample data:
https://support.spatialkey.com/spat...

01-15 Codes.csv contains:
cdatetime,address,district,beat,grid,crimedescr,ucr_ncic_code,latitude,longitude
1/1/06 0:00,3108 OCCIDENTAL DR,3,3C ,1115,10851(A)VC TAKE VEH W/O OWNER,2404,38.55042047,-121.3914158
1/1/06 0:00,2082 EXPEDITION WAY,5,5A ,1512,459 PC BURGLARY RESIDENCE,2204,38.47350069,-121.4901858
1/1/06 0:00,4 PALEN CT,2,2A ,212,10851(A)VC TAKE VEH W/O OWNER,2404,38.65784584,-121.4621009

01-18 Codes.csv contains"
cdatetime,address,district,beat,grid,crimedescr,ucr_ncic_code,latitude,longitude
1/1/06 0:00,22 BECKFORD CT,6,6C ,1443,476 PC PASS FICTICIOUS CHECK,2501,38.50677377,-121.4269508
1/1/06 0:00,3421 AUBURN BLVD,2,2A ,508,459 PC BURGLARY-UNSPECIFIED,2299,38.6374478,-121.3846125
1/1/06 0:00,5301 BONNIEMAE WAY,6,6B ,1084,530.5 PC USE PERSONAL ID INFO,2604,38.52697863,-121.4513383
1/1/06 0:00,2217 16TH AVE,4,4A ,957,459 PC BURGLARY VEHICLE,2299,38.537173,-121.4875774

01-35 Codes.csv contains:
cdatetime,address,district,beat,grid,crimedescr,ucr_ncic_code,latitude,longitude
1/1/06 0:00,3547 P ST,3,3C ,853,484 PC PETTY THEFT/INSIDE,2308,38.56433456,-121.4618826
1/1/06 0:00,3421 AUBURN BLVD,2,2A ,508,459 PC BURGLARY BUSINESS,2203,38.6374478,-121.3846125
1/1/06 0:00,1326 HELMSMAN WAY,1,1B ,444,1708 US THEFT OF MAIL,2310,38.60960217,-121.4918375
1/1/06 0:00,2315 STOCKTON BLVD,6,6B ,1005,ASSAULT WITH WEAPON - I RPT,7000,38.55426406,-121.4546045

01-output.csv contains:
cdatetime,address,district,beat,grid,crimedescr,ucr_ncic_code,latitude,longitude
1/1/06 0:00,3108 OCCIDENTAL DR,3,3C ,1115,10851(A)VC TAKE VEH W/O OWNER,2404,38.55042047,-121.3914158
1/1/06 0:00,2082 EXPEDITION WAY,5,5A ,1512,459 PC BURGLARY RESIDENCE,2204,38.47350069,-121.4901858
1/1/06 0:00,4 PALEN CT,2,2A ,212,10851(A)VC TAKE VEH W/O OWNER,2404,38.65784584,-121.4621009
1/1/06 0:00,22 BECKFORD CT,6,6C ,1443,476 PC PASS FICTICIOUS CHECK,2501,38.50677377,-121.4269508
1/1/06 0:00,3421 AUBURN BLVD,2,2A ,508,459 PC BURGLARY-UNSPECIFIED,2299,38.6374478,-121.3846125
1/1/06 0:00,5301 BONNIEMAE WAY,6,6B ,1084,530.5 PC USE PERSONAL ID INFO,2604,38.52697863,-121.4513383
1/1/06 0:00,2217 16TH AVE,4,4A ,957,459 PC BURGLARY VEHICLE,2299,38.537173,-121.4875774
1/1/06 0:00,3547 P ST,3,3C ,853,484 PC PETTY THEFT/INSIDE,2308,38.56433456,-121.4618826
1/1/06 0:00,3421 AUBURN BLVD,2,2A ,508,459 PC BURGLARY BUSINESS,2203,38.6374478,-121.3846125
1/1/06 0:00,1326 HELMSMAN WAY,1,1B ,444,1708 US THEFT OF MAIL,2310,38.60960217,-121.4918375
1/1/06 0:00,2315 STOCKTON BLVD,6,6B ,1005,ASSAULT WITH WEAPON - I RPT,7000,38.55426406,-121.4546045

i5-6600K[delid]@4.8GHz@1.456v | 2x4GB Crucial-DDR4-2400MHzCL17@3000MHzCL12@1.45v | Sapphire Nitro+ SE RX 590 8GB@1525Mhz core@1.00v/2235MHz@1500MHz timings BiosModded


Reply ↓  Report •

#6
July 31, 2019 at 08:37:41
:O Wow! It works perfectly! I was wonder if it's possible to add some line in the code to Delete the initial files and just let the xx-output.csv file?

Reply ↓  Report •

#7
July 31, 2019 at 08:44:02
sure, will do later tonight, or maby tomorrow morning.

gotta catch my train right now ;)

i5-6600K[delid]@4.8GHz@1.456v | 2x4GB Crucial-DDR4-2400MHzCL17@3000MHzCL12@1.45v | Sapphire Nitro+ SE RX 590 8GB@1525Mhz core@1.00v/2235MHz@1500MHz timings BiosModded


Reply ↓  Report •

#8
August 1, 2019 at 04:20:40
tried and made it bit more readable

merger.bat:

@echo off
title .csv merger
setlocal
:readdroppedfile
for /f "tokens=1 delims=-" %%a in ('echo %~n1') do (
	::set var=01
	::in 01-15 Codes.csv
	set var=%%a
	::change dir to directory for caught file from drag & drop, needed for command dir/b/A-D
	cd "%~dp1"
)
:echo1stfile
for /f "tokens=1,* delims= " %%a in ('dir/b/A-D ^| find "%var%-" ^| find ".csv"') do (
	::using %TEMP%, so the for loop doesnt also process newly made files...
	type "%%a %%b">%TEMP%\%var%-output.csv
	::remove old file when done
	del "%%a %%b"
	::exit for loop after 1 try
	goto simple
)
:simple
for /f "tokens=1,2* delims=-" %%a in ('dir/b/A-D') do (
	if "%%a"=="%var%" (
		::write line 1 to %test%
		set /p test=< "%%a-%%b"
		::exit for loop after 1 try
		goto next
	)
)
:next
for /f "skip=1 tokens=1,2* delims=-" %%a in ('dir/b/A-D') do (
	if "%%a"=="%var%" (
		::write line 1 to %test1%
		set /p test1=< "%%a-%%b"
		::exit for loop after 1 try
		goto next1
	)
)
:next1
::if the first line of file 1 & to are the same "header" do:
if "%test%"=="%test1%" (
	::1st file has been writen to %TEMP%\%var%-output.csv in :echo1stfile before
	::if there are headers of file 1 ^& 2 are the same, don't echo 1st line of all other files, else do.
	for /f "tokens=1* delims= " %%A in ('dir/b/A-D ^| find "%var%-" ^| find ".csv"') do (
		for /f "skip=1 tokens=1 delims=|" %%a in ('type "%%A %%B"') do (
			::echo contents of file to "output" file, located in %TEMP%
			echo %%a>>%TEMP%\%var%-output.csv
			::remove file when done
			del "%%A %%B"
		)
	)
) else (
	::no headers, so echo everyting
	for /f "tokens=1* delims= " %%A in ('dir/b/A-D ^| find "%var%-" ^| find ".csv"') do (
		for /f "tokens=1 delims=|" %%a in ('type "%%A %%B"') do (
			echo %%a>>%TEMP%\%var%-output.csv
			del "%%A %%B"
		)
	)
)
::move file from temp dir to dir where inputfile was dragged form
move /y "%TEMP%\%var%-output.csv" "%CD%"
exit/b
:addextralinefeed
::unused code, for files missing a linefeed on last line
for /f "tokens=1,2* delims=-" %%a in ('dir/b/A-D ^| find "%var%-" ^| find ".csv"') do (
	echo. >> "%%a-%%b"
)
exit/b

i5-6600K[delid]@4.8GHz@1.456v | 2x4GB Crucial-DDR4-2400MHzCL17@3000MHzCL12@1.45v | Sapphire Nitro+ SE RX 590 8GB@1525Mhz core@1.00v/2235MHz@1500MHz timings BiosModded


Reply ↓  Report •

#9
August 1, 2019 at 18:35:56
Dear friend, Thanks for your answer. Does the code only work for files called "01-15 Codes.csv" because the idea is that the code can work with any file with two numbers in the begining.

For Example:
01-14 Codes.csv
03-78 Codes.csv
04-18 Codes.csv
05-9 Codes.csv
Etc Etc


Reply ↓  Report •

#10
August 1, 2019 at 19:38:02
Not "crashing" the thread. Just wanted to monkey around. This just uses "more" to
skip the header-lines:
@echo off & setlocal enabledelayedexpansion
for /f "tokens=1,2 delims=-" %%a in ('dir /b ??-*.csv^|findstr "^[0-9][1-9]-*"') do (
if not exist %%a.csv (
set /p k=<%%a-%%b
>%%a.csv echo !k!
)
more +1 %%a-%%b >> %%a.csv
rem missed the part about deleting, also crossed in the mail post #9
rem but don't burn your bridges until you're sure you don't need to go back!
rem del %%a-%%b > nul 2>&1
)

Not drag-droppable :( Must be run from command-prompt in the dir where the csv files are located.
Hide addressed these hazards competently in his solution.

message edited by nbrane


Reply ↓  Report •

#11
August 2, 2019 at 03:52:21
#9 that worries me a bit, it should work for any .csv file which name begins with 2 (or less) numbers or letters & a - sign.

i just tried it on another PC to be sure...

whan does the output file look like if you feed it something else?

i5-6600K[delid]@4.8GHz@1.456v | 2x4GB Crucial-DDR4-2400MHzCL17@3000MHzCL12@1.45v | Sapphire Nitro+ SE RX 590 8GB@1525Mhz core@1.00v/2235MHz@1500MHz timings BiosModded


Reply ↓  Report •

#12
August 3, 2019 at 10:08:35
Hello! Hidden! the code you made for me! works perfectly but just with files called
01
02
03
.....
09

but when the files start to be called
10
11
12
13
14
....etc etc

It don't merge correctly! :(

But in general terms it work perfect!

message edited by Santy4Tas


Reply ↓  Report •

#13
August 4, 2019 at 15:23:58
ill take a look tomorrow

i5-6600K[delid]@4.8GHz@1.456v | 2x4GB Crucial-DDR4-2400MHzCL17@3000MHzCL12@1.45v | Sapphire Nitro+ SE RX 590 8GB@1525Mhz core@1.00v/2235MHz@1500MHz timings BiosModded


Reply ↓  Report •

#14
August 5, 2019 at 06:35:26
hopefully this fixes it, else i gotta do some workaround

merger.bat:

@echo off
title .csv merger
setlocal
:readdroppedfile
for /f "tokens=1 delims=-" %%a in ('echo %~n1') do (
	::set var=01
	::in 01-15 Codes.csv
	set var=%%a
	::change dir to directory for caught file from drag & drop, needed for command dir/b/A-D
	cd "%~dp1"
)
:echo1stfile
for /f "tokens=1,* delims= " %%a in ('dir/b/A-D ^| find "%var%-" ^| find ".csv"') do (
	::using %TEMP%, so the for loop doesnt also process newly made files...
	type "%%a %%b">%TEMP%\%var%-output.csv
	::remove old file when done
	del "%%a %%b"
	::exit for loop after 1 try
	goto simple
)
:simple
for /f "tokens=1,2* delims=-" %%a in ('dir/b/A-D ^| find "%var%-" ^| find ".csv') do (
	if "%%a"=="%var%" (
		::write line 1 to %test%
		set /p test=< "%%a-%%b"
		::exit for loop after 1 try
		goto next
	)
)
:next
for /f "skip=1 tokens=1,2* delims=-" %%a in ('dir/b/A-D ^| find "%var%-" ^| find ".csv') do (
	if "%%a"=="%var%" (
		::write line 1 to %test1%
		set /p test1=< "%%a-%%b"
		::exit for loop after 1 try
		goto next1
	)
)
:next1
::if the first line of file 1 & to are the same "header" do:
if "%test%"=="%test1%" (
	::1st file has been writen to %TEMP%\%var%-output.csv in :echo1stfile before
	::if there are headers of file 1 ^& 2 are the same, don't echo 1st line of all other files, else do.
	for /f "tokens=1* delims= " %%A in ('dir/b/A-D ^| find "%var%-" ^| find ".csv"') do (
		for /f "skip=1 tokens=1 delims=|" %%a in ('type "%%A %%B"') do (
			::echo contents of file to "output" file, located in %TEMP%
			echo %%a>>%TEMP%\%var%-output.csv
			::remove file when done
			del "%%A %%B"
		)
	)
) else (
	::no headers, so echo everyting
	for /f "tokens=1* delims= " %%A in ('dir/b/A-D ^| find "%var%-" ^| find ".csv"') do (
		for /f "tokens=1 delims=|" %%a in ('type "%%A %%B"') do (
			echo %%a>>%TEMP%\%var%-output.csv
			del "%%A %%B"
		)
	)
)
::move file from temp dir to dir where inputfile was dragged form
move /y "%TEMP%\%var%-output.csv" "%CD%"
exit/b
:addextralinefeed
::unused code, for files missing a linefeed on last line
for /f "tokens=1,2* delims=-" %%a in ('dir/b/A-D ^| find "%var%-" ^| find ".csv"') do (
	echo. >> "%%a-%%b"
)
exit/b

i5-6600K[delid]@4.8GHz@1.456v | 2x4GB Crucial-DDR4-2400MHzCL17@3000MHzCL12@1.45v | Sapphire Nitro+ SE RX 590 8GB@1525Mhz core@1.00v/2235MHz@1500MHz timings BiosModded


Reply ↓  Report •

#15
August 13, 2019 at 16:32:32
#13 Thanks for the correction but definitely the codes doesn't merge files with names from 10 codes onward. In fact, the last code you write doesn't merge any file, even the files named 01, 02, 03, 04........I don't know what happened, because the previous code you write was working margin files from 01 to 09 at least.

:(


Reply ↓  Report •

#16
August 13, 2019 at 21:47:45
Oh well, try #2 from this end

@echo off & setlocal enabledelayedexpansion
for /f "tokens=1,2* delims=-" %%a in ('dir /b "??-* codes.csv"^|findstr "^[0-9][0-9]-*"') do (
if not exist %%a.csv (
set /p k=<"%%a-%%b"
>%%a.csv echo !k!
)
more +1 "%%a-%%b" >> %%a.csv
rem do the deletes here next line
echo del %%a-%%b
)
:: -------- end script. left the actual deletion up to you. I never like to code for that.


Reply ↓  Report •

Ask Question