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
 
fluiduk said:
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
Click to expand...

Assuming that "excel database" was a typo and that you actually meant MySQL as in the thread title, here's a breakdown of where to start:

1. Read the csv file into an array (1 line per element) - see file()
2. Set up a db connection - see mysql_connect()
3. For each line in the array, break it up into its constituent fields and assign them to variables - see explode() or strtok()
4. Write an INSERT query to insert your new variables into the correct mysql fields - see mysql_query()

That should be enough to get you started.
 
You could just import directly from csv..

Code: 
LOAD DATA LOCAL INFILE 'file.csv' 
INTO TABLE `table` 
FIELDS TERMINATED BY ',' 
LINES TERMINATED BY '\n';

Though you need access to mysql prompt for that.
 
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
 
Code: 
<?php
$file = array();
foreach(file('filename.csv') as $line)
{
    $file[] = explode('","', trim($line, '"'));
}

?>

That should create an array of arrays. The columns in their own indexes, all stored witin a super array for each row.

e.g. $files[5][7] will hold the 5th row, 7th column data.
 
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:
no MSN or any other form of IM I'm afraid - on here is the best I can do :)

Can you make up some mock-data so I can see what sort of thing to parse? Just ensure you use the same delimeters please. :)
 
Cool, will mock some data up now, will take a while as there is loads of fields, i dont need them all though

Aaron
 
will only need two or three fields. I just literally need to see exactly how it is delimeted as it can sometimes vary.
 
Code: 
<?php

$file = array();
foreach(file('file.csv') as $line)
{
    if (!preg_match('/^(\s|\n)+$/', $line)) $file[] = explode(',', $line);
}

var_dump($file);

?>

should work now..
 
You must log in or register to reply here.
Back
Top Bottom