Importing with PhpMyAdmin error.

[NerdConcepts]

I've been testing import csv files with PHP. Well it's going ok, I guess, when you save with Excel everything works fine. The output .csv file is from another piece of software, and it appears to be completely fine. Look at this:

 

Line [1] from Excel saved CSV under "date" is this.

59:04.0

 

Line [1] from Software saved CSV under "date" is this.

2007-06-14 11:59:04.0

 

Excel takes out most of the date/time. When it is almost taken out it imports with PhpMyAdmin just fine. Well I need that date. For some reason when that date/time is complete MySQL kicks it back. Know the weird thing, I can go into PhpMyAdmin and insert just "2007-06-14 11:59:04.0" into the "date" column. So, this completely makes no sence why MySql is kicking it back. When I compare both csv files they are exactly alike, except the date; yet, there is no problem...anyone have any idea how to fix this? Or maybe a loop around it using PHP. I've got a class file that imports the Excel CSV just fine.

[NerdConcepts]

I take it, since no one has responded to this, that no one has any idea what is going on. If so can someone please let me know that it really does make no sense. Thank you very much.

[AndyB]

Shouldn't the field for date and time be defined as a datetime field in MySQL?

[NerdConcepts]

I've tried that, still wont import the data. I have them all set to "VARCHAR[255]" and I can manually insert just that value into MySQL, but PHPMyAdmin won't import it in. Everything is separated like it should be. With a comma at the beginning and end of that date/time.

 

Here is the data that won't work:

TEST DESCRIPTION,0000000000,000000,R0000000000,S000000000000,AAAAAAAAAAAA,2007-06-14 11:59:04.0,USPS PRIORITY,000000000,STORAGE

 

Here is the data that does work.

TEST DESCRIPTION,0000000000,000000,R0000000000,S000000000000,AAAAAAAAAAAA,59:04.0,USPS PRIORITY,000000000,STORAGE

 

As you notice, that only thing that is different is the date/time. Excel says it's not correct data and takes out most of the date/time. Not sure why it does it, but it does. Since the date/time is completely correct, nothing should be tripping an error.

[AndyB]

I'm confused about what you are really trying to do.  Are you trying to import a CSV file generated from 'Software'; are you trying to import a CSV generated by Excel.

 

when you save with Excel everything works fine

 

Line [1] from Excel saved CSV under "date" is this (which apparently isn't what you want)

 

I don't understand what "MySQL kicks it back" means.

 

Inserting a date/time as varchar makes no sense.

[NerdConcepts]

Im using a CSV file that is exported though a piece of software, not Excel, different. I referenced to excel because I went ahead and opened it in excel, then saved it through excel as a CSV to see if there was a difference, and there was, it took out most of the date/time.

 

The reason I'm using a VARCHAR to store the time/date, well it will never be changed; it's just there as a record. Basically using it for test reasons.

 

The 2 lines of CSV data I showed are that other piece of software (which doesn't work) and excel's version of the data which does work.

 

PhpMyAdmin kicks it back, meaning that it says there is an error in the csv file. Although as you see the file is technically fine.

 

Also, you are right, I don't want employees to have to export their inventory data and then open/save/close it through Excel. Those steps just seem to be a big giant pain in the butt when having to do it 3/4 times a day, 5 or 6 days a week.

[AndyB]

Here's what I did, and it all worked with no errors.

 

First I created a dummy database with 10 varchar fields

 

Second I created a dummy .csv file with three lines in it, alll minor vrints of

TEST DESCRIPTION1,0000000000,000000,R0000000000,S000000000000,AAAAAAAAAAAA,2007-04-14 11:59:04.0,USPS PRIORITY,000000000,STORAGE

 

From phpMyAdmin, I selected SQL which shows an option "Insert data from a textfile into table" - mI chose it.  The next screen asks me to browse for the text/csv file and set various parameters for the upload/insert.  I chose fields separated by comma, no escape character, \r\n as delimiter and hit upload .... worked perfectly.

[NerdConcepts]

This is interesting. When I grab all the data in the file that was exported out of the "software" (not sure if it really even has a name, not excel). And copy it all to notepad and save it as a csv it works just perfectly. But when the software exports it, it doesn't work. Let me show you the code I use to break the file apart and what not. I'm wondering if there is a way to make PHP take the file and re-write a new file, something like temp.csv every time the data is uploaded. Since eventually I will be using my software to do this, which with my copy/paste into a new csv file my software and PhpMyAdmin both import it fine. I only was using PhpMyAdmin to make sure it wasn't my coding.

 

$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(".0", "", $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_inventory_temp FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"' LINES TERMINATED BY '\r\n' 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());

 

as you see the file is uploaded, read, first line modified (removing spaces and replace with underscores, does that since I do have a class I wrote that takes the first line and read it to see where each column is, since they may be rearranged some day). The it is put back together. I am wondering if I can some how create a whole new file. Maybe that will work. If I take each line (with data) and write a complete new file with the data. Not sure if this is possible since I really don't know how to do it and can't exactly seem to find anything that points to how to do it.

 

Thanks for the help, starting to kind of make since now. Seems as if the exported CSV file has something wrong with it, even though the file (when opened in notepad) looks completely fine. Not sure.

[NerdConcepts]

I dumb I know, lol. I used the fopen to do the write, correct. Well this time I just am having it write the $filecontents to a temp. .csv file. Still the same problems. Is it possible that there is something funning going on with the .csv file that is exported by the software? Since I can copy and paste the contents into a new file with notepad and it works. Even when I do a line by line comparison in notepad, the files look completely the same. Is there maybe some kind of underline protection that is being hidden in the file some how, so that the software only imports it. Since the software will import it back into itself just fine, but nothing else likes it, not my script, PhpMyAdmin, or Excel even likes it. Excel also likes the file if I cut and paste the data into a new .csv or .txt file. This is just not making any sense to me.

[AndyB]

Can you post a small example file generated by the "software". You should be able to post it as an attachment here.

[NerdConcepts]

Here it is. This is a small list of stuff that I chose to export. Remember that if you copy and paste into a new .txt or .csv file it works fine. But this file doesn't. Also another thing I've noticed, if you actually import the first line of the file, it puts null "\0" characters between each character. I think this has something to do with "hidden characters" within the file to prevent importing. I kind of ran into this problem before, but not with PHP or any other web based stuff.

 

do note the file extension had to be changed so that it would upload. Native is .csv

 

[attachment deleted by admin]

[NerdConcepts]

AndyB: I take it that you have no idea whats going on? Also, I've tried a few things to just create a new file but it seems that PHP is copying all the data in the file include the "hidden" stuff that shouldn't be copied. I have also yet to find something that does a copy/paste as if I did it in notepad.

[AndyB]

Actually I was out all day losing golf balls and drinking beer, courtesy of my eldest son.

 

Here's exactly what happens when I attempt to use your 25kb textfile without any pre-processing.

 

I get no errors, and only one row inserted. That row shows data only in the very first field and the data is a three-character string of weird stuff, i.e. some sort of control character mess.

 

If I open it with my text file editor (EditPad Lite) and save it without any changes whatsoever then it craps out the same way.  If I move the column headings from the top of the text file to the bottom - and all I can see is plain text - then I get 80 rows of data inserted nicely, except that the first row has three weird characters in the first column.

 

The problem is 'something' in the file ahead of the column names.  Looks like copy/paste is the only solution.

[Corona4456]

Looking at the hex dump it you are correct NerdConcepts.  It does put a \0 character after every single character, so if you have an editor such as TextPad, you can save the file as UTF-8, ANSI or ASCII then you will be able to import the file just fine.

[NerdConcepts]

Later this week I should get to take a good gander at the software that exports that file and see if there is some way I can get it export it w/o those characters. It'll do delimidation by tabs, commas, etc. So maybe there is an option to change the format. You should see the weird code that has to be ran to get the work order information to actually import correctly. But it doesn't include null characters.

 

I do have to express that I am very thankful for the help. Atleast I got more then myself to back up my claim that it's impossible (as of what I know) to directly import the file.

[Corona4456]

Well... glad to confirm that you aren't insane