Weird results in MySQL.

[NerdConcepts]

After this script imports a .txt file (similar to a .csv file) it inserts the data just fine, except it modifies it and puts a "?" after every character. Here is the Table info

 

CREATE TABLE `dn_inven_temp` (
  `Part_Description` varchar(255) NOT NULL,
  `Waybill` varchar(255) NOT NULL,
  `Part_Number` varchar(255) NOT NULL,
  `R00` varchar(255) NOT NULL,
  `S00` varchar(255) NOT NULL,
  `Serial_Number` varchar(255) NOT NULL,
  `Ship_Date` varchar(255) NOT NULL,
  `Ship_Method` varchar(255) NOT NULL,
  `Tracking_Number` varchar(255) NOT NULL,
  `Warehouse` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

 

Here is the page coding.

<?PHP
require('includes/init.php');
include($theme . 'header.php');

if (isset($_POST['submitted'])) {
$target = "csv_storage/";
$target = $target . basename($_FILES['file_source']['name']) ;
$ok=1;
if(move_uploaded_file($_FILES['file_source']['tmp_name'], $target)) {
	echo '<span class="title">Upload Successful.<br /><br /></span>';
} else {
	echo "Sorry, there was a problem uploading your file.<br /><br />";
}

// Read Uploaded File
$fcontents = "csv_storage/" . $_FILES['file_source']['name'];
$fh = fopen($fcontents, 'r');
$filecontents = fread($fh, filesize($fcontents));

// Modify Data
$lines = explode("\n", $filecontents);
$lines[0] = str_replace(" ", "_", $lines[0]);
$lines = str_replace("\t",",", $lines);
$filecontents = join("\n", $lines);
fclose($fh);

// Write New Data
$fw = fopen($fcontents, 'w');
$stringData = $filecontents;
fwrite($fw, $stringData);
fclose($fw);

$file = "c:/wamp/www/csvtophp/csv_storage/" . $_FILES['file_source']['name'];


$query = "LOAD DATA INFILE '$file' INTO TABLE dn_inven_temp FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"' IGNORE 1 LINES (Part_Description,Waybill,Part_Number,R00,S00,Serial_Number,Ship_Date,Ship_Method,Tracking_Number,Warehouse)";
$result = mysql_query($query) or trigger_error("Query: $query\n<br />MySQL Error: " . mysql_error());
}

?>
<form enctype="multipart/form-data" action="dn_inventory_import.php" method="post">
<fieldset>
	<legend>Inventory Import</legend>
	Select Inventory Output File
	<input type="file" name="file_source" />
	<br />
	<input type="hidden" name="submitted" value="TRUE" />
	<input type="submit" name="submit" value="Import" />
</fieldset>
</form>

<?PHP
include($theme . 'footer.php');
?>

 

Here is the original txt stuff

Part Description	Waybill	Part Number	R00	S00	Serial Number	Ship Date	Ship Method	Tracking Number	Warehouse
Description Test	0000000000	0000000000-00	No Value	No Value	No Value	35:48.0	FEDEX FREIGHT	0000000000	Warehouse

 

Here is the SQL dump after it imports, here is what that line above looks like

INSERT INTO `dn_inven_temp` (`Part_Description`, `Waybill`, `Part_Number`, `R00`, `S00`, `Serial_Number`, `Ship_Date`, `Ship_Method`, `Tracking_Number`, `Warehouse`) VALUES 
('\0D\0e\0s\0c\0r\0i\0p\0t\0i\0o\0n\0 \0T\0e\0s\0t\0', '\00\00\00\00\00\00\00\00\00\00\0', '\00\00\00\00\00\00\00\00\00\00\0-\00\00\0', '\0N\0o\0 \0V\0a\0l\0u\0e\0', '\0N\0o\0 \0V\0a\0l\0u\0e\0', '\0N\0o\0 \0V\0a\0l\0u\0e\0', '\03\05\0:\04\08\0.\00\0', '\0F\0E\0D\0E\0X\0 \0F\0R\0E\0I\0G\0H\0T\0', '\00\00\00\00\00\00\00\00\00\00\0', '\0W\0a\0r\0e\0h\0o\0u\0s\0e\0');

 

Really weird how this is coming out. Anyone have a clue?

[NerdConcepts]

I guess it wouldn't be to big of a deal if I could figure out how to use removed the unwanted characters as they are moved from the temp database to a permanent one. Tried a few things but can't seem to get that to do anything either.

[NerdConcepts]

I fingered out a little of the problem. When using \t to separate fields, that is when it adds the null characters to the database. If I open the .csv into excel and re-export it with commas separating the fields it works fine. So I tried replacing all tables with commas as the file is modified. It goes ahead and still adds the null characters. Not sure why it is doing this though.