Insert records from a local csv file

[freecrm]

Using MySQL/php.

All I need to do is to allow a user to insert records into a MySQL table from a .csv file on his/her local computer...

Sounds simple..

Hmmm...:nuts:

I have looked into DATA LOAD LOCAL INFILE, but aparently, this only works from the server in which the code/data resides.

Do I have to upload a the file first?

Anyone done this before?

[Scoochi2]

yeah, the csv file will need to be uploaded onto *a* server. Not necessarily your server, any will do so long as your script has the permissions needed to read it.
Once the script is online, you can do something along the lines of the following in order to convert the file into an array in your PHP script:

$file = 'location of file';
$array = explode(',',file_get_contents($file)); 

Then, it's as simple as using a foreach to get all values of $array into your database

Alternatively, use a textarea form and get the list from $_POST

[freecrm]

yeah, the csv file will need to be uploaded onto *a* server. Not necessarily your server, any will do so long as your script has the permissions needed to read it.
Once the script is online, you can do something along the lines of the following in order to convert the file into an array in your PHP script:

$file = 'location of file';
$array = explode(',',file_get_contents($file)); 

Then, it's as simple as using a foreach to get all values of $array into your database

Alternatively, use a textarea form and get the list from $_POST

Cool - thanks - I'll get back to you when I've done some dev and testing.

P.S. - like the text area option - this will avoid many formatting issues.

[Scoochi2]

Also remember to use the trim function if the list might include whitespace that you don't want to keep

[freecrm]

OK - finally come back to this and I'm still struggling...

And to be honest, I don't know much about arrays or foreach!!!

I have put in the following:

<?php
mysql_select_db($database_freecrm, $freecrm)
or die(mysql_error()); 

if ((isset($_POST["MM_insert"])) && ($_POST["MM_insert"] == "form1")) {

$file=$_POST['filepath'];
$arrfile = explode(',',file_get_contents($file)); 

        foreach($arrfile as $value){
             echo $value.'<br />';
            $insert="INSERT INTO TEST (FIRSTNAME, LASTNAME, COMPANY) VALUES ('$value')";
            mysql_query($insert) OR die(mysql_error());
        }

}
?>

I'm getting an error code

"FIRSTNAME"
Column count doesn't match value count at row 1


One thing I noticed - this is surely just returning one value for each DB record, whereas the csv file could contain several columns (does this have to be specified?)

As you can see, I am trying (very trying) but I'm not even sure I'm going in the right direction.

[mattura]

well your insert columns and values need to match for a start:

$fnam=?;
$lname=?;
$comp=?;
INSERT INTO `test` (`firstname`,`lastname`,`company`) VALUES ('$fnam','$lnam','$comp'); 

I'm not sure about the state of your array when you are in that loop, so I put question marks. It may be $value[0], $value[1], $value[2] for example.

Do the values have to be in separate columns? Sometimes I have a database column which is a text field and consists of comma separated values. I can then SELECT just one column, and make php do the hard work using explode() and implode().
It depends on what you are storing.

Edit:

Oh, show an example of your csv - I think you will have to explode() differently. Perhaps on line breaks, or every third record or something, then within the foreach loop another explode() on commas.

If you have:

joe,bloggs,amtex\n john,smith,acme\n james,black,comtech\n

you need to have

$triplet=explode("\n",$);
foreach($triplet as $vals) {
 $fnam=$vals[0];
 $lnam=$vals[1];
 $comp=$vals[2];
} 

if in doubt, use print_r($triplet) to find out what your array looks like
Edit:
Hey, I found this and thought of you:
http://docs.php.net/manual/en/function.str-getcsv.php
have fun!

[freecrm]

Thanks Guys..

For starters, my file is a comma seperated values file as follows (Copied directly from notepad but with \n at new lines):

For ease of testing, this file is located at www.freecrm.x10hosting.com/testdata.csv

"FIRSTNAME","LASTNAME","COMPANY"\n
"Joe","Bloggs","Joe Bloggs Ltd"\n
"Jane","Bloggs","Joe Bloggs Ltd"\n
"John","Doe","John Doe Ltd"\n

My code now looks like this...

//connect to db or show error
mysql_select_db($database_freecrm, $freecrm)
or die(mysql_error()); 
    //if form hidden field returns a value, execute the following script
    if ((isset($_POST["MM_insert"])) && ($_POST["MM_insert"] == "form1")) {
        //allocate posted file path to variable
        $file=$_POST['filepath'];
        //load file and split into array
        $arrfile = explode(',',file_get_contents($file)); 

        //loop through each line
        foreach($arrfile as $value){
        
            //define column values
            $firstname=value[1];
            $lastname=value[2];
            $company=value[3];
            
            //insert into db
            $insert="INSERT INTO TEST (FIRSTNAME, LASTNAME, COMPANY) VALUES ('$firstname','$lastname','$company')";
            mysql_query($insert) OR die(mysql_error());
        }

    } 

I have tried several versions of $something=$value[1] but I can't get it right. The square brackets are returning an error.

Parse error: syntax error, unexpected '[' in /home/freecrm/public_html/crmimexport/contactimport.php

I have tried with (), and without brackets but no difference.

Just one thing to bear in mind, field (column) 1 is an autoincrement Integer ID and not specified in the csv file.

I checked out the page on the php site but it's all gobbledegook to me!!! I would prefer to understand what I'm doing rather than just copy lines and lines of strange code...

[Scoochi2]

The following function takes a filename as the only parameter, and returns an array.
Use as follows:
$data = explode_by_lines('testdata.csv');

<?php
function explode_by_lines($filename)
  {
  $data_1 = array();
  foreach (file($filename) as $array)
    {
    $data_1[] = explode(",",str_replace('"','',$array));
    }
  return $data_1;
  }
$data = explode_by_lines('testdata.csv');
print_r($data);
?>

The above will output

Array
(
    [0] => Array
        (
            [0] => FIRSTNAME
            [1] => LASTNAME
            [2] => COMPANY

        )

    [1] => Array
        (
            [0] => Joe
            [1] => Bloggs
            [2] => Joe Bloggs Ltd

        )

    [2] => Array
        (
            [0] => Jane
            [1] => Bloggs
            [2] => Joe Bloggs Ltd

        )

    [3] => Array
        (
            [0] => John
            [1] => Doe
            [2] => John Doe Ltd

        )

)

[freecrm]

Thanks Scoochi

I have put this code in but am not getting anywhere with it.

In your example, where do i insert the file path from the form?

You have references to $filename (which is testdata.csv but could be any number of names) and also to the filename itself.

I have tried various combinations but I continue to get the same error.

Warning: file(filepath) [function.file]: failed to open stream: No such file or directory in /home/freecrm/public_html/crmimexport/contactimport.php on line 27 [foreach (file($filename) as $array)]

Is this becuase it only works for files on your own server?

I need it to work for any hosted file (i.e. for a user.)

I'm amazed there isn't a free script for this...

[Scoochi2]

It should work for any file. Just make sure you use an absolute rather than a relative path.
That being said, it depends on how the sever the script is hosted on whether it will for for files hosted elsewhere.
I think on X10 you need the intermediate level PHP (at least).

You do not need to change the function at all. You specify the filename when you call the function. For example:

$data = explode_by_lines('http://arandomdomain.com/directory/testdata.csv');