Write data from one excel sheet to another

Question

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 : enter image description here

This is how csv files will look like, one file can have maximum of 800 rows and same number of columns with same heading: enter image description here

why excel? csv is just a text file you can handle with typical text tools. **Hint:** try combining the output of `head -n 1 1abc.csv` and `tail -q -n +2 ?abc.csv` — PA., Jan 07 '15 at 11:21
I wouldn't try doing this using PHPExcel, that's like using a tactical nuke to swat a fly; but a csv file isn't an excel file either, simply a file that MS Excel is capable of reading.... perhaps use native PHP functions like `SPLFileObject` and its `getcsv()` and `putcsv()` methods — Mark Baker, Jan 07 '15 at 11:52
just try with those commands in the command prompt and you'll see what I meant. If need more help, post again with a more detailed question. — PA., Jan 08 '15 at 15:47

score 1 · Answer 1 · answered Jan 07 '15 at 13:14

1

Iin cmd you can use:

copy /b 1filename.csv+2filename.csv filename.csv

copy /b 1abc.csv+2abc.csv abc.csv

answered Jan 07 '15 at 13:14

LPs

16,045
8
30
61

this adds files but how do put in for loop after segregating based on my requirements? Also heading is repeated – Penny Jan 08 '15 at 04:55
In view of your last edit I think the best solution for you is to wrote a little script (c#, java...) that can do all your stuff very quick. – LPs Jan 08 '15 at 07:16

Aacini · Answer 2 · 2015-01-09T22:51:58.947

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

Hey thanks for your suggestions. I have edited my qs and added pictures to make the question more clear. I also tried your solution.I does create unique file per domain but adds content of only one 1st file not all! — Penny, Jan 08 '15 at 04:37
I think its overwriting the new file . It copies all the files but overwrites! — Penny, Jan 08 '15 at 04:47
i tried your code but still i am getting only last file's content in resultant file in the end. It write one file then overwrites with next respective file — Penny, Jan 09 '15 at 05:53

Write data from one excel sheet to another

2 Answers2