Solved

PHP - Open 2 CSV files and update the second CSV file

Posted on 2013-10-29
4
977 Views
Last Modified: 2013-10-31
I have 2 huge CSV files. The first CSV file will look something like below.

jack   1 2 3 4
john   5 6 7 8
mark   8 7 9 10
mike   1 3 4 7
helen  3 4 5 6

Open in new window


The second CSV file will look something like below.

1 2  \t      12 23 45
3 5  \t      13 45 57
1 4  \t      12 98 88

Open in new window


The format of the second CSV file would be set of values (like 1,2 separated by spaces) and then a tab delimiter followed by values separated by spaces.

In the second CSV file all I need to do is, replace the values like 1,2,3 etc with the corresponding column values of the first CSV file. So, after replacing the values, the second CSV file should look like, For example, the first row in the second CSV file has 1 and 2 which corresponds to Jack and John in the first CSV file. So the final output that am expecting is something like this.

jack john     12 23 45
mark helen    13 45 57
jack mike    12 98 88

Open in new window


I was trying to use PHP and trying to do something like this.

$file = fopen("secondfile.csv","r");
//I am reading till tab space to get the values that needs replacement. 
while ($line = fgetcsv($file, 0, "\t") !== false)
  {
    //I should read till tab spce and replace it with the values from firstfile.csv
  }

fclose($file);
?>

Open in new window


I am not able to figure out a way on how to do this problem. Can someone please guide me in the right direction?
0
Comment
Question by:ramesh4046
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 43

Accepted Solution

by:
Chris Stanyon earned 500 total points
ID: 39611258
Have a play around with this and see how you get on:

<?php 
$csv1 = array();
$csv2 = array();

//read first column of file 1 into array
$file = fopen("csv1.txt","r");
while ($line = fgetcsv($file, 0, " ")):
	$csv1[] = $line[0];
endwhile;
fclose($file);



//read file 2, line by line, splitting on Tab
$file = fopen("csv2.txt","r");
while ($line = fgetcsv($file, 0, "\t")):
	//split the pre-tab content on space
	$matches = explode(" ", $line[0]);
	
	//loop through the pre-tab data
	$newLine = array();
	foreach ($matches as $match):
		//read the corresponding data from the file 1 array 
		if (array_key_exists($match-1,$csv1)):
			$newLine[] = $csv1[$match-1];
		endif;
	endforeach;
	//add the data from file 2
	$newLine[] = $line[1];
	
	//join it all together with a space and add the new info to an array
	$csv2[] = implode(" ", $newLine);
endwhile;
fclose($file);



//now open a file for writing
$file = fopen("csv3.txt","w");

//loop through the newly formed lines
foreach ($csv2 as $line):
	//write to the file
	fwrite($file, $line . PHP_EOL);
endforeach;

fclose($file);
?>

Open in new window

0
 
LVL 110

Expert Comment

by:Ray Paseur
ID: 39611770
CSV files contain data fields that are usually separated by commas, but there is a conspicuous lack of commas in the data samples.  Any chance we could see a link to the actual data or a subset of the actual data, so we can read the files from a data source?
0
 
LVL 110

Expert Comment

by:Ray Paseur
ID: 39611904
I have to leave this question now.  This code tests out given the information in the OP.

http://www.laprbass.com/RAY_temp_ramesh4046.php

<?php // RAY_temp_ramesh4046.php
error_reporting(E_ALL);
echo '<pre>';

// SEE http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/Q_28280262.html

// SIMULATE READING THESE FILES WITH file_get_contents()
$file1 = <<<EOD
jack   1 2 3 4
john   5 6 7 8
mark   8 7 9 10
mike   1 3 4 7
helen  3 4 5 6
EOD;

$file2 = <<<EOD
1 2  \t      12 23 45
3 5  \t      13 45 57
1 4  \t      12 98 88
EOD;

// TURN FILE 1 INTO A USEFUL DATA STRUCTURE
$data1    = array();
$data1[0] = NULL;
$arr = explode(PHP_EOL, $file1);
foreach ($arr as $str)
{
    // ARRAY NUMERIC INDEX WILL MATCH NUMBERS EXPECTED IN FILE 2
    $data1[] = current(explode(' ', $str));
}

// PROCESS FILE 2
$data3 = array();
$arr = explode(PHP_EOL, $file2);
foreach ($arr as $str)
{
    $out  = NULL;

    // SPLIT ON TAB AND SAVE THE RIGHTMOST DATA STRING
    $sub  = explode("\t", $str);
    $ends = trim(end($sub));

    // SPLIT ON BLANK
    $nums = trim($sub[0]);
    $nums = explode(' ', $nums);

    // USE INDEX NUMBER TO CHOOSE NAME
    foreach ($nums as $num)
    {
        $out .= $data1[$num] . ' ';
    }

    // RECOVER THE RIGHTMOST DATA STRING AND SAVE THE ELEMENT IN THE ARRAY
    $out .= $ends;
    $data3[] = $out;
}

// SHOW THE WORK PRODUCT
print_r($data3);

// WRITE: http://php.net/manual/en/function.file-put-contents.php

Open in new window

HTH, ~Ray
0

Featured Post

Don't Cry: How Liquid Web is Ensuring Security

WannaCry is just the start. Read how Liquid Web is protecting itself and its customers against new threats.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Browsers only know CSS so your awesome SASS code needs to be translated into normal CSS. Here I'll try to explain what you should aim for in order to take full advantage of SASS.
Have you tried to learn about Unicode, UTF-8, and multibyte text encoding and all the articles are just too "academic" or too technical? This article aims to make the whole topic easy for just about anyone to understand.
The purpose of this video is to demonstrate how to integrate Mailchimp with Facebook. This will be demonstrated using a Windows 8 PC. Mailchimp and Facebook will be used. Log into your Mailchimp account. : Click on your name. Go to Account Setti…
The purpose of this video is to demonstrate how to integrate Mailchimp with WordPress, by placing a Mailchimp signup form on a WordPress Page or Post. This will be demonstrated using a Windows 8 PC. Mailchimp will be used. Log into your Mailchi…

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question