csv upload to mysql

looking for a good tutorial

[willybfriendly]

I have a need to write a script that will upload a csv file on the local computer to a mysql db on the server.

I know this is possible in phpmyadmin, but I need to write something more user friendly for the neophytes I am developing this for.

Have looked for a tutorial covering this without success. Anyone know of a decent tutorial, or a script I can disect and adapt?

Tutorial would be best, since it is easier to adapt if I really understand what is taking place.

WBF

[ergophobe]

Does the one [webmasterworld.com] in the forum library [webmasterworld.com] to it for you?

It won't cover the file upload part of it, but it should take you through the rest.

[Elijah]

Just recently, I worked on doing exactly the same thing you are trying to do.

Here is some of the relevant code that I used:

1. HTML file upload form (simplified):

<form action="my_script.php" enctype="multipart/form-data" method="POST">
<input name="csvfile" type="file" />
<input type="submit" value="Submit" />
</form>

2. Code for to call for each uploaded file:

<?php
CSV2DB('csvfile');
?>

PHP function converting CSV file to MySQL row:

<?php
/**
 * Converts an uploaded CSV file to a row in a MySQL database table
 *
 */
function CSV2DB($filename) {
 if (FALSE == empty($_FILES[$filename]['tmp_name'])) {
 $row = 1;
 $handle = fopen($_FILES[$filename]['tmp_name'], "r");
 while (($data = fgetcsv($handle, 1000, ","))!== FALSE) {
 $num = count($data);
 // echo "<p> $num fields in line $row: <br /></p>\n";
 $row++;
 for ($c = 0; $c < $num; $c = $c + 2) {
 // Remove all dots
 $data[$c] = str_replace('.', '', $data[$c]);
 // Replace spaces with underscores
 $data[$c] = str_replace(' ', '_', $data[$c]);
 $disease_data[$data[$c]] = $data[$c + 1];
 // echo $data[$c] . "<br />\n";
 }
 }
 fclose($handle);
 // id is one of the columns in the table
 $disease_data['id'] = '';
 // $disease_data['page_id'] = $pageId;
 DB_insert('[b]MYSQL_TABLE_NAME[/b]', $disease_data);
 }
}
// DB function used:
 /**
 * Builds an insert query from an array of values and inserts the data.
 *
 */
 function DB_insert($table, $data)
 {
 $query = 'INSERT INTO `'.$table. '` (';
 $first_time = TRUE;
 foreach ($data as $key => $value ) {
 if (FALSE == $first_time) {
 $query .= ', ';
 } else {
 $first_time = FALSE;
 }
 $query .= '`'.$key.'`';
 }
 $query .= ') VALUES (';
 $first_time = TRUE;
 foreach ($data as $key => $value ) {
 if (FALSE == $first_time) {
 $query .= ', ';
 } else {
 $first_time = FALSE;
 }
 $query .= '"'.$value.'"';
 }
 $query .= ');';
 return mysql_query($query);
 }
?>

The above function is part of a larger DB class that I wrote but you only need that function.

The code was being used for a disease statistics analysis program I wrote for my Dad to use at work.

Basically the code takes a CSV file in the following format:

age, 16
country, USA

It then adds a row to a MySQL table with assigning the value givin in the CSV file to each column.

For example:

In the sample CSV data above a column named 'age' would have the value '16' assigned to it. A column named 'country' would have the value 'USA' assigned to it.

From the CSV file:

age, 16
country, USA

You would end up with a MySQL table sort of like this:
id¦age¦country
1¦16¦USA

I hope what I've written is not completely unintelligable. :)

I know the code is rough. I've tried to simplify it a little bit but it's probably still pretty hard to understand. I hope you find it useful though.

I'll be glad to help if you run into any problems. ;)

Elijah

P.S Sadly all the code indentation is lost when posting to this forum. Hopefully your text editor allows for easy auto-indenting like mine does.

[willybfriendly]

Thanks guys. I posted, and then discovered LOAD DATA INFILE [dev.mysql.com] which seems to simplify things substantially.

Funny how asking a question often leads one to discovering their own answer.

WBF