Login   Register  
PHP Classes
elePHPant
Icontem

Quickly importing data from CSV file into PHP applications - PHP Classes blog

Recommend this page to a friend!
Stumble It! Stumble It! Bookmark in del.icio.us Bookmark in del.icio.us
  Blog PHP Classes blog   RSS 1.0 feed RSS 2.0 feed   Blog Quickly importing dat...   Post a comment Post a comment   See comments See comments (17)   Trackbacks (0)  
<< Previous: Automating Backup Tas...>> Next: Blogs for class packages

Author: Alexander Skakunov

Posted on:

Categories: PHP Tutorials

In this article you may learn how to quickly import data from CSV files into your application with help of PHP.

It also covers a tool that provides a Web based user interface for importing CSV data into a MySQL database table with support for mapping CSV columns into arbitrary MySQL database table columns.




Contents

* Introduction

* Importing data from CSV files

* Dealing with special characters

* Importing CSV data directly into a database

* Friendly user interface to import CSV data into a database


* Introduction

Did you know there are data exchange formats besides XML? If the data to exchange is flat, i.e. is not in an hierarchical structure, CSV (comma separated values) format is a good candidate format to import or your export your application data.

You can find out more about CSV format in Wikipedia:

http://en.wikipedia.org/wiki/CSV


* Importing data from CSV files

Since we have lines of values separated by commas, the easiest way to process them is to parse each line using the PHP explode() function:

<?php

$arrResult = array();
$arrLines = file('data.csv');
foreach($arrLines as $line) {
$arrResult[] = explode( ',', $line);
}

?>

* Dealing with special characters

This simple solution will not work if you have a comma in a value, like for instance when the column is an address and it has a value is like "Obama street, 1".

In such cases, the column value in the CSV file is quoted to indicate that the data between quotes should be read as a single column.

To deal with this situation, you can use a specially tailored regular expression or use the PHP fgetcsv() function.

http://www.php.net/fgetcsv

<?php

$arrResult = array();
$handle = fopen("data.csv", "r");
if( $handle ) {
while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
$arrResult[] = $data;
}
fclose($handle);
}
?>

Note that locale settings may affect how fgetcsv() function works. As noted in the PHP manual, if LANG locale setting is for instance "en_US.UTF-8", files in one-byte encoding are read wrong by this function, so be aware.


* Importing CSV data directly into a database

If you are importing data from a CSV file into a MySQL database, you import it directly into a database table. It will be much faster than processing it line by line using PHP.

Fortunately, most relational database have tools for the bulk data import from CSV files. For instance, in MySQL you can use LOAD DATA INFILE query statement.

http://dev.mysql.com/doc/refman/5.0/en/load-data.html


* Friendly user interface to import CSV data into a database

An alternative solution to import CSV data is to use my class "Quick CSV Import" also available in the PHPClasses site:

http://www.phpclasses.org/quick_csv_import

An even more user-friendly alternative to import CSV data into a database consists in using the application "Quick CSV import with visual mapping". It is based in the class above.

This application helps importing CSV files into a database table allowing to define with CSV file columns are mapped to which columns of the database table. This application can even suggest automatically separator character besides the comma.

You may learn more about this application in the following page:

http://i1t2b3.com/2009/01/14/quick-csv-import-with-mapping/

You need to be a registered user or login to post a comment

Login Immediately with your account on:

Facebook ConnectGmail or other Google Account
Hotmail or Microsoft Windows LiveStackOverflow
GitHubYahoo


Comments:

1. Seriously... - Nicholas Calugar (2010-06-02 03:09)
Does this really deserve an article?... - 9 replies
Read the whole comment and replies

4. csv tutorial - Josť Filipe Lopes Santos (2009-06-18 06:22)
csv tutorial... - 1 reply
Read the whole comment and replies

2. Necessary? - JMAN (2009-02-27 21:22)
Necessary?... - 3 replies
Read the whole comment and replies

3. Library for CSV parsing - Thomas Off (2009-02-26 08:38)
Use parsecsv-for-php instead of fgetcsv()... - 0 replies
Read the whole comment and replies


<< Previous: Automating Backup Tas...>> Next: Blogs for class packages

  Blog PHP Classes blog   RSS 1.0 feed RSS 2.0 feed   Blog Quickly importing dat...   Post a comment Post a comment   See comments See comments (17)   Trackbacks (0)