JDT

FREE Downloads
Articles
Tutorials

Converting a Spreadsheet into an SQL Script

It is sometimes necessary to export data from a spreadsheet and import it into a database, for example, a MySQL database. In this tutorial we'll look at how to use PHP to create an SQL script, which contains data from a spreadsheet. The SQL script can then be run to populate a MySQL database table.

Note: It is possible to export data from a spreadsheet and import it directly into a database, without needing to create an SQL script. However, I prefer to create an SQL script, which I can then examine before I run it. This gives me more control over the export/import process.

Spreadsheet applications such as Microsoft Excel enable you to save files in comma separated value (csv) format, which is a text-based format in which fields are delimited (separated) by commas. You can then process such a file without needing to worry about how to handle all the code that would have been present in the original spreadsheet file.

The PHP script given below processes a file called filename.csv, and creates an SQL script called update_database_table.sql, which in turn updates (when it is run) a MySQL database table called database_table. As these filenames are hardcoded into the PHP script itself, you would naturally need to change them for your particular needs.

<?php

$filename="filename.csv";

$tablename="database_table";

if (!file_exists($filename))
    echo "Cannot find import file " . $filename . ", please check that the path is correct.";

    $row = 1;
    $handle = fopen($filename, "r");
    while (($data = fgetcsv($handle, 1000, ",")) !== FALSE)
    {
       $num = count($data);

       # Generate the query
       $query="INSERT INTO " . $tablename . " VALUES (" . $data[0];
       for ($c=1; $c < $num; $c++)
           $query .= ", '" . $data[$c] . "'";
       $query .= ");";

       print $query;

       # Open the file and append a new insert line
       $fp = fopen("update_database_table.sql", "a");

       # Write the data to the file
       fwrite($fp, $query);

       # Close the file
       fclose($fp);

       $row++;
    }
    fclose($handle);

?>

The script initially checks that the comma separated value file exists; if it doesn't, an error message is displayed. If it does exist, it is opened for reading.

All the fields for the first row are put into an array ($data[0], $data[1], etc), and a database query ($query) is built. It is then printed on the screen (this is just so that you can see what queries are being built).

An SQL file is created (if it doesn't already exist) and opened (using fopen). Each row is then appended (using fwrite) to the SQL file.

Author: Backrubber
John Dixon Technology Ltd

Go back to MySQL Tutorials home page

Go back to Tutorials home page

Earnings Tracker is John Dixon Technology's FREE open source accounting and bookkeeping software tool. The software is written in PHP and MySQL and is available to use for FREE online, or as a FREE download. Earnings Tracker is aimed at contractors and freelancers, and lets you record invoice amounts, salaries, income tax, pension contributions, employers and employees national insurance contributions, and calculates the amount of VAT and corporation /business tax due, and the size of dividends that can be taken by shareholders. Earnings Tracker can also be used simply as a dividend, corporation tax, or VAT calculator.

Copyright Notice for John Dixon Technology Ltd

Privacy Statement

Terms & Conditions