Page 1 of 1

import a csv file

Posted: Tue Aug 24, 2010 1:05 pm
by peterhall
hi there.

I want to import a csv file into my dbase in mysql, so far i did it. now, what I want is, and because everyday I have to import the same file, but with a diferent name (the date), eg:


20100820_file.csv
20100821_file.csv
20100822_file.csv

it will be something like this:

*.*_file.csv

and, in case of the server goes down for a few days, i want that the code import all the csv files, when the server comes up, in the folder, maybe a loop in the code until he understand that there 's nothing more to import... if so, how can I do that?

thanks !!

Re: import a csv file

Posted: Tue Aug 24, 2010 6:17 pm
by Christopher
Use glob() to get the filename. Use fgetcsv() to read the file. See the manual for details and examples.

Re: import a csv file

Posted: Thu Aug 26, 2010 5:05 pm
by peterhall
hi again!!

I try to build the code in many ways but doesnt work...

this is the code:

Code: Select all

$databasehost = "localhost";
$databasename = "db_name";
$databasetable = "db_table";
$databaseusername ="root";
$databasepassword = "";
$fieldseparator = ",";
$lineseparator = "\n";

$csvfile = "D:/dir/dir/20100518_file.csv"; --------- IN HERE IS WHERE I WANT TO INCLUDE THE glob() TO IMPORT ALL THE FILES WHERE THE DIFFERENCE IS THE DATE, THE REST OF THE FILE IS THE SAME
 
$addauto = 0;

$save = 0;
$outputfile = "output.sql";

if(!file_exists($csvfile)) {
	echo "File not found. Make sure you specified the correct path.\n";
	exit;
}

$file = fopen($csvfile,"r");

if(!$file) {
	echo "Error opening data file.\n";
	exit;
}

$size = filesize($csvfile);

if(!$size) {
	echo "File is empty.\n";
	exit;
}

$csvcontent = fread($file,$size);

fclose($file);

$con = @mysql_connect($databasehost,$databaseusername,$databasepassword) or die(mysql_error());
@mysql_select_db($databasename) or die(mysql_error());

$lines = 0;
$queries = "";
$linearray = array();

foreach(split($lineseparator,$csvcontent) as $line) {

	$lines++;

	$line = trim($line," \t");
	
	$line = str_replace("\r","",$line);
		
	$line = str_replace("'","\'",$line);
		
	$linearray = explode($fieldseparator,$line);
	
	$linemysql = implode("','",$linearray);
	
	if($addauto)
		$query = "insert into $databasetable values('','$linemysql');";
	else
		$query = "insert into $databasetable values('$linemysql');";
	
	$queries .= $query . "\n";

	@mysql_query($query);
}

@mysql_close($con);

if($save) {
	
	if(!is_writable($outputfile)) {
		echo "File is not writable, check permissions.\n";
	}
	
	else {
		$file2 = fopen($outputfile,"w");
		
		if(!$file2) {
			echo "Error writing to the output file.\n";
		}
		else {
			fwrite($file2,$queries);
			fclose($file2);
		}
	}
	
}

echo "Found a total of $lines records in this csv file.\n";

Re: import a csv file

Posted: Thu Aug 26, 2010 7:35 pm
by McInfo
An example of loading and displaying CSV data from multiple files:

Code: Select all

<?php
header('Content-Type: text/plain');

define('CSV_SEARCH_PATTERN', 'D:/dir/dir/*_file.csv');
define('CSV_LINE_LENGTH', 1024);
define('CSV_DELIMITER', ',');
define('CSV_ENCLOSURE', '"');
//define('CSV_ESCAPE', '\\'); // PHP 5.3.0

$files = glob(CSV_SEARCH_PATTERN);
print_r($files);

foreach ($files as $file) {
    if ($fh = fopen($file, 'r')) {
        while ($row = fgetcsv($fh, CSV_LINE_LENGTH, CSV_DELIMITER, CSV_ENCLOSURE/*, CSV_ESCAPE*/)) {
            print_r($row);
        }
        fclose($fh);
    }
}

Re: import a csv file

Posted: Fri Aug 27, 2010 10:38 am
by peterhall
tku for ur reply, but what I want, is insert it in the mysql table... how can I arrange this code to do that???


thanks...

Re: import a csv file

Posted: Fri Aug 27, 2010 11:24 am
by mikosiko
you can use the code provided for McInfo and include/adjust in the foreach loop the MYSQL clause LOAD INFILE (obviously you must connect to the database and run that sentence with mysql_query)

here is an example of that clause (you must adjust it to your case)

Code: Select all

LOAD DATA INFILE $file
INTO TABLE mytable FIELDS TERMINATED BY ','
 (mytable_field1,mytable_field2,.... mytable_fieldn,@mytable_datefield)
SET mytable_datefield = date_format(str_to_date(@mytable_datefield,'%m/%d/%Y'),'%Y/%m/%d');

this sentence can be modified in many ways to match your text file structure.

Here is the link to the LOAD DATA instruction if you want to take a look a it:
http://dev.mysql.com/doc/refman/5.1/en/load-data.html

Re: import a csv file

Posted: Fri Aug 27, 2010 11:41 am
by peterhall
tks alot mikosiko ... I will take a look and make some tests, than I will post in here the results!!!

Re: import a csv file

Posted: Fri Aug 27, 2010 11:53 am
by McInfo
peterhall wrote:what I want, is insert it in the mysql table... how can I arrange this code to do that???
Working with the example I provided, connect to the database before the foreach loop and close the database connection after the foreach loop. Within the nested while loop, where print_r($row) is, build and execute the queries. $row is an array. Access individual elements with $row[0], $row[1], etc.

mikosiko's suggestion of opening the files from MySQL will work as long as the files are accessible to the MySQL server. In that case, to use my example, you must remove the "guts" of the foreach loop (the if and while loop). Also remove all defines except CSV_SEARCH_PATTERN.