How to update csv column names with database table header

Question

I am facing this problem some past days and now frustrate because I have to do it.

I need to update my CSV file columns name with database table header. My database table fields are different from CSV file. Now the problem is that first I want to update column name of CSV file with database table headers and then import its data with field mapping into database.

Please help me I don't know how I can solve this.

This is my php code:

$file      = $_POST['fileName'];
$filename  = "../files/" . $file;
$list      = $_POST['str'];
$array_imp = implode(',', $list);
$array_exp = explode(',', $array_imp);
$fp        = fopen("../files/" . $file, "w");
$num       = count($fp);

for ($c = 0; $c < $num; $c++) {
    if ($fp[c] !== '') {
        fputcsv($fp, $array_exp);
    }
}

fclose($fp);


require_once("../csv/DataSource.php");
$path = "../files/test_mysql.csv";
$dbtable = $ext[0];

$csv = new File_CSV_DataSource;
        $csv->load($path);
        $csvData = $csv->connect();
        $res=''; 
        foreach($csvData  as $key)
        {  print_r($key[1]);
            $myKey ='';
            $myVal='';
            foreach($key as $k=>$v)
            { 
                $myKey .=$k.',';
                $myVal .="'".$v."',";

            }
            $myKey = substr($myKey, 0, -1);
            $myVal = substr($myVal, 0, -1); 
            $query="insert into tablename($myKey)values($myVal)";
            $res=  mysql_query($query);

And what specific part are you having a problem with? Is this supposed to happen in PHP? Using a manual import? You have not really posed a specific answerable *question*. — deceze, Dec 14 '12 at 07:33
I am using php script to import csv file and the problem is coming when i try to update csv file columns names with database table headers. It removes complete data from csv file and insert only table headers. — user1523311, Dec 14 '12 at 07:45
Then give us something to help with. No code, vague question, nothing to help. — deceze, Dec 14 '12 at 09:35
Please put that code into your question, this is unreadable. — deceze, Dec 14 '12 at 09:41
In $filname there is my csv file with data and into $list there is table headers and i am using fputcsv code to update the csv columns but it removes all the data from table and insert only table headers. — user1523311, Dec 14 '12 at 09:48
i have put my code with question please help me out if you can. — user1523311, Dec 14 '12 at 10:14

score 1 · Answer 1 · edited May 23 '17 at 12:21

You have got an existing file of which the first line needs to be replaced.

This has been generally outlined here:

Overwrite Line in File with PHP

Some little explanation (and some tips that are not covered in the other question). Most often it's easier to operate with two files here:

The existing file (to be copied from)
A new file that temporarily will be used to write into.

When done, the old file will be deleted and the new file will be renamed to the name of the old file.

Your code does not work because you are already writing the new first line into the old file. That will chop-off the rest of the file when you close it.

Also you look misguided about some basic PHP features, e.g. using count on a file-handle does not help you to get the number of lines. It will just return 1.

Here is step by step what you need to do:

Open the existing file to read from. Just read the first line of it to advance the file-pointer (fgets)
Open a new file to write into. Write the new headers into it (as you already successfully do).
Copy all remaining data from the first file into the new, second file. PHP has a function for that, it is called stream_copy_to_stream.
Close both files.

Now check if the new file is what you're looking for. When this all works, you need to add some more steps:
Rename the original file to a new name. This can be done with rename.
Rename the file you've been written to to the original filename.

If you want, you then can delete the file you renamed in 5. - but only if you don't need it any longer.

And that's it. I hope this is helpful. The PHP manual contains example code for all the functions mentioned and linked. Good luck. And if you don't understand your own code, use the manual to read about it first. That reduces the places where you can introduce errors.

Now i need to check duplicate values that if there are duplicate values into csv file matched with database table then i don't want to insert it.i want to insert only different record which are not inserted into database table.I have paste the insertion code above. — user1523311, Dec 17 '12 at 11:33
this code insert the csv values successfully but do not match for duplicate values.I need to match the csv file "ID" column name values with table field "ID". — user1523311, Dec 17 '12 at 11:39
@user1523311: Please use the search, mysql has something like insert ignore and similar features that can be used in such cases. — hakre, Dec 17 '12 at 12:35

score 0 · Answer 2 · answered Dec 14 '12 at 12:27

If you are managing to insert the table headers then you're half way there. It sounds to me like you need to append the data after the headers something like:

$data = $headers;

if($fp[c]!=='')
{ $data .= fputcsv($fp, $array_exp); }

Notice the dot '.' before the equals '=' in the if statement. This will add none blank $fp[c]values after the headers.

How to update csv column names with database table header

2 Answers2

Linked