Tips and Tricks

Very useful, thank you.

I usually use phpmyadmin, but true, in case one does not have it installed, and does not have shell access (like most of the shared hosting providers), this can come in very handy.

Thanks a lot this saved lots of time! Nice script.

Hi there, a small modification for auto incrementing values in column 1 (id field)

see variable $count.

ola, enrico.

//$con = @mysql_connect($databasehost,$databaseusername,$databasepassword) or die(mysql_error());
//@mysql_select_db($databasename) or die(mysql_error());

$lines = 0;
$queries = “”;
$linearray = array();

$count = 0;

foreach(split($lineseparator,$csvcontent) as $line) {

$count = $count + 1;

$lines++;

$line = trim($line,”\t”);

$line = str_replace(“‘”,”\’”,$line);

$linearray = explode($fieldseparator,$line);

$linemysql = implode(“‘,’”,$linearray);

$query = “insert into `your db table` VALUES(‘$count’,'$linemysql’);”;

$queries .= $query . “\n”;

@mysql_query($query);
}

//@mysql_close($con);

Wow is cool … had idea after read your coding… thank a lot

Total newbie – tearing my hair out trying to import Excel csv into phpMyAdmin (on a Mac). Would love to know exactly where and what I do with this php script…?

Steve,

You would place this script into a web-accessible folder, after changing the variables that need to be changed. Read the comments in the script to find out what you need to specify. Then, call the script up in a web browser, like:

https://www.example.com/path-to-script/simplescvimport.php

it would be a good idea to add prevent headers within the csv from being submitted into the database. Or to create a script that pulls out the first row and creates a db using these values as the column names.

I am getting this error File not found. Make sure you specified the correct path.

Can any one reply fast i have a csv file bbqrest.csv at correct path(root)..

Rengaraj,
the csv file should be in the same place as the PHP file.
Also, you don’t really have to use the same csv file name.
bbqrest.csv is an example, replace this file name with your own.

Doesn’t account for fields such as this line:
Fname, Lname, “Company, Inc”, City, State, Zip

Company and Inc get split into two different columns.

Well, here’s the fix to my previous comment:

insert this in between:

$linearray = explode($fieldseparator,$line);

… insert code snippet below….

$linemysql = implode(“‘,’”,$linearray);

Finds and keeps all “Stuff, More Stuff” together before imploding it.

$a = “”;
$b = “”;
$qoutecount = 0;
for ($x = 0; $x -1) {
$qoutecount++;
if ($qoutecount == 1)
$a = $x; // store the first instance
elseif ($qoutecount == 2) {
$quotecount = 0;
$b = $x; // store the second instance
// THAT DOES IT… compile all elements from $a to $b into $a and ignore elements anything after $a to $b in new array;
$newa = “”; // initialize new string.
for ($z = $a; $z $a && $y

)
; // skip because it falls within the bad array elements
else
$newarray[$y] = str_replace(‘”‘,”,$linearray[$y]);
$linearray = array_values($newarray); // reset the keys to the new array
$x++;
}
}
}

i get the first so much of it cut off and the rest just code in my browser. I know php is working because the rest of the site works.
I did upgrade to php5 does that matter?

brad, if you had downloaded it earlier than last week, replace the ‘< ?’at the top with ‘< ?php’ (remove space)

Robbie, thanks for you addition, it’s quite welcome. Unfortunately, it doesn’t work straight away when I copy it. I think something is missing because the code is spread out over two posts.

Could you check the code as displayed here? Is something missing?

Hi there, nice script thanks. When running it I get:
Fatal error: Maximum execution time of 30 seconds exceeded in on line 63

Is there anything I can do to get around this?

Hi all,

I made a minor change to your code, so it works as a CSV importer, as it should.

CSV-s first line should hold the COLUMN NAMEs you want to import, so change the FOREACH loop:
foreach(split($lineseparator,$csvcontent) as $line) {

$lines++;

$line = trim($line,” \t”);

$line = str_replace(“\r”,”",$line);

/*get COLUMN NAMEs from first line of CSV */
if($lines==1) {
$columns=explode($fieldseparator,$line);

$columnsql=implode(“,”,$columns);

echo $columnsql;
continue;
}

/************************************
This line escapes the special character. remove it if entries are already escaped in the csv file
************************************/
$line = str_replace(“‘”,”\’”,$line);
/*************************************/

$linearray = explode($fieldseparator,$line);

$linemysql = implode(“‘,’”,$linearray);

if($addauto)
$query = “insert into $databasetable ($columnsql) values(‘$linemysql’);”;
else
$query = “insert into $databasetable ($columnsql) values(‘$linemysql’);”;

$queries .= $query . “\n”;

@mysql_query($query);
}

Nice work chief

it did not insert into the database table where i want it inserted..:(

I can only get the first line of my CSV file to be imported into the database.
Here’s a copy of the log file:

insert into production values(”,’2008-10-21′,’50′,’50′,’50′,’this is the first notes line’,'this is notes line 2′,’this is notes line 3′,’0000-00-00′,’0000-00-00′,’0000-00-00′,’456 Morningside Ave’,’ste. 512′,’Brooklyn’,'NY’,'10023′,’Joe’,'Customer 1′);
insert into production values(”,’2008-10-09′,’50′,’50′,’50′,’this is the first notes line’,”,”,’2008-10-09′,’2008-10-14′,’0000-00-00′,’456 Morningside Ave’,’ste. 512′,’Brooklyn’,'NY’,'10023′,’Joe’,'Customer 1′);
insert into production values(”,’2008-10-14′,’25′,’25′,’25′,’NOTES 1′,”,”,’2008-10-14′,’2008-10-17′,’NULL’,'555 somewhere’,’suite 123′,’new york’,'new y’,'10001′,’Joe’,'Name 1′);
insert into production values(”,’2008-10-16′,’45′,’45′,’45′,”,”,”,’2008-10-16′,’2008-10-24′,’NULL’,”,”,”,”,”,’Tom’,”);
insert into production values(”,’2008-10-01′,’34′,’34′,’34′,”,”,”,’2008-10-02′,’2008-10-04′,’2008-10-04′,”,”,”,”,”,’Steve’,”);
insert into production values(”,’2008-10-17′,’50′,’50′,’50′,’this is the first notes line’,”,”,’2008-10-03′,’0000-00-00′,’0000-00-00′,’456 Morningside Ave’,’ste. 512′,’Brooklyn’,'NY’,'10023′,’Joe’,'Customer 1′);
insert into production values(”,’2008-10-18′,’25′,’25′,’25′,’NOTES 1′,”,”,’2008-10-04′,’0000-00-00′,’0000-00-00′,’555 somewhere’,’suite 123′,’new york’,'new y’,'10001′,’Joe’,'Name 1′);
insert into production values(”,’2008-10-19′,’45′,’45′,’45′,”,”,”,’2008-10-05′,’0000-00-00′,’0000-00-00′,”,”,”,”,”,’Tom’,”);
insert into production values(”,’2008-10-20′,’34′,’34′,’34′,’new notes’,”,”,’2008-10-06′,’0000-00-00′,’0000-00-00′,”,”,”,”,”,’Steve’,”);
insert into production values(”,’2008-10-21′,’12′,’12′,’12′,’even newer notes’,”,”,’2008-10-07′,’0000-00-00′,’0000-00-00′,”,”,”,”,”,’mark’,”);
insert into production values(”,’2008-10-22′,’1′,’0′,’0′,”,”,”,’2008-10-08′,’0000-00-00′,’0000-00-00′,”,”,”,”,”,”,”);
insert into production values(”,’2008-10-23′,’2′,’0′,’0′,”,”,”,’2008-10-09′,’0000-00-00′,’0000-00-00′,”,”,”,”,”,”,”);
insert into production values(”,’2008-10-24′,’3′,’0′,’0′,”,”,”,’2008-10-10′,’0000-00-00′,’0000-00-00′,”,”,”,”,”,”,”);
insert into production values(”,’2008-10-15′,’0′,’0′,’0′,’later’,”,”,’2008-10-11′,’0000-00-00′,’0000-00-00′,”,”,”,”,”,”,”);
insert into production values(”,’2008-10-16′,’0′,’0′,’0′,’later still’,”,”,’2008-10-12′,’0000-00-00′,’0000-00-00′,”,”,”,”,”,”,”);
insert into production values(”,’2008-10-17′,’0′,’0′,’0′,’later still’,”,”,’2008-10-13′,’0000-00-00′,’0000-00-00′,”,”,”,”,”,”);
insert into production values(”,’2008-10-18′,’0′,’0′,’0′,’later still’,”,”,’2008-10-14′,’0000-00-00′,’0000-00-00′,”,”,”,”,”,”);
insert into production values(”,’2008-10-19′,’0′,’0′,’0′,’latest’,”,”,’0000-00-00′,’0000-00-00′,’0000-00-00′,”,”,”,”,”,”);
insert into production values(”,’2008-10-01′,’34′,’34′,’34′,’very latest’,”,”,’2008-10-02′,’2008-10-04′,’2008-10-04′,”,”,”,”,”,’bruce’);
insert into production values(”,”);

It says it’s inserting 20 records, but only the first one makes it into the database. Any thoughts?

Hi all,

Thanks for the script. I’m a noob at PHP and MySQL so I really appreciate something like this. I got as far as generating the “File is not writable, check permissions” error message in my browser. Can someone please explain the comment about setting the permission to 777? I’m using an Excel-generated .csv file? Thanks in advance.

Hey Everyone,

Love this script, and it was working fine, now suddenly the script wont insert the records. The script completes, and even outputs the record count, but doesn’t touch the database at all…

Any ideas?

Thanks
C

With regards to the quotes issue, you can just use this:

$linearray = preg_split(“/,(?=(?:[^\"]*\”[^\"]*\”)*(?![^\"]*\”))/”, $line);

Cheers,
Gaz.

it says to load this into the data base

load data local infile ‘PATH’ into table TABLE fields terminated by ‘,’ lines terminated by ‘\n’;

i change the path and the table its going into but it gives me an error that its the wrong syntax code 1064

can anyone give me some advise on what to do

thanks
stan

Hi everyone!

Seem to have the script working…exept that it does not write data to the mysql…
Like with Chris: The script completes, and even outputs the record count, but doesn’t touch the database at all… but tells me: Found a total of 1 records in this csv file.

What do I do wrong (im a noob at php and mysql)

Thanks
Claus

Hi All
I am using drupal to build a new website, the challenge I am having right now is that I need to import data from a CSV file into a mysql database which has shared tables, therefore some of the fields into which I need to import csv data are in different sections of the same database will this script work for me? If not can this csv file be imported using another method?

Please advise, thank you for any assistance.

Regards
Adrian

Thanks for the script. Very understandable.

How can I modify this script so that it fetches and store a csv file from the Internet into MySQL?

Hello,

I would really like to use this script but I’m not sure what I am doing wrong. I get the same problem mentioned above with data not loading into the db. It finds the correct number of rows but nothing shows in the db.

I changed line 88 @mysql_query($query) or die(mysql_error());

and get this message “Column count doesn’t match value count at row 1″

how can i empty my already filled table before inserting the new file?
or can i update an already existing input?

TRUNCATE, UPDATE? how do i get this in the query?

Hi, it tells me the record count but not putting data into database

Hope you can help

Nathan

I need to download data automatically from a remote server. Its a CSV file, and i need to unzip it, and store the data in a mysql database that i created. There are multiple databases. The first section below is one of the databases that someone helped me with and it works wonderfully. I set up a cron job and it downloads it automatically as scheduled. However i have more databases and cant get the others to work.

THIS SECTION BELOW WORKS FINE!!!!
—————————————————————————–

#!/bin/bash

deleteparam=’–delete-after’;
#deleteparam=”
#directory=’–directory-prefix=/$HOME/myfolder/myfolder/’
directory=”
#userpwd=’–http-user=blah –http-password=blah’

toUpper() {
echo $1 | tr “[:lower:]” “[:upper:]”
}

#if [ -z "$1" ]; then
# echo usage: $0 table
#exit
#fi

ZIP=name of data file in remote server
ZIP=`toUpper $ZIP`

#the file within the zip has one less _
DATA=name of table in my mysql database
DATA=`toUpper $DATA`

#rm $TABLE.csv.zip
#rm $DATA.csv

cd /$HOME/myfolder/myfolder/
#######get the residential

wget ‘https://3pv.mlslirealtor.com/Data3pv/DownloadBRSaction.asp?user_code=XXXXXX&password=XXXXXXX&data_type=datazip’ -O $ZIP.csv.zip
sleep 10
#######unzip it
pwd
ls -la

unzip -o $ZIP.csv.zip
sleep 10
#######load it

wget –verbose $deleteparam $directory $userpwd https://mywebsite/myfolder/import.php?table=$DATA
sleep 300

######delete the files

rm $ZIP.csv.zip
rm $DATA.csv
sleep 10
_———————————————————————————
ALL OF THIS ABOVE WORKS WONDERFUL

THIS IS WHAT I NEED HELP WITH
—————————————————————————-
1. I need a script to add to the above file that will download the photos. If you see below i need a script that automatically gets the CURRENT date and time that the script is ran. There server holds pic from the last 7 days and is constantly updated. Once this script is ran it will download the data into the table that i created.

2. Then, I need a script that queries the mysql database looking for entries that have photos and then retrieve the actual photos directly from their remote servers. This csv file DOES NOT download photos, just data that i can use to run a script to retrieve the photos at a given spot. See below.
Below are the instructions I received.

INSTRUCTIONS
Photo data is retrieved by HTTP. The photos data is updated once daily and is available for download as a CSV file. You can then write a script using the data from the CSV file to point back to the images on our image server. YOU MUST SUPPLY A VALUE FOR THE QUERY STRING last_updt > ‘YYYYMMDD HH:MM:SS’ for the URL to return data. The field last_updt is the date value for the last time that a photo was changed on the listing.

You will need to replace the user_code and password place holders (XXXX) with the login credentials provided to you.

Step 1: To retrieve the primary listing photo data CSV file go to the URL below.

https://remotewebserver/Data3pv/DownloadBRSaction.asp?user_code=XXX&password=XXXX&query_str=last_updt%20&gt;%20‘YYYYMMDD%20HH:MM:SS’&data_type=PHOTOS

Step 2: The photos table data should download in a CSV format.

Step 3: Using the Y flags and the ML Number in the data you can link back to our image locations.

Our primary image directory path is as follows:

https://remoteserver/folder/folder/folder/Last3DigistsofML#/listing#.jpg

For example- The primary photo for listing number 1899430 is located at

https://remoteserver/folder/folder/1/430/1899430.jpg

Our additional image directory path structure is as follows:

https://remoteserver/mlsphotos/full/PhotoPosition/Last3DigistsofML#/listing#_photoPosition.jpg

For example- The second photo for listing number 1899430 is located at

https://remoteserver/folder/folder/2/430/1899430_2.jpg

Excellent script! I needed a little jump start for a project I was working on and this did the trick.

I did notice something odd however, I modified the script to work with a form and I did notice that for searching, the last field was getting buggered up because the new line char (either \r or \n or both) was still being recorded in the MySQL database.

To that end, if you replace:
$line = str_replace(“\r”,”",$line);

with:
$line = str_replace(‘\r’,”,$line);

it will keep the return from being a problem, as for the new line, I have added a little something that allows for inserting new records, but updates existing records, so that you don’t end up with duplicate entries. At the top of the code below, you will see where I made the same fix for the actual new line char.

$linemysql = implode(“‘,’”,$linearray);

$newlinemysql = str_replace(‘\n’,”,$linemysql);

if($addauto) {

switch($databasetable) {

case(‘address’):

$duplicatevals = “address = VALUES(address),
city = VALUES(city),state = VALUES(state),

zip = VALUES(zip)”;

break;

case(‘owner’):

$duplicatevals = “ownername = VALUES(ownername),
housetype = VALUES(housetype),

addresskey = VALUES(addresskey)”;

break;

}

$query = “insert into $databasetable values(”,’$newlinemysql’)

ON DUPLICATE KEY UPDATE

$duplicatevals”;

} else {

$query = “insert into $databasetable values(‘$linemysql’)

ON DUPLICATE KEY UPDATE

$duplicatevals”;

}

Also, in my form I added the table name and whether I wanted the table to be [boolean] “addauto” or not as a single input from a drop-down (comma separated), then split the result at the top of the simplecsvimport script.

Finally, you have to remember to set indexes in the database for the, “ON DUPLICATE…” to work, auto increments will not work in this case, so you have to set a secondary index on the auto increment tables, the tables that don’t auto increment and have unique fields work with the single index if that is the route you would like to go.

I hope this helps someone!

Cheers!

Hello,

Made some modifications. My .txt file has 3 columns separated by a “|” (pipe) character. The script displays the file data, counts the rows, but does not enter the data into the database. Any comments?

$csvcontent = fread($file,$size);

fclose($file);

$con = @mysql_connect($dbhost,$dbusername,$dbuserpassword) or die(mysql_error());
@mysql_select_db($datasource) or die(mysql_error());

$lineseparator = “\n”;
$fieldseparator = “|”;

$lines = 0;
$queries = “”;
$linearray = array();

$count = 0;

foreach(split($lineseparator,$csvcontent) as $line) {

$count = $count + 1;

$lines++;

$line = trim($line,” \t”);

$line = str_replace(“\r”,”",$line);

/************************************
This line escapes the special character. remove it if entries are already escaped in the csv file
************************************/
$line = str_replace(“‘”,”\’”,$line);
/*************************************/

$linearray = explode($fieldseparator,$line);

$linemysql = implode(“””,$linearray);

if($addauto)

$query = “insert into INDICATORS values(‘$count’,'$linemysql’);”;

else

$query = “insert into INDICATORS values(‘$linemysql’);”;

$queries .= $query . “\n”;

@mysql_query($query) or die(mysql_error());

echo “$linemysql\n”;
}

i did not get it how it work from a file control. means first browse and choose a file and then submit where i need to modified

This is very Useful code for me..
thanks.

How can i get the browse file instead of typing the full path name..

Please help in this.

thanks

Thanks, this code has really helped!

How would I modify it to allow a form to be displayed in which the user browses and selects the csv file to be imported into the table?

And also how could I make it so that each time a new file is submitted it would override whatever is currently stored in the table?

Many thanks

Hi all,

How do I contain these csv quotes in the date “day,month date,2010″ and the state “prov,state” to a column in my data base? I see many examples but i can’t get it to work. any help would good.

ak,10037591,1,”Tuesday, March 9, 2010 15:55:16″,59.7151,-151.4434,2.7,75.90,32,”Kenai Peninsula, Alaska”
thx