import a csv file

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
peterhall
Forum Newbie
Posts: 24
Joined: Sat Aug 21, 2010 5:47 pm

import a csv file

Post 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 !!
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: import a csv file

Post by Christopher »

Use glob() to get the filename. Use fgetcsv() to read the file. See the manual for details and examples.
(#10850)
peterhall
Forum Newbie
Posts: 24
Joined: Sat Aug 21, 2010 5:47 pm

Re: import a csv file

Post 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";
User avatar
McInfo
DevNet Resident
Posts: 1532
Joined: Wed Apr 01, 2009 1:31 pm

Re: import a csv file

Post 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);
    }
}
peterhall
Forum Newbie
Posts: 24
Joined: Sat Aug 21, 2010 5:47 pm

Re: import a csv file

Post 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...
mikosiko
Forum Regular
Posts: 757
Joined: Wed Jan 13, 2010 7:22 pm

Re: import a csv file

Post 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
peterhall
Forum Newbie
Posts: 24
Joined: Sat Aug 21, 2010 5:47 pm

Re: import a csv file

Post by peterhall »

tks alot mikosiko ... I will take a look and make some tests, than I will post in here the results!!!
User avatar
McInfo
DevNet Resident
Posts: 1532
Joined: Wed Apr 01, 2009 1:31 pm

Re: import a csv file

Post 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.
Post Reply