Reputation: 816
I have some .csv files in my folder with names like 1filename.csv
, 2filename.csv
, 3filename.csv
then 1abc.csv
, 2abc.csv
and so on.
I need to merge files with same post name and each file has same no. of columns with same heading.So final file should have only one row with titles for columns and appended with data.
Example: 1. 1filename.csv
, 2filename.csv
, 3filename.csv
should be merged to filename.csv
(where the data is appended one after the other)
2.1abc.csv
, 2abc.csv
will be merged and to form abc.csv
Can I achieve this in batch file or with the help of phpexcel? I want to do in batch file but i am not sure that it is possible.
Thanks
These are the files which needs to be merged I will have 1000s of files splitted with different domains :
This is how csv files will look like, one file can have maximum of 800 rows and same number of columns with same heading:
Upvotes: 1
Views: 6637
Reputation: 67256
EDIT: Small bug fixed
The original code does not take into account that the file names will be processed in alphabetic order, not in post-name groups.
@echo off
setlocal EnableDelayedExpansion
rem I have some .csv files in my folder
rem with names like 1filename.csv, 2filename.csv, 3filename.csv then 1abc.csv, 2abc.csv and so on.
set "postName="
for /F %%a in ('dir /B *.csv ^| sort /+2') do (
rem I need to merge files with same post name
set "name=%%a"
if "!name:~1!" neq "!postName!" (
set "postName=!name:~1!"
rem and each file has same no. of columns with same heading.
rem So final file should have only one row with titles for columns
echo Merging data into: !postName!
copy "%%a" "!postName!" > NUL
) else (
rem and appended with data.
(for /F "skip=1 delims=" %%b in (%%a) do echo %%b) >> "!postName!"
)
)
You always should post a segment of the files or describe its contents at least, otherwise we can only guess about it...
This Batch solution may fail if the files contains special Batch characters, like < > | !
EDIT: Reply to comments
Please, note that the info in a picture can not be copy-pasted as text. You should post data as text enclosed in code tags, like this one:
C:\ dir /b
1www.ais.csv
1www.futureshade.com.au.csv
2www.ais.csv
2www.futureshade.com.au.csv
3www.ais.csv
test.bat
C:\ test
Merging data into: www.ais.csv
Merging data into: www.futureshade.com.au.csv
C:\ dir /b
1www.ais.csv
1www.futureshade.com.au.csv
2www.ais.csv
2www.futureshade.com.au.csv
3www.ais.csv
test.bat
www.ais.csv
www.futureshade.com.au.csv
Note that this solution is designed for filenames with just one digit in the first character, so it fail if there are more than 9 files in the same post name (this is the type of details that you should post since the very begining).
2ND EDIT: Output testing added
The test below show that this solution works as stated:
C:\ dir /b
1www.ais.csv
1www.futureshade.com.au.csv
2www.ais.csv
2www.futureshade.com.au.csv
3www.ais.csv
test.bat
C:\ type 1www.ais.csv
Ad group,Keyword,Currency,Avg. Monthly Search,Competition,Suggested bic,Impr. sh
ar,In account?,In plan?,Extracted from
1AIS,ais,AUD,14800,0.01,3.39,--,N,N
1AIS,medicare,AUD,201000,0.04,2.97,--,N,N
C:\ type 2www.ais.csv
Ad group,Keyword,Currency,Avg. Monthly Search,Competition,Suggested bic,Impr. sh
ar,In account?,In plan?,Extracted from
2AIS,ais,AUD,14800,0.01,3.39,--,N,N
2AIS,medicare,AUD,201000,0.04,2.97,--,N,N
C:\ type 3www.ais.csv
Ad group,Keyword,Currency,Avg. Monthly Search,Competition,Suggested bic,Impr. sh
ar,In account?,In plan?,Extracted from
3AIS,ais,AUD,14800,0.01,3.39,--,N,N
3AIS,medicare,AUD,201000,0.04,2.97,--,N,N
C:\ type 1www.futureshade.com.au.csv
Ad group,Keyword,Currency,Avg. Monthly Search,Competition,Suggested bic,Impr. sh
ar,In account?,In plan?,Extracted from
1FUTURESHADE,ais,AUD,14800,0.01,3.39,--,N,N
1FUTURESHADE,medicare,AUD,201000,0.04,2.97,--,N,N
C:\ type 2www.futureshade.com.au.csv
Ad group,Keyword,Currency,Avg. Monthly Search,Competition,Suggested bic,Impr. sh
ar,In account?,In plan?,Extracted from
2FUTURESHADE,ais,AUD,14800,0.01,3.39,--,N,N
2FUTURESHADE,medicare,AUD,201000,0.04,2.97,--,N,N
C:\ test
Merging data into: www.ais.csv
Merging data into: www.futureshade.com.au.csv
C:\ dir /B
1www.ais.csv
1www.futureshade.com.au.csv
2www.ais.csv
2www.futureshade.com.au.csv
3www.ais.csv
test.bat
www.ais.csv
www.futureshade.com.au.csv
C:\ type www.ais.csv
Ad group,Keyword,Currency,Avg. Monthly Search,Competition,Suggested bic,Impr. sh
ar,In account?,In plan?,Extracted from
1AIS,ais,AUD,14800,0.01,3.39,--,N,N
1AIS,medicare,AUD,201000,0.04,2.97,--,N,N
2AIS,ais,AUD,14800,0.01,3.39,--,N,N
2AIS,medicare,AUD,201000,0.04,2.97,--,N,N
3AIS,ais,AUD,14800,0.01,3.39,--,N,N
3AIS,medicare,AUD,201000,0.04,2.97,--,N,N
C:\ type www.futureshade.com.au.csv
Ad group,Keyword,Currency,Avg. Monthly Search,Competition,Suggested bic,Impr. sh
ar,In account?,In plan?,Extracted from
1FUTURESHADE,ais,AUD,14800,0.01,3.39,--,N,N
1FUTURESHADE,medicare,AUD,201000,0.04,2.97,--,N,N
2FUTURESHADE,ais,AUD,14800,0.01,3.39,--,N,N
2FUTURESHADE,medicare,AUD,201000,0.04,2.97,--,N,N
Upvotes: 0
Reputation: 16243
Iin cmd you can use:
copy /b 1filename.csv+2filename.csv filename.csv
copy /b 1abc.csv+2abc.csv abc.csv
Upvotes: 1