Page 1 of 1

Importing CSV To MySQL

Posted: Thu Jul 27, 2006 6:00 pm
by afoote
feyd | Please use

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]


[b]Hey All...[/b]

Fairly new to PHP but learning.... 

I have a site with a little "members area" and I want to allow people to import some data ([i]a contact list[/i]) into a MYSQL table.

I have this script, see code below, that takes the submitted form, from when the users uploads the photo, and places the info into the table. 

It works just fine, The script also grabs the "list id" from the form and the "member id" fomr the sessoin to include this data in the table as well - this part is working fine as well...

But my problem is this:

It only works if the tables, based on the headers of the CVS file, are the exact same as the database, in the exact order...

Now I don't mind making my users have the CVS header field name match the database, but sometimes it not always in the exact order and/or they are missing a field ([i]for example, they don't the feild "fax number"[/i]).

I am wondering if there is any way I could modify the script to make it so it will like "look" at the headers of the CVS file and match tehm up correctly (so long as the names match), so they don't have to be in the exact order ([i]eg: name before email, I would like it to still work, if they had email before name[/i]).

and if there were any fields missing ([i]say they did'nt have "name"[/i]) the script would just skip it and move on to "email" and import that.

[b]Hopefully this makes sence...[/b] Also, the CSV file could be of any size, 5 record, or 50, so we can't limit it, within reason anyway...

Code: Select all

function process_import() {

global $Member_ID;

	$csvFile = $_FILES['csv_file']['tmp_name'];
 
	if(empty($csvFile)) die('Please select a CSV file to import from');
	
	$extension = explode(".", basename($_FILES['csv_file']['name']));
	$extension = strtoupper( $extension[count($extension) -1] );
	
	if($extension == 'CSV' || $extension == 'TXT') {
		
		$fileArray = file($csvFile);

		$i = 0;
		foreach ($fileArray as $line) {

		$lineArray = explode(',', str_replace('"', '', $line));

				$fieldArray[$i][0] = $lineArray[1];
				$fieldArray[$i][1] = $lineArray[0];
				$fieldArray[$i][2] = $lineArray[2];
				$fieldArray[$i][3] = $lineArray[3];
				$fieldArray[$i][4] = $lineArray[4];
				$fieldArray[$i][5] = $lineArray[5];
				$fieldArray[$i][6] = $lineArray[6];
				$fieldArray[$i][7] = $lineArray[7];
				$fieldArray[$i][8] = $lineArray[8];
			$i++;
		}
		
		$dbConnection = db_connect() or die(sql_error());
		
		$importCount = 0;
		for($i = 0; $i < count($fieldArray); $i++) {
			
			$invalid = false;
			
			if(!ereg("^[^@ ]+@[^@ ]+\.[^@ \.]+$", $fieldArray[$i][0]))
				$invalid = true;
				
			if(!$invalid){
				$sSQL = 'INSERT INTO maillist_users(list_id, user_name, email, address, city, state, zip, phone, phone_other, faxnumber, realtor_id, date_added) ';
				$sSQL .= 'VALUES(' . $_POST['list_id'] . ", '" . $fieldArray[$i][0] . "', '";
				$sSQL .= $fieldArray[$i][1] . "', '";
				$sSQL .= $fieldArray[$i][2] . "', '";
				$sSQL .= $fieldArray[$i][3] . "', '";
				$sSQL .= $fieldArray[$i][4] . "', '";
				$sSQL .= $fieldArray[$i][5] . "', '";
				$sSQL .= $fieldArray[$i][6] . "', '";
				$sSQL .= $fieldArray[$i][7] . "', '";
				$sSQL .= $fieldArray[$i][8] . "', $Member_ID, NOW())";
	
				mysql_query($sSQL) or die(sql_error());
				$importCount++;
			}
		}
		
		print_header();
		echo '<h2 align="center">Import Contacts</h2>';
		echo '<p align="center">' . $importCount . ' contacts were successfully imported.</p>';

	}
	else die('The file you specified is not a valid CSV file');

	print_footer();

}

feyd | Please use

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]

Oh & Ya....

Posted: Thu Jul 27, 2006 6:11 pm
by afoote
The only other thing I can think of is, and I may be totally wrong here, let me know if I am, but I've got this script that will take the CSV file and echo out a Array, like below, just repeates over and over again - for each line in the CSV file..

Is a way I can take this array and place it into the MYSQL table? This way, in a way it will just "match up" the tables rows with the names in the array - this would , I think, solve my problem, that way, no matter what order they were in, or if they were missing a field, it would still work????

Code: Select all

Array {

[Name] => John Smith
[Email] => johsmith@example.com
[Address] => 123 Any Street
[Phone] => 555-1212
[Fax] => 555-1313

}

Posted: Thu Jul 27, 2006 6:17 pm
by Benjamin
FYI - phpMyAdmin has the ability to import csv files.

Posted: Thu Jul 27, 2006 6:19 pm
by afoote
I know that... Actually just figured that out a little while ago, does a great job as well.

but this is for people visiting the site to use , its a php script that takes the form results (the users upload the CVS file via the form). I can't let my members use PhpMyAdmin...

Posted: Thu Jul 27, 2006 6:55 pm
by Benjamin
This should help you. It's not ready to run but I think most of the logic correct. I think this little bit of code will come in handy sometime.

Code: Select all

<?php

// $fieldNames should be the first line of the file with the field names seperated by a space
$fieldNames = explode(' ', $fieldNames);

// set the field name
foreach ($fieldNames as $fieldName)
{
    $fields[] = $fieldName;
}

// fetch all the records..

$lineNum = 0;

foreach ($fileArray as $line)
{

    $lineArray = explode(',', str_replace('"', '', $line));
    $fields = count($lineArray);

    for ($i = 0; $i < $fields; $i++)
    {
        $results[$lineNum][$i] = $lineArray[$i];
    }

    $lineNum++;
}


// start building the query..
$sSQL = "INSERT INTO `maillist_users` ('list_id', ";

foreach ($fields as $fieldName)
{
    $sSQL .= '`' . mysql_real_escape_string($fieldName) . '`, ';
}

$sSQL = substr($sSQL, 0, -2) . ') ';
$sSQL .= 'VALUES(' . mysql_real_escape_string($_POST['list_id']) . ", '";

foreach ($results as $lineNum => $data)
{
    foreach ($data as $fieldValue)
    {
        $sSQL .= mysql_real_escape_string($fieldValue) . "', '";
    }

    $sSQL .= $Member_ID . "', NOW())";

    // insert them into the database..
    mysql_query($sSQL) or die(sql_error());
}
?>

Thanks...

Posted: Fri Jul 28, 2006 1:27 pm
by afoote
Hey, Thanks for your reply, still a little lost but I will see what I can do what you gave me, like I said still new at this, but doing my best to learn...

What Is Wong???

Posted: Wed Aug 02, 2006 12:04 pm
by afoote
Hey All...

I've got this code and I've played around with it forever now but nothing seams to work, I have no idea what is wrong or how to correct.

It is suposed to take the imported CVS file and:

1. Read the first line to fine the "field names" in the CVS header file
2. Then make an array of all the other lines.
3. Then inport the script into the DB Table.

Note: The DB Table, has 3 other rows that are not included in the CVS file (from the headers):
  • "member_id" - the unique ID of the member who is logged into my site and uploading / importing the CVS file. This is passed via the form where they upload it.

    "list ID" - the id od the list that are importing into - each member of my site may have more htan one list - that is in another DB table. This is passed via the form where they upload it.

    "date_added" - the date.
I keep getting the following error:

Code: Select all

1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''list_id') VALUES(267, 'user_name', 'email', 'address', 'city',
The Script:

Code: Select all

function process_import() {

global $Member_ID;

	$csvFile = $_FILES['csv_file']['tmp_name'];
 
	if(empty($csvFile)) die('Please select a CSV file to import from');
	
	$extension = explode(".", basename($_FILES['csv_file']['name']));
	$extension = strtoupper( $extension[count($extension) -1] );
	
	if($extension == 'CSV' || $extension == 'TXT') {

$fileArray = file($csvFile);
		
$fieldNames = explode(',', $fieldNames);

foreach ($fieldNames as $fieldName)
{
    $fields[] = $fieldName;
$lineNum = 0;
}

foreach ($fileArray as $line)
{

    $lineArray = explode(',', str_replace('"', '', $line));
    $fields = count($lineArray);

    for ($i = 0; $i < $fields; $i++)
    {
        $results[$lineNum][$i] = $lineArray[$i];
    }

    $lineNum++;
}

$dbConnection = db_connect() or die(sql_error());

$sSQL = "INSERT INTO `maillist_users` ('list_id', ";

foreach ($fields as $fieldName)
{
    $sSQL .= '`' . mysql_real_escape_string($fieldName) . '`, ';
}

$sSQL = substr($sSQL, 0, -2) . ') ';
$sSQL .= 'VALUES(' . mysql_real_escape_string($_POST['list_id']) . ", '";

foreach ($results as $lineNum => $data)
{
    foreach ($data as $fieldValue)
    {
        $sSQL .= mysql_real_escape_string($fieldValue) . "', '";
    }

    $sSQL .= $Member_ID . "', NOW())";

    mysql_query($sSQL) or die(sql_error());
}
		print_header();
		echo '<h2 align="center">Import Contacts</h2>';
		echo '<p align="center">The contacts were successfully imported.</p>';

	}
	else die('The file you specified is not a valid CSV file');

	print_footer();

}

Posted: Wed Aug 02, 2006 12:07 pm
by RobertGonzalez
You're using single quotes (') on your field names. Use backticks(`) instead. The quotes are making mysql choke.

Code: Select all

$sSQL = "INSERT INTO `maillist_users` ('list_id', ";
Should be:

Code: Select all

$sSQL = "INSERT INTO `maillist_users` (`list_id`, ";

thanks, some progress..

Posted: Wed Aug 02, 2006 1:03 pm
by afoote
Hey, Thanks

That got ride of that error, so some progress --- but now there is a new error..

Code: Select all

Column count doesn't match value count at row 1
I think I know what the problem is, however I just am not sure how to fix it, I could be wrong, but I think it has to do with those extra DB fields that are not in the actual CSV file... eg: "memeber_id and" "date_added".

If you look at the code above, you will the line:

Code: Select all

$sSQL .= $Member_ID . "', NOW())";
I think this is where it puts the actual values in...

But if you look above it, where it creeated the "feild names" from the CSV file, it does create one for 1 of the 3 extra fields "list_name", but I don't see where it does this for "memeber_id and" "date_added"...

I've tried adding something to this area but just get error message, any idea on where this goes? or am I totally wrong here.

Posted: Wed Aug 02, 2006 1:06 pm
by RobertGonzalez
That means that you are trying to insert more or less values than are in the table. Echo out your SQL statement instead of sending it to the DB so you can see the statement that the DB sees. This should give you some insight as to what is going on.

Posted: Wed Aug 02, 2006 1:41 pm
by afoote
Ya, the MySQL statement is totally message up.... it's not getting the fields names property from the CSV file only the "list_id" and as I figured it was not doing the other two ones "member_id" and "date" , instead its putting the "field names as values".

Code: Select all

INSERT INTO `maillist_users` (`list_id`) VALUES(267, 'user_name', 'email', 'address', 'city', 'state', 'zip', 'phone', 'phone_other', 'faxnumber', 'notes', '\n', '5', NOW())
Now I am even more lost than even :-( .... Why is it so hard to import a CVS file...

Can anyone shed any light, or help me locate a script I can purcahse even... I need this done ASAP, even if I need to buy somethign I will.