Inserting CSV to Mysql with PHP

fluiduk

fluiduk

fluiduk

Associate
Joined
13 Nov 2003
Posts
1,567
Location
Manchester
Hi All

Working on a script that generates packing slips from our ebay shop and uploads a collection request file to parcelforce

For this I need to upload a CSV file into an excel database. What is the best way of doing this, does any one have any feedback on this?

All help appreciated

Thanks
Aaron
 
Thanks for the input so far guys, been very helpful

The problem that I have is that the file we get from ebay has loads of other useless stuff in it, ie empty rows and a summary row that do not contain any useful data.

How can I get the script to ignore/delete these as I dont want to have to remove this stuff manually unless I really need to

Thanks
Aaron
 
I have got the data into php now using the file implode and I have split it into individual strings, however this only gives me the data for the first row (which I need to ignore coincidentally). How do I loop this for each row in the file?

Thanks
Aaron
 
Hmm, not sure thats working, do I need to change it to set the delimiter, in this case a comma.

Here is my code, its not outputting any data

Code: 
<?php
$file = array();
foreach(file('http://development.fluiduk.co.uk/****.csv') as $line)
{
    $file[] = explode('","', trim($line, '"'));
}
echo $file[1][1];
echo $files[5][7];

?>
 
It doesnt work locally either.

If I echo out $file it just says Array
And if I echo out $line, it gives me the last line from the file, so It can read the file

Aaron
 
Gives me

NULL

EDIT

if i change files to file it does dump the all the data. If you msn me I will give you the URL so you can see what it dumps, dont really want to put it on here

Aaron
 
Last edited:
Cool, will mock some data up now, will take a while as there is loads of fields, i dont need them all though

Aaron
 
Before I leave you in a peace

What would be the best way to enter that data into the DB, im guessing I will need to loop an insert command?

How would you go about this?

Sorry to be a pain, this a bit beyond my skillset lol

Aaron
 
It will also have the same amount of columns, but obviously different amount of rows

How would I make a look that inserts the data, I can do the insert query, just not the looping

Aaron
 
Heya

Tried that query, its giving me errors, my code is

Code: 
mysql_select_db($database_wp_data, $wp_data);
$query = "INSERT INTO `addresses` (`id`,`1`,`2`,`3`,`4`,`5`,`6`,`7`,`8`,`9`,`10`,`11`,`12`,`13`,`14`,`15`,`16`,`17`,`18`,`19`,`20`,`21`,`22`,`23`,`24`,`25`,`26`,`27`,`28`,`29`,`30`) VALUES ";

foreach (file('uploads/'.$filename.'') as $line)
{
    $query .= '(';
    $start = true;
    foreach(explode(',', $line) as $col)
    {
        if (!$start)
        {
            $query .= ', ';
        }
        $start = false;
        $query .= "'" . mysql_real_escape_string($col) . "'";
    }
}

$query .= ')';

echo htmlentities($query);
//mysql_query($query);


mysql_query($query, $wp_data) or die(mysql_error());

The error I am getting is
'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 '('Sales Record Number', 'User ID', 'Buyer Fullname', 'Buyer Address 1', 'Buyer A' at line 1'

Sorry to be a pain.

I did have another query for entering the data which seemed to work but then it started missing fields and inserting fields in the wrong order for no reason :(
 
Neither of those have fixed it :(

Thanks though

EDIT - I have found out what was causing the random empty fields and data in the wrong order, some people have used comas in their address fields, eek
 
Last edited:
Another quick issue for you php demigods.

If I have a csv that has wrapped each field in a double quotes how would I strip these out, is it something I need to set in the upload options?

Thanks
Aaron
 
You must log in or register to reply here.
Back
Top Bottom