Page 1 of 1

insert file name into mysql

Posted: Thu Sep 16, 2010 5:56 pm
by peterhall
hi guys. i want to know if ispossible to import a csv file into mysql table and insert also the file name in a column of the same table.

i'm using this code to import:

Code: Select all

<?php

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

header('Content-Type: text/plain');
define('CSV_SEARCH_PATTERN', 'D:/dir/dir/*_file.csv'); // this will be the file name to insert into mysql table
define('CSV_LINE_LENGTH', 10000);
define('CSV_DELIMITER', ';');
define('CSV_ENCLOSURE', '"');
//define('CSV_ESCAPE', '\\'); // PHP 5.3.0

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


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

$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 "Imported a total of $lines records in this csv file.\n"; 
}

?>

Re: insert file name into mysql

Posted: Thu Sep 16, 2010 9:22 pm
by John Cartwright
Much simpler to use mysql's built functions. See http://dev.mysql.com/doc/refman/5.1/en/load-data.html

Re: insert file name into mysql

Posted: Fri Sep 24, 2010 7:58 pm
by peterhall
I agree and I've got it, but I still with the same problem.

Imagine that I'm using the LOAD DATA INFILE to import a txt file into mysql.

what I want, besides the lines of the file, is insert, also, the name of the file, just like an auto increment, but in this case, for each file that I import, i want to add the name in the first column, like a ID, wich means,if I have 100 lines in the file A, the ID will be "file A" for all the lines in that file, 100 lines in file b, the id will be "file b" for all the lines in that file and so on...


or, if I can add an a multiple key to all lines that I import for each file, file a, 100 lines, key 1*100, file b, 150 lines, key 2*150, and so on...

all this, always in the first column, behind the data line that i import from the files!!