Importing data from CSV file into PHP applications

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/

Comments

Post a Comment

Popular posts from this blog

PHP / SQL Security – The Big Picture

Web Security Whether your site is the web presence for a large multinational, a gallery showing your product range and inviting potential customers to come into the shop, or a personal site exhibiting your holiday photos, web security matters. After the hard work put in to make your site look good and respond to your users, the last thing you want is for a malicious hacker to come along, perform a PHP hack and break it somehow. There are a number of problems in web security, and unfortunately not all of them have definite solutions, but here we'll look at some of the problems that should be considered every time you set out to write a PHP script to avoid a PHP hack attack. These are the problems which, with well-designed code, can be eliminated entirely. Before looking in detail at the solutions, though, lets take a moment to define the problems themselves. SQL Injection In this attack, a user is able to execute SQL queries in your website's database. This attack is u...
Read more

PHP / SQL Security – SQL Commands and Non-String Variables

SQL Injection SQL (Structured Query Language) is the language used to interface with many database systems, including MySQL, PostgreSQL and MSSQL. Certain words and characters are interpreted specially by SQL, as commands, separators, or command terminators, for instance. When a user enters data into a form, there is nothing stopping them entering these special commands and characters. Consider the PHP code below: $query = “INSERT INTO orders(address) VALUES('$_GET['address']')”; $result = mysql_query($query); A form with a textbox named address would be used to gather the information for this page. We'll ignore any other form elements for now, but obviously there'd be the order items, a name, possibly a price, a delivery date, and so on, which would also all need storing in a database. Imagine a perfectly legitimate user comes along and enters the following address 14 King's Way Kingston Kingham County The database would spit back an error b...
Read more

Top 50 Web Hacking Techniques

This post will serve to collect new attack techniques as they are published. If you think something should be added, please comment below and I'll add them. "Every year the Web security community produces a stunning amount of new hacking techniques published in various white papers, blog posts, magazine articles, mailing list emails, etc. Within the thousands of pages are the latest ways to attack websites, Web browsers, Web proxies, and so on. Beyond individual vulnerability instances with CVE numbers or system compromises, we're talking about actual new and creative methods of Web-based attack. The Top Ten Web Hacking Techniques list encourages information sharing, provides a centralized knowledge-base, and recognizes researchers who contribute excellent work." Current 2011 List Bypassing Flash’s local-with-filesystem Sandbox Abusing HTTP Status Codes to Expose Private Information SpyTunes: Find out what iTunes music someone else has CSRF: Fla...
Read more