help on importing csv file to database

[pixeltrace]

guys,

i need help. i am getting some errors importing csv file into my database.

what happens is,

all the data from my csv file is just being inserted into 1 row instead of going

to its respective rows

 

attached is a copy of my csv file.

hope you could help me with this

i am using mysql 4.1.12

 

thanks!

[MadTechie]

erm.. no file attached but we need to see some code ?

 

also try LOAD DATA from MySQL

 

IE

 

LOAD DATA LOCAL INFILE '/tmp/php9vWmPg' INTO TABLE table FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '\\' LINES TERMINATED BY '\n'

 

 

EDIT:Due to a ton of dup posts from you i will no longer be helping

 

read the rules and use bump!

[jitesh]

This code will give a csv data in to array.

 

From array you may insert into DB.

 

<?php
$row = 1;
$handle = fopen("test.csv", "r");
while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
    $num = count($data);
    echo "<p> $num fields in line $row: <br /></p>\n";
    $row++;
    for ($c=0; $c < $num; $c++) {
        echo $data[$c] . "<br />\n";
    }
}
fclose($handle);
?> 

[pixeltrace]

Hi,

 

Thanks for all the help!

 

using this code? how can i insert the items into my database?

[pixeltrace]

hi,

 

i used this script you shared me and this is what it displays

http://www.jobhirings.com/csv/test.php

 

also, i changed the value "1000" to 5774 since this is the total row in mg csv

 

my prob now is, how can i insert this into my database?

need help on this please.

 

thanks so much!

 

[per1os]

I think you dug your own hole on help, I am with Madtechie.

 

Learn to read the rules first.

[pixeltrace]

ey,

 

my apologies on this matter. honestly, the reason why i posted twice because my current connection is slow

im just sniffing currently using my blueberry and during my first post while submitting, i lost my internet connection

and it took some time because i got back again, i posted back because when i went back i didnt saw my first post.

 

i've been posting here eversince and this is the first time that it happened to me. my apologies again

 

thanks!

[MadTechie]

Sighs.. ok heres a basic idea of the next step

of course i don't know your database so this is basic..

 

<?php
$row = 1;
$handle = fopen("test.csv", "r");
while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
    $num = count($data);
    echo "<p> $num fields in line $row: <br /></p>\n";
    $row++;
$SQLq ="";
    for ($c=0; $c < $num; $c++) {
        echo $data[$c] . "<br />\n";
$SQLq .= "$data[$c],";
    }
$SQLq = trim($SQLq, ",");

//SQL part goes here ie insert into table (field1,field2,field3) VALUES ($SQLq);
}
fclose($handle);
?> 

 

while i normal stick to my guns, we all make mistakes but considering this forum is free support i don't like wasting my time trying to help other so by post 3 exact posts it wastes my time..

 

to find your own post click and Show the last topics of this person.

[Barand]

As the file is on your server, omit LOCAL

 

LOAD DATA INFILE '/tmp/php9vWmPg' 
INTO TABLE table 
FIELDS 
    TERMINATED BY ',' 
    OPTIONALLY ENCLOSED BY '"' 
    ESCAPED BY '\\' 
LINES TERMINATED BY '\n'

 

If the csv file doesn't contain data for all the columns that are in the table, or are in a different order, use the optional COLUMNS option

[pixeltrace]

Hi,

 

Thanks again!

i tried using the codes you gave me

<?php
$row = 1;
$handle = fopen("test.csv", "r");
while (($data = fgetcsv($handle, 5774, ",")) !== FALSE) {
    $num = count($data);
    echo "<p> $num fields in line $row: <br /></p>\n";
    $row++;
$SQLq ="";
    for ($c=0; $c < $num; $c++) {
        echo $data[$c] . "<br />\n";
$SQLq .= "$data[$c],";
    }
$SQLq = trim($SQLq, ",");

//---- database
$dbhost = 'localhost';
$dbusername = 'username';
$dbpasswd = 'password';
$database_name = 'MCJoomla';

	$connection = mysql_pconnect("$dbhost","$dbusername","$dbpasswd") 
	or die ("Couldn't connect to server.");

$db = mysql_select_db("$database_name", $connection)
	or die("Couldn't select database.");

$sql = mysql_query("INSERT INTO jos_muse_users (MemberID, ContactID, GivenName, FamilyName, Email, AddressAsEmail, HomePhone, BusinessPhone, Mobile, Gender, DateofBirth ,Address1, Address2, PostalCode, SourceForm, NRIC, Race, Nationality, ChildName, ChildDOB, ChildGender, City, State, Country, SourcePortal)
      VALUES ($SQLq)") or die (mysql_error());

//--- database

fclose($handle);
?> 

 

but i am getting this error

Parse error: parse error, unexpected $ in /var/www/html/memoryworld/csv/test.php on line 33

what does this mean?

 

hope you could help me on this.

thanks!

[pixeltrace]

Hi Barrand,

 

how do i use this?

normally, when i import csv, i just go to the import section, select the csv file in my local pc.

next field, i just left it there, normally by default its utf8

next portion, i skip.

next part is on the bottom,

select csv, check "replace table data with file", check ignore duplicate rows

then i click "go".

 

what is wrong on my procedure? because using this procedure i am getting and error

like

Invalid field count in CSV input on line 1.

also how do i use the code you gave me? and where to run it?

i tried attaching my csv file but its kinda big.

you can just check the csv file here

http://mango.resonance.com.sg/memoryworld/csv/users.csv

 

hope you could help me with this. thanks so much!

[MadTechie]

change

<?php
$sql = mysql_query("INSERT INTO jos_muse_users (MemberID, ContactID, GivenName, FamilyName, Email, AddressAsEmail, HomePhone, BusinessPhone, Mobile, Gender, DateofBirth ,Address1, Address2, PostalCode, SourceForm, NRIC, Race, Nationality, ChildName, ChildDOB, ChildGender, City, State, Country, SourcePortal)
      VALUES ($SQLq)") or die (mysql_error());
?>

 

to

 

<?php
$query = "INSERT INTO jos_muse_users (MemberID, ContactID, GivenName, FamilyName, Email, AddressAsEmail, HomePhone, BusinessPhone, Mobile, Gender, DateofBirth ,Address1, Address2, PostalCode, SourceForm, NRIC, Race, Nationality, ChildName, ChildDOB, ChildGender, City, State, Country, SourcePortal) VALUES ($SQLq)";
echo $query;
$sql = mysql_query($query) or die (mysql_error());
?>

 

and post the results

 

 

as for the load data method you have more columes than fields

[Barand]

LOAD DATA INFILE '/tmp/php9vWmPg' 
INTO TABLE table 
FIELDS 
    TERMINATED BY ',' 
    OPTIONALLY ENCLOSED BY '"' 
    ESCAPED BY '\\' 
LINES TERMINATED BY '\n'
(colname1, colname2, .... , colnameN)

 

The last line is where you specify the actual cols that are in the csv file

 

If the file is on your PC and not on the server, use LOAD DATA LOCAL INFILE and the file is sent from the client to the server.

 

Run it as a script using something like MySQL Query Browser (downloadable from mysql.com)

[pixeltrace]

Hi Barand,

 

I already download and installed MySQL Query Browser.

i tried to execute this

LOAD DATA LOCAL INFILE 'C:\Documents and Settings\pixeltrace\My Documents\jos_muse_users1.csv'
INTO TABLE jos_muse_users 
FIELDS
    TERMINATED BY ','
    OPTIONALLY ENCLOSED BY '"'
    ESCAPED BY '\\'
LINES TERMINATED BY '\n'
(MemberID, ContactID, GivenName, FamilyName, Email, AddressedAsEmail, HomePhone, BusinessPhone, Mobile, Gender, DateofBirth, Address1, Address2, Post

 

its tells me that "No database is selected"

in the right panel, i already click the table where i want to load the csv.

 

how do i solve this? need help. thanks!

[pixeltrace]

any help on this please.

thanks!

[pixeltrace]

Hi Madtechie,

 

sorry for the late update,

this is the error that i am getting

Parse error: parse error, unexpected $ in /var/www/html/memoryworld/csv/test1.php on line 33

and this is the current code that i have

<?php
$row = 1;
$handle = fopen("jos_muse_users1.csv", "are");
while (($data = fgetcsv($handle, 5774, ",")) !== FALSE) {
    $num = count($data);
    echo "<p> $num fields in line $row: <br /></p>\n";
    $row++;
$SQLq ="";
    for ($c=0; $c < $num; $c++) {
        echo $data[$c] . "<br />\n";
$SQLq .= "$data[$c],";
    }
$SQLq = trim($SQLq, ",");

//---- database
$dbhost = 'localhost';
$dbusername = 'username';
$dbpasswd = 'password';
$database_name = 'MCJoomla';

	$connection = mysql_pconnect("$dbhost","$dbusername","$dbpasswd") 
	or die ("Couldn't connect to server.");

$db = mysql_select_db("$database_name", $connection)
	or die("Couldn't select database.");

$query = "INSERT INTO jos_muse_users (MemberID, ContactID, GivenName, FamilyName, Email, AddressAsEmail, HomePhone, BusinessPhone, Mobile, Gender, DateofBirth ,Address1, Address2, PostalCode, SourceForm, NRIC, Race, Nationality, ChildName, ChildDOB, ChildGender, City, State, Country, SourcePortal) VALUES ($SQLq)";
echo $query;
$sql = mysql_query($query) or die (mysql_error());
//--- database

fclose($handle);
?> 

 

 

hope you could still help me on this. thanks!

[MadTechie]

you missed the last }

 

<?php
$row = 1;
$handle = fopen("jos_muse_users1.csv", "are");
while (($data = fgetcsv($handle, 5774, ",")) !== FALSE) {
    $num = count($data);
    echo "<p> $num fields in line $row: <br /></p>\n";
    $row++;
$SQLq ="";
    for ($c=0; $c < $num; $c++) {
        echo $data[$c] . "<br />\n";
$SQLq .= "$data[$c],";
    }
$SQLq = trim($SQLq, ",");

//---- database
$dbhost = 'localhost';
$dbusername = 'username';
$dbpasswd = 'password';
$database_name = 'MCJoomla';

	$connection = mysql_pconnect("$dbhost","$dbusername","$dbpasswd") 
	or die ("Couldn't connect to server.");

$db = mysql_select_db("$database_name", $connection)
	or die("Couldn't select database.");

$query = "INSERT INTO jos_muse_users (MemberID, ContactID, GivenName, FamilyName, Email, AddressAsEmail, HomePhone, BusinessPhone, Mobile, Gender, DateofBirth ,Address1, Address2, PostalCode, SourceForm, NRIC, Race, Nationality, ChildName, ChildDOB, ChildGender, City, State, Country, SourcePortal) VALUES ($SQLq)";
echo $query;
$sql = mysql_query($query) or die (mysql_error());
//--- database
} //<--this one to close the while loop
fclose($handle);
?> 

 

 

[Barand]

In the list of schemata on the right, right-click the one you want and select "Make default schema" in the popup menu