Tag: csv
Nabin Nepal (Starx) csv database mysql phpmyadmin
March 16, 2012

importing a CSV into phpmyadmin

Question by sico87

sico87sico87

I have a CSV that looks like this,

candidate_id,show_on_site,first_name,surname,gender,DOB,showdob,Location,height,eyes,hair_colour,hair_length,accents,unions,training,url,visible,availability
,26,urban talent,Alice,Barry,Female,11 Jan 1942,FALSE,Manchester,5'2,Brown,Black,Mid-length,Native Lancashire,Equity,Urban Talent TV & Drama Workshops,Alice-Barry---5.4.06.jpg,Yes,Yes
,29,urban talent,Angela,Murray,Female,12 Oct 1970,FALSE,Manchester,5'7,Brown,Dark Brown,Long,"Native Manchester, others include - Cheshire, RP, Patois, Standard USA",Equity Member,"BA Acting Studies, Arden School of Theatre<br>Urban Talent TV & Drama Workshops",Angela Murray 1_1.jpg,Yes,Yes
,31,urban talent,Christine,Barton-Brown,Female,4 Jun 1947,FALSE,Manchester,5'0,Hazel,Blonde,Mid-length,"Native Manchester, others include - Liverpool, Cockney, Birmingham, West Country, Standard Scottish, Standard Welch, S Irish",,Manchester School of Acting<br>3 Years at David Johnson Acting Workshops,Christine Barton-Brown web 1.jpg,Yes,Yes
,33,urban talent,Davinia,Jokhi,Female,1 Jul 1979,FALSE,Manchester,5'2,Dark Brown,Dark Brown,Long,"Native Manchester, others include - Liverpool, RP, Lancashire, Birmingham, Cockney, Devon, Geordie, West Country, Glasgow, Edinburgh, South African, Standard & Southern US, Persian, Asian, Indian ~ good ear for accents",,"Manchester School of Acting, with Mark Hudson<br>North Cheshire Theatre College, with David Johnson<Oldham Theatre Workshop",Davinia Jokhi web 4.jpg,Yes,Yes

Is it possible to just insert this data into the existing columns in my database, all I can seem to it insert it as a new table which then has columns name A, B, C , D, E etc.

Answer by Starx

StarxStarx

Using the LOAD DATA INFILE SQL statement you can import the CSV file, but to update you can’t. However, there is a trick you can use.

  • Create another temporary table to use for the import

  • Load onto this table from the CSC

    LOAD DATA LOCAL INFILE '/file.csv'
INTO TABLE temp_table
FIELDS TERMINATED BY ','
LINES TERMINATED BY 'n'
(field1, filed2, field3);

  • UPDATE the real table joining the table

    UPDATE maintable
INNER JOIN temp_table A USING (field1)
SET maintable.field1 = temp_table.fiedl1
Nabin Nepal (Starx) csv mysql php

CSV into MySQL using PHP

Question by user1183307

I’m trying to import data from my students.csv file into mysql using php. The entries in the csv file is in such a way that column (student_number, fname, lname, level) will be inserted into biodata table..

I’m also uploading the student.csv file from my computer.

When I run the page I dont get anything out on the screen.

session_start();
require('includes/dbconnect.php');
require 'includes/header.inc.php';

//check for file upload
if (isset($_FILES['csv_file']) && is_uploaded_file($_FILES['csv_file']['tmp_name'])) {
    //upload directory
    $upload_dir = "C:UsersDOTMANDocumentsstudents.csv";
    //create file name
    $file_path = $upload_dir . $_FILES['csv_file']['name'];
    //move uploaded file to upload dir
    if (!move_uploaded_file($_FILES['csv_file']['tmp_name'], $file_path)) {
        //error moving upload file
        echo "Error moving file upload";
    }
    //open the csv file for reading
    $handle = fopen($file_path, 'r');
    //turn off autocommit and deletethe bio data
    mysql_query("SET AUTOCOMMIT=0");
    mysql_query("BEGIN");
    mysql_query("TRUNCATE TABLE biodata") or die(mysql_error());
    while (($data = fgetcsv($handle, 1000, ',')) !== FALSE) {
        //Access field data in $data array ex.
        $student_number = $data[0];
        $fname = $data[1];
        $lname = $data[2];
        $level = $data[3];
        //Use data to insert into db
        $query = "INSERT INTO biodata (student_number, fname, lname, level)
                  VALUES ('$student_number', '$fname', '$lname', '$level')";
        mysql_query($query) or die (mysql_error());
    }
}

Answer by Starx

StarxStarx

Solution using PHP

$file = 'path/to.csv'; 

$lines = file($file);
$firstLine = $lines[0];
foreach ($lines as $line_num => $line) {
    if($line_num==0) { continue; } //escape the header column
    $arr = explode(",",$line);
    $column1= $arr[0];
    $column2= $arr[1];

    echo $column1.$column2."<br />";
        //put the mysql insert statement here
}
Nabin Nepal (Starx) csv mysql php sql
March 15, 2012

csv to mysql loading

Question by Tzook Bar Noy

Tzook Bar NoyTzook Bar Noy

Im trying to upload this csv file to mysql on my hosting.

The csv is built by two collums:
alias, id

and than each row contains the data.
here is an image
a screenshot of my csv file

But the mysql rejects me.
why is that?

Answer by sam yi

sam yisam yi

You can also open up csv from excel and generate series of insert statements. Not the best solution but might be useful if you’re looking for something quick and dirty.

Answer by Starx

StarxStarx

Solution using PHP

$file = 'path/to.csv'; 

$lines = file($file);
$firstLine = $lines[0];
foreach ($lines as $line_num => $line) {
    if($line_num==0) { continue; } //escape the header column
    $arr = explode(",",$line);
    $column1= $arr[0];
    $column2= $arr[1];

    echo $column1.$column2."<br />";
        //put the mysql insert statement here
}
...

Please fill the form - I will response as fast as I can!