Combine/move one column from CSV to another CSV as last column

Question

I'm having two CSV files. The first one contains two columns (comma separated) the second one contains just one column.

I'm trying to combine them and move the column from the second file as a last column to the first file.

The problem: when I run the script the column goes to the first file but instead of append as last column it completely replace the content of the file.

file_1.csv

file_2.csv

title_1
title_2
title_3
title_4

When combined the file_1.csv should looks like

1,13414214,title_1
6,13414214,title_2
13,13,title_3
3,33129,title_4

The files are with same line numbers.

This is what I have so far

$file = fopen("file_1.csv","w");
$row = 1;
if (($handle = fopen("file_2.csv", "r")) !== FALSE) {
    while (($data = fgetcsv($handle, 1000)) !== FALSE) {
        $num = count($data);
        $row++;
        echo $data[0]. "\n";
        fputcsv($file, $data);
    }
}
fclose($handle);
fclose($file);

Any help is appreciated.

UPDATE: This is the output from the answer

1,13414214
,title_1

6,13414214
,title_2

13,13
,title_3

3,33129
,title_4

For simplicity I would not try to attempt to write to `file_1`. Just read from `file_1` and `file_2` and then write to `file_3`. — KIKO Software, Oct 27 '22 at 07:58
You need to read file_1.csv, currently yo have only opened it — RiggsFolly, Oct 27 '22 at 08:00
@RiggsFolly, 99% will be around 10-20k lines. But there is possibility that sometimes the file can be over 100k lines — Luke, Oct 27 '22 at 08:02
Related: [append new data to existing csv php](https://stackoverflow.com/q/22643669/2943403) and [How to combine multiple CSV files in PHP](https://stackoverflow.com/q/70750255/2943403) and [How to merge new csv file as a new column into an existing csv file using php or even javascript](https://stackoverflow.com/q/61751441/2943403) — mickmackusa, Oct 27 '22 at 09:20

KIKO Software · Accepted Answer · 2022-10-27T08:33:47.473

1

For simplicity I would not try to attempt to write to file_1. Just read from file_1 and file_2 and then write to file_3. Something like:

$file1 = file("file_1.csv");
$file2 = file("file_2.csv");
$lines3 = [];
foreach ($file1 as $line1No => $line1) {
    $line2 = $file2[$line1No] ?? "";
    $lines3[] = trim($line1) . "," . trim($line2);
}
file_put_contents("file_3.csv", implode(PHP_EOL, $lines3));

As you can see, I treat the files as simple text file, not as CVS file, because I don't need to do anything with the data in them.

Note that this will only work somewhat when file_1.csv and file_2.csv contain the same number of lines.

Oh, and also: This might not be the best approach for very large files. Then it would probably be better, albeit a lot slower, to read them line by line.

edited Oct 27 '22 at 08:33

answered Oct 27 '22 at 08:06

KIKO Software

15,283
3
18
33

Thanks. It's working in term of merging them but the way the output looks is a bit off. Can it be on same lines? I have updated my question with the current output – Luke Oct 27 '22 at 08:13
All you need to do is `trim($file2[$line1No];)` to remove its newline – RiggsFolly Oct 27 '22 at 08:15
@Luke Added the trims as RiggsFolly suggested. Does that help? I should text my code before using it in an answer. Let me do that. – KIKO Software Oct 27 '22 at 08:16
Yes, it helps. Now everything is in the line. Thanks! (will test it with large files also) – Luke Oct 27 '22 at 08:18
Yes, indeed, it does. Tested it myself now. I assumed that `file()` would have eliminated the new lines, which was clearly a wrong assumption. – KIKO Software Oct 27 '22 at 08:22
You could add the `FILE_IGNORE_NEW_LINES` flag to `file()` so the new lines are removed, but this might, sometimes, introduce spaces, so I would stick with the `trim()`. – KIKO Software Oct 27 '22 at 08:25
@Luke If you have enough memory in PHP this might still work surprisingly well with big files. – KIKO Software Oct 27 '22 at 08:26
@KIKOSoftware, thanks and yes, I have enough (I believe) memory for now .. will see :) – Luke Oct 27 '22 at 08:32
@Luke: For your sanity I added `?? ""` behind the `$line2 = $file2[$line1No]` just in case file2 is shorter than file1. It will just add a space instead of generating an warning. – KIKO Software Oct 27 '22 at 08:35
Just tested it with ~16.5mb file containing 486k lines and everything went just fine. About a second or so. That's the biggest file that I need to combine. – Luke Oct 27 '22 at 09:03
@Luke Wow. That's really pushing it, but given enough memory certainly possible. I wouldn't do that on a server that's actually in production. – KIKO Software Oct 27 '22 at 09:05
Ah, yes, I'm doing it on my local machine since it is not needed to be on the server. I just need processed file so I'm doing it "offline". – Luke Oct 27 '22 at 09:08

Combine/move one column from CSV to another CSV as last column

1 Answers1