Solved

Import multiple csv files  in mysql database

Posted on 2012-04-12
4
319 Views
Last Modified: 2012-04-12
Hi,

I have a 3 csv files (persons.csv, time.csv, history.csv). I want to import all these csv files in a single mysql table (alldata).

All these csv files has a variable number of colums.

I need a php script/function that will alow me to import all these files and all the columns in  my table like this:

alldata - table
ID   |  FileName    |    Date     |      ColumnName    |    DataImport    |  


so if  I have in the csv file (persons.csv) data like this
Name     |     Code     |    Actions   |    ActionsNo
Rick                 28              sales                    3
Mike               14              man                     1

The alldata - table after import should look like this
alldata - table
ID   |    Date         | FileName   |  RowNumber   |       ColumnName    |    DataImport    |  
 1      2012-04-12    persons.csv              2                       Name                         Rick          
 2      2012-04-12    persons.csv              2                        Code                          28
 3      2012-04-12    persons.csv              2                        Action                       sales
 4      2012-04-12    persons.csv              2                        ActionNo                     3
 5      2012-04-12    persons.csv              3                      Name                         Mike          
 6      2012-04-12    persons.csv              3                       Code                          14
 7      2012-04-12    persons.csv              3                        Action                       man
 8      2012-04-12    persons.csv              3                       ActionNo                     1


Please help me!
0
Comment
Question by:Qw M
  • 3
4 Comments
 
LVL 35

Accepted Solution

by:
gr8gonzo earned 500 total points
ID: 37836923
<?php

mysql_connect("host","user","pass");
mysql_select_db("your database");

importCSVIntoMainTable("persons.csv");
importCSVIntoMainTable("time.csv");
importCSVIntoMainTable("history.csv");

function importCSVIntoMainTable($csvFile)
{
	$row = 1;
	$importDate = date("Y-m-d");
	$baseFileName = mysql_real_escape_string(basename($csvFile));
	
	if (($handle = fopen($csvFile, "r")) !== FALSE)
	{
		$headerRow = true;
		while (($data = fgetcsv($handle, 1000, ",")) !== FALSE)
		{
			// Get column names from first row
			if($headerRow == true)
			{
				$columnNames = $data;
				$headerRow = false;
				continue; // Skip rest of the processing
			}
			
			
			foreach($data as $idx => $value)
			{
				$columnName = mysql_real_escape_string($columnNames[$idx]);
				
				// Build query
				$query = "INSERT INTO alldata (Date,FileName,RowNumber,ColumnName,DataImport) VALUES ('{$importDate}','{$baseFileName}','{$row}','{$columnName}','".mysql_real_escape_string($value)."');";
				
				// Uncomment below to run query
				// mysql_query($query);
			}
			
			// Increment row #
			$row++;

		}
		fclose($handle);
	}
}
?>

Open in new window

0
 
LVL 35

Expert Comment

by:gr8gonzo
ID: 37836956
On a separate note, storing data like that for any long term purpose is usually a terrible idea. If it's all temporary and you get dynamic files all the time that you're simply transforming, that's one thing, but it's horribly inefficient to try to have this kind of dynamic structure. Even with indexes, the data will be cumbersome to retrieve, and there's no indication of the order of columns (if that's important to you). You also run the risk of running out of IDs if you have a lot of rows unless you use something like a BIGINT for your ID column, and the resulting database size will be much larger than the original files.

If it's truly all temporary, you could also do something where you simply json_encode() the row and store the resulting text in the database, so each row in the file is only taking up 1 ID in the database. At least you can probably stick with an INT for your ID. If you actually want to search on any values in the data, you're probably going to see some slow queries, but if you just want to pull all records from a particular file later, this might be a good approach.

Your table structure would look like:

ID, Date, FileName, RowNumber, JSONData

Then the above function would still work, but you'd change the query-building section a bit:

Old:
			foreach($data as $idx => $value)
			{
				$columnName = mysql_real_escape_string($columnNames[$idx]);
				
				// Build query
				$query = "INSERT INTO alldata (Date,FileName,RowNumber,ColumnName,DataImport) VALUES ('{$importDate}','{$baseFileName}','{$row}','{$columnName}','".mysql_real_escape_string($value)."');";
				
				// Uncomment below to run query
				// mysql_query($query);
			}

Open in new window


New:
			$mappedData = array();
			foreach($data as $idx => $value)
			{
				$columnName = $columnNames[$idx];
				$mappedData[$columnName] = $value;
			}

			// Build query
			$query = "INSERT INTO alldata (Date,FileName,RowNumber,JSONData) VALUES ('{$importDate}','{$baseFileName}','{$row}','{$columnName}','".mysql_real_escape_string(json_encode($mappedData))."');";
				
			// Uncomment below to run query
			// mysql_query($query);

Open in new window

0
 
LVL 35

Expert Comment

by:gr8gonzo
ID: 37836959
One other advantage to the second method (with JSONData) is that you're running only one INSERT query per row in the CSV instead of having one query for every column, which could dramatically slow down the import.
0
 

Author Comment

by:Qw M
ID: 37837024
gr8gonzo - thank you so much for your help!
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Author Note: Since this E-E article was originally written, years ago, formal testing has come into common use in the world of PHP.  PHPUnit (http://en.wikipedia.org/wiki/PHPUnit) and similar technologies have enjoyed wide adoption, making it possib…
This article discusses four methods for overlaying images in a container on a web page
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

685 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question