Jump to content

[SOLVED] MSSQL to MySQL


The14thGOD

Recommended Posts

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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)

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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;
?>

Link to comment
Share on other sites

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);

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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);
}

Link to comment
Share on other sites

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)?

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.