[SOLVED] MSSQL to MySQL

[The14thGOD]

So for the last couple of days I have been trying to import this CSV outputted by a MSSQL into a MySQL database. This is being done by a PHP script that we want to run daily (cron job). The client has their own db that they get from somewhere else and they will upload it to the server where this script is. However I've been plagued with errors, is there something I need to do from a MSSQL server to MySQL server? Here's the PHP code:

<?php
ini_set ("display_errors", "1");
error_reporting(E_ALL);
//CONNECTION INFO HIDDEN
$fcontents = file ('./MY.csv');
mysql_query("TRUNCATE table_name");
$date = date("Y-m-d H:i:s");
for($i=0; $i<sizeof($fcontents); $i++) { 
	$line = trim($fcontents[$i]); 
	$arr = explode("|", $line); //, is the delimter 
	if($arr[21] == ''){
		$arr[21] = '0000-00-00 00:00:00';
	}
	if($arr[22] == ''){
		$arr[22] = '0000-00-00 00:00:00';
	}
	foreach($arr as $value){
		if($value == ''){
			$value = "'1'";
		}
		$value = mysql_real_escape_string($value);
	}
	$arr[21] = '\''.$arr[21].'\'';
	$arr[22] = '\''.$arr[22].'\'';
	$data = implode(',',$arr);
	$data = str_replace('"',"'",$data);
	//$data = preg_replace('^([0-9]{4}-[0-9]{2}-[0-9]{2} [0-9]{2}:[0-9]{2}:[0-9]{2})$','\'\\1\'',$data);
	$sql = "INSERT INTO table_name VALUES ($data".','."'$date') "; 
	mysql_query($sql);
	if(mysql_error()) {
		echo mysql_error() ."<br>\n";
		echo $sql;
		echo '<br /><br />';
		echo $data;
		break;
	} 
}
echo $i;
?>

 

Error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ','0000-00-00 00:00:00','2009-10-29 00:00:00','M','2009-10-29 13:27:02')' at line 1

INSERT INTO table VALUES (ID,1,'A','','STATUS','NAME','','','PHONE','EXT ','ADDRESS','','COUNTRY','CITY','COUNTY','STATE','ZIP','AI','',,,'0000-00-00 00:00:00','2009-10-29 00:00:00','M','2009-10-29 13:27:02')

 

Sensitive data removed.

 

I thought it was due to the date being blank so thats why i have code that sets it to 0000-00-00 00:00:00 however that didn't fix it, i then added the single quotes (') around the field because it was breaking worse then I think. (I've tried a lot of things so I'm sure I missed something).

 

Any ideas/help is much appreciated.

Thanks,

Justin

[cags]

Your problem is around fields 19 and 20 (I think, only a quick count through). You have...

 

...'AI','',,,'0000-00-00 00:00:00'...

 

... at the very least you would need to be passing blank strings through...

 

...'AI','','','','0000-00-00 00:00:00'...

 

Can we a sample line that causes the problem? With blanked out/made up data if privacy is an issue.

[The14thGOD]

That's what I was thinking too, but those are all over in the doc in the same spots but (now here's a confusing part) it only stops on the line below which is almost the end of the document. (there's over 2000 lines or something like that) However, when I check the database, there are only 9 rows.

 

From the CSV:

ID|1|"A"|""|"STATUS"|"NAME"|""|""|"PHONE"|"EXT  "|"ADDRESS"|""|"COUNTRY"|"COUNTY"|"CITY"|"STATE"|"ZIP"|"AI"|""||||2009-10-26 00:00:00|"M"

 

Sorry for the long delay in response, was in a meeting.

 

Thanks again for any and all help,

Justin

[cags]

Looks like the problem is actually with your foreach loop.

 

      foreach($arr as $value){
         if($value == ''){
            $value = "'1'";
         }
         $value = mysql_real_escape_string($value);
      }

I don't believe this will actually swap the value in the array for 1, not will it actually escape the value in the array. Try this instead...

 

 foreach($arr as $k=>$value){
         if(empty($value)){
            $arr[$k] = "'1'";
         }
         $arr[$k] = mysql_real_escape_string($value);
      }

This of course assume you want 1 if the field is empty (a blank string) you may actually want to use the NULL datatype or a blank string, thats up to you.

[The14thGOD]

how do you do the NULL thing? I've never understood null and reading about it makes my head spin in circles. Most of the data in the db has null set to 'yes'.

 

with a combination of that for loop and this line:

 

$sql = "INSERT INTO tablename VALUES ("mysql_real_escape_string($data).','."'$date') ";

 

I managed to get 306 rows in and no error : / (theres a total of 2883 rows). There's no MySQL error or php error so I'm not sure what happens that it just stops. I just get a white page (which I would expect from a completion)

[cags]

I'm not sure how a true NULL value would apply with an implode. Try this...

 

foreach($out as $k=>$a) {
   if(empty($a))
      $out[$k] = 'NULL';
}

 

[The14thGOD]

ok, after some troubleshooting (missed a '.' somewhere) it now runs through the script.

 

There's 2823 rows of content however, only 2816 make it through. No errors. Any ideas?

 

Thanks again for all of the help!

[cags]

Nothing springs to mind, it's probably a particular type of character in one of the fields or something, but the question is what?! I think you may have to attempt to find a line that isn't inserting, based on that it should be alot easier to figure out. Looking at your code it output's the $sql line when it fails, you will need to try and spot a diffrence between those and the other values.

[The14thGOD]

Ya, I just dont know how to do it without going through each line lol. The only thing that echo's is $i

and thats all the way at the end of the script after all the inserting takes place. And if it does error it

should break out of the loop. Hmmm...puzzling indeed.

[The14thGOD]

oh boy..just noticed, in the database, there are now double quotes (") all over the place

here's the code now:

<?php
ini_set("display_errors", "1");
error_reporting(E_ALL);
$fcontents = file ('./My.csv');
mysql_query("TRUNCATE tablename");
$date = date("Y-m-d H:i:s");
for($i=0; $i<sizeof($fcontents); $i++) { 
	$line = trim($fcontents[$i]); 
	$arr = explode("|", $line); //, is the delimter 
	if($arr[21] == ''){
		$arr[21] = '0000-00-00 00:00:00';
	}
	if($arr[22] == ''){
		$arr[22] = '0000-00-00 00:00:00';
	}
	foreach($arr as $k=>$value){
		if(empty($value)){
			$arr[$k] = 'NULL';
		}
		$arr[$k] = str_replace('"','',$value);
		$arr[$k] = mysql_real_escape_string($value);
		$arr[$k] = "'".$arr[$k]."'";
	}
	$data = implode(',',$arr);
	//$data = preg_replace('^([0-9]{4}-[0-9]{2}-[0-9]{2} [0-9]{2}:[0-9]{2}:[0-9]{2})$','\'\\1\'',$data);
	$sql = "INSERT INTO tablename VALUES (".$data.','."'$date') "; 
	mysql_query($sql);
	if(mysql_error()) {
		echo mysql_error() ."<br>\n";
		echo $sql;
		echo '<br /><br />';
		echo $data;
		break;
	} 
}
echo $i;
?>

[cags]

Try changing this...

 

mysql_query($sql);
if(mysql_error()) {
   echo mysql_error() ."<br>\n";
   echo $sql;
   echo '<br /><br />';
   echo $data;
   break;
} 

... for this...

 

mysql_query($sql) or trigger_error("SQL: $sql, ERROR: " . mysql_error(), E_USER_ERROR);

[The14thGOD]

I'm wondering if this line is breaking it:

$arr[$k] = "'".$arr[$k]."'";

 

I know theres usually problems with single quotes inside of double quotes, so like i can never do echo "$row['image_path']" but can do echo "$row[image_path]"

 

I also swapped the code, however it still went through w/o triggering an error.

[cags]

Ok, I sat down and looked at your code properly, I think this is basically what your after...

 

   $arr = explode("|", $line); //, is the delimter 

   foreach($arr as $k=>$v){
      $v = trim($v, '"');

      if(empty($v)){
         $arr[$k] = 'NULL';
      } elseif(is_numeric($v)) {
         $arr[$k] = $v;
      } else {
         $arr[$k] = "'" . mysql_real_escape_string($v) . "'";
      }
   }

   $data = implode(",", $arr);
   $sql = "INSERT INTO table_name VALUES ($data) "; 
   mysql_query($sql) or trigger_error("SQL: $sql, ERROR: " . mysql_error(), E_USER_ERROR);
}

[The14thGOD]

I cleaned that up a bit too, slightly different, however, it gets the same 2816 rows and now theres single quotes all over in my table.

magic_quotes_gpc is off

 

I noticed this though, with the changes i made:

<?php
$line = trim($fcontents[$i]);
	$line = str_replace("'","\'",$line);
	$line = str_replace('"',"'",$line);
	$arr = explode("|", $line); //, is the delimter 
	if($arr[21] == ''){
		$arr[21] = '0000-00-00 00:00:00';
	}
	if($arr[22] == ''){
		$arr[22] = '0000-00-00 00:00:00';
	}
	foreach($arr as $k=>$value){
		if(empty($value)){
			$arr[$k] = 'NULL';
		}
	}
?>

 

The error: (look at the dates, they are formatted right)

INSERT INTO mytable VALUES (id,2,'A','timezone','status','name','zip','email','phone','ext ','address','','state','county','city','state','zip','CH','AD7587',NULL,500,2006-12-14 00:00:00,2009-10-29 00:00:00,'M','2009-10-29 17:10:17')

 

When I copied it into sql in phpmyadmin and posted it i go this (look at the dates, not formatted):

INSERT INTO mytable

VALUES ( id, 2, 'A', 'timezone', 'status', 'name', 'zip', 'email', 'phone', 'ext ', 'address', '', 'country', 'county', 'city', 'state', 'zip', 'CH', 'AD7587', NULL , 500, 2006 -12 -1400 :00 :00, 2009 -10 -2900 :00 :00, 'M', '2009-10-29 17:03:31' )

 

or look here:

2006 -12 -1400 :00 :00, 2009 -10 -2900 :00 :00

Is MSSQL like inserting invisible type characters (sort of like how word does when you paste into a text area for a CMS)?

[The14thGOD]

unfortunantly i have to leave for the day, but Ill be back tomorrow. If you can help thats great, if not, thanks for all your help today, really appreciate it!

 

Justin

[cags]

Very difficult for me to say/see without having the database and the csv at hand. All I can tell you is that the code I provided, formats the demonstration string into exactly the format I would have typed if I was manually writting an SQL string in PHP. It escapes the data successfully, it handles numbers and NULL values (which vary much depends on your database accepting null values.

 

INSERT INTO table_name VALUES ('ID',1,'A',NULL,'STATUS','NAME',NULL,NULL,'PHONE','EXT ','ADDRESS',NULL,'COUNTRY','COUNTY','CITY','STATE','ZIP','AI',NULL,NULL,NULL,NULL,'2009-10-26 00:00:00','M')

 

The adjustment you are doing is basically swapping the two known date columns to the default 'empty' date, which is only required if the date is not allowed to be null. But since the empty date has no more meaning that null I really don't see the point of having the column not null. I purposefully avoided using string replace since it will replace all instances, which dependent on the columns definately wouldn't be favourable.

 

Given 1 or two lines of example data and the table structure (DESCRIBE table_name) I'm sure could come up with something better, but given the example line and no idea of the table structure I can't do any better than I have, as the previous code I posted I believe should work.

[The14thGOD]

with some further tweaking I managed to get it to work. (I forgot to do something on my end, your code was fine =). Thanks again for all of your help. It's much appreciated.

 

Justin

[cags]

Your welcome, glad it's sorted.