Page 1 of 1

Syntax error in output sql file.

Posted: Sat Mar 24, 2012 1:56 am
by krasi_e_d
Hello, guys

Please, guys help me for this code:

Code: Select all


$databasehost = "localhost";
$databasename = "export_ajur";
$databasetable = "invoice";
$databaseusername ="*****";
$databasepassword = "****";
$fieldseparator = "'";
$lineseparator = "\n";
$csvfile = "1.csv";

$addauto = 0;


$save = 1;
$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(explode($lineseparator,$csvcontent) as $line) {

	

$lines++;

	

$line = trim($line," \t");

	


	

$line = str_replace("\r","",$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";




When I start the code the output file is:
[text]
insert into invoice values ('ID_PURCHASE_INVOICE,DOC_TYPE_NAME,DOC_EXT_CODE,DOC_NUM,DOC_DATE,WOVAT_AMOUNT,VAT_AMOUNT,TOTAL_AMOUNT,CONTRAGENT_NAME,CONTRAGENT_BULSTAT,CONTRAGENT_VATNUMBER,CONTRAGENT_POST_ADDRESS,CONTRAGENT_ZIP,CONTRAGENT_CITY,CONTRAGENT_AREA,CONTRAGENT_STREET,CONTRAGENT_MOL,CONTRAGENT_PHONE,CONTRAGENT_BANK,CONTRAGENT_BANK_CODE,CONTRAGENT_BANK_ACCOUNT,CONTRAGENT_EXT_CODE,CONTRAGENT_EMAIL,PAYMENT_NAME,DOC_MARKER1,DOC_MARKER2,DOC_MARKER3,DOC_MARKER4,DOC_MARKER5,DOC_MARKER6,DOC_MARKER7,DOC_MARKER8,DOC_MARKER9,DOC_MARKER10');

insert into invoice values ('108826,ДАНЪЧНА ФАКТУРА,1,111366,2/15/2012,160.08,32.02,192.1,ПРЕЦИЗ ООД,103565237,BG103565237,ВАРНА ГЕН.КОЛЕВ 38,,,,,,,,,,,,БАНКА,,,,,,,,,,');

[/text]
But I need to be like this to insert row. The errors is only where I have ' in values . How to change my code ?
[text]
insert into invoice values (ID_PURCHASE_INVOICE,DOC_TYPE_NAME,DOC_EXT_CODE,DOC_NUM,DOC_DATE,WOVAT_AMOUNT,VAT_AMOUNT,TOTAL_AMOUNT,CONTRAGENT_NAME,CONTRAGENT_BULSTAT,CONTRAGENT_VATNUMBER,CONTRAGENT_POST_ADDRESS,CONTRAGENT_ZIP,CONTRAGENT_CITY,CONTRAGENT_AREA,CONTRAGENT_STREET,CONTRAGENT_MOL,CONTRAGENT_PHONE,CONTRAGENT_BANK,CONTRAGENT_BANK_CODE,CONTRAGENT_BANK_ACCOUNT,CONTRAGENT_EXT_CODE,CONTRAGENT_EMAIL,PAYMENT_NAME,DOC_MARKER1,DOC_MARKER2,DOC_MARKER3,DOC_MARKER4,DOC_MARKER5,DOC_MARKER6,DOC_MARKER7,DOC_MARKER8,DOC_MARKER9,DOC_MARKER10);# 1 row affected.

insert into invoice values ('108826','ДАНЪЧНА ФАКТУРА','1','111366','2/15/2012','160.08','32.02','192.1','ПРЕЦИЗ ООД','103565237','BG103565237','ВАРНА ГЕН.КОЛЕВ 38','','','','','','','','','','','','БАНКА','','','','','','','','','','');
[/text]

This is my cvs file if you want to see it.
[text]
ID_PURCHASE_INVOICE,DOC_TYPE_NAME,DOC_EXT_CODE,DOC_NUM,DOC_DATE,WOVAT_AMOUNT,VAT_AMOUNT,TOTAL_AMOUNT,CONTRAGENT_NAME,CONTRAGENT_BULSTAT,CONTRAGENT_VATNUMBER,CONTRAGENT_POST_ADDRESS,CONTRAGENT_ZIP,CONTRAGENT_CITY,CONTRAGENT_AREA,CONTRAGENT_STREET,CONTRAGENT_MOL,CONTRAGENT_PHONE,CONTRAGENT_BANK,CONTRAGENT_BANK_CODE,CONTRAGENT_BANK_ACCOUNT,CONTRAGENT_EXT_CODE,CONTRAGENT_EMAIL,PAYMENT_NAME,DOC_MARKER1,DOC_MARKER2,DOC_MARKER3,DOC_MARKER4,DOC_MARKER5,DOC_MARKER6,DOC_MARKER7,DOC_MARKER8,DOC_MARKER9,DOC_MARKER10

108826,ДАНЪЧНА ФАКТУРА,1,111366,2/15/2012,160.08,32.02,192.1,ПРЕЦИЗ ООД,103565237,BG103565237,ВАРНА ГЕН.КОЛЕВ 38,,,,,,,,,,,,БАНКА,,,,,,,,,,

108830,ДАНЪЧНА ФАКТУРА,1,1002304974,2/15/2012,872.33,174.46,1046.79,ОМВ БЪЛГАРИЯ ООД,121759222,BG121759222,,,СОФИЯ,,,,,,,,,,БАНКА,,,,,,,,,,
[/text]

Re: Syntax error in output sql file.

Posted: Sat Mar 24, 2012 2:37 am
by krasi_e_d
OK! I think now is better, but I need to remove the last line in the output sql file.
The code:

Code: Select all

<?php
$databasehost = "localhost";
$databasename = "export_ajur";
$databasetable = "invoice";
$databaseusername ="root";
$databasepassword = "password";
$fieldseparator = ",";
$lineseparator = "\n";
$csvfile = "1.csv";

$addauto = 0;


$save = 1;
$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(explode($lineseparator,$csvcontent) as $line) {

	$lines++;

	$line = trim($line," \t");
	
	$line = str_replace("\r","",$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 . "";

	@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";


?>
and the output is:
[text]
insert into invoice values ('ID_PURCHASE_INVOICE','DOC_TYPE_NAME','DOC_EXT_CODE','DOC_NUM','DOC_DATE','WOVAT_AMOUNT','VAT_AMOUNT','TOTAL_AMOUNT','CONTRAGENT_NAME','CONTRAGENT_BULSTAT','CONTRAGENT_VATNUMBER','CONTRAGENT_POST_ADDRESS','CONTRAGENT_ZIP','CONTRAGENT_CITY','CONTRAGENT_AREA','CONTRAGENT_STREET','CONTRAGENT_MOL','CONTRAGENT_PHONE','CONTRAGENT_BANK','CONTRAGENT_BANK_CODE','CONTRAGENT_BANK_ACCOUNT','CONTRAGENT_EXT_CODE','CONTRAGENT_EMAIL','PAYMENT_NAME','DOC_MARKER1','DOC_MARKER2','DOC_MARKER3','DOC_MARKER4','DOC_MARKER5','DOC_MARKER6','DOC_MARKER7','DOC_MARKER8','DOC_MARKER9','DOC_MARKER10');
insert into invoice values ('108826','ДАНЪЧНА ФАКТУРА','1','111366','2/15/2012','160.08','32.02','192.1','ПРЕЦИЗ ООД','103565237','BG103565237','ВАРНА ГЕН.КОЛЕВ 38','','','','','','','','','','','','БАНКА','','','','','','','','','','');
insert into invoice values ('108830','ДАНЪЧНА ФАКТУРА','1','1002304974','2/15/2012','872.33','174.46','1046.79','ОМВ БЪЛГАРИЯ ООД','121759222','BG121759222','','','СОФИЯ','','','','','','','','','','БАНКА','','','','','','','','','','');
insert into invoice values ('');
[/text]

I need to remove the last line ' insert into invoice values (''); '