How to import a memo type field to mySQL

[kuau]

I am trying to import into a mySQL table a csv file exported from a MS Access table. It acts as if it is importing (the green bar progresses) but then has zero records when done. There are 35,000 records in the original table.

The table is basically an ID number and a large (WAY over 255 characters) notes field. If I convert the field to text, it truncates at the first hard return. I suspect that the import is choking on the hard returns embedded in the notes field. I am wondering if an ODBC onnection would get around this somehow. Or whether there is a way to do a search & replace on all the hard returns. Not sure it is possible to designate hard return in a search. Any ideas? Thanks, e :)

[djr33]

It should certainly be possible to fix this. I'd just use trial and error to find a method that works.
Some suggestions:
1. MySQL frequently has problems importing a lot of info. Just chop it up (manually, probably) and do multiple queries. It sounds like a lot of data.
2. You can search and replace using many methods. Try notepad, for example-- copy the 'return' character (using shift+arrow keys) and paste it into the find field. Use \n or whatever you'd like to use instead in the replace field. (It's also possible to use the same logic in PHP if for some reason the return causes problems in notepad, etc.-- just use str_replace(), cutting and pasting the relevant characters.)
3. If none of this works, you could write a custom CSV parser in PHP, but that's extra work if you can find a way around it like search/replace in notepad.

Could you post a shortened version of the CSV? (Probably best as an attachment since the formatting of the post will confuse things, but maybe in a [code] block would work.)

[kuau]

Hi Daniel! You always come to my rescue :) Thanks!

Not sure what you mean by "copy the 'return' character (using shift+arrow keys)"

As for 2 & 3, they may work when I get the data on the server, but that is the problem... getting the data from my local Windows 7 computer up to the server where I can run php.

Here is an excerpt from the csv file. It's really hard to find the beginning and end of a record but the end is right after the date. Some of the records are a whole page of text. These are small ones...


173,,"OldCP: XAPL
OldAC: H
OldAA: HS

, L-03-19, F02NC, F05PF F06PH,
, 7-17-81 Trib 19/375
10/85 Artists of Maui-",12/10/2001 12:24:44
176,,"OldCP: XASUL
OldAC: H
OldAA: HS

, L-01-223, P-01u, F02NC, SI-01, F05PDB, F06H, P-08, NC-04
, CFS 1982 Sunlit 223/475, given to LA Society auction.
1-22-86 Hills & Pastures, unsigned
Interested in Taro Ponds, Springtime Grazing and Jacaranda Season
May be interested in Makena Stroll (commission work)
02-18-93 ML, Purchased Colours, NC (DS)-",12/10/2001 12:24:44
180,,"OldCP: XASUL
OldAC: H
OldAA: HS

, L-02-144, P-04u, F02NC, SI-01, PI-02, F05PDB, F06H,
, CFS 9-12-88 HT 144/375, LP u/s. (DEL)
Send info on Jacaranda Season when finished.
11-20-88 Called, info in book.
7/27/92 Called RE: UMC Nothing Ordered (LS)
-",12/10/2001 12:24:44
185,,"OldCP: AU
OldAC: H
OldAA: HS

, P-02s, LI-01, SI-01, F02NC, F05PDB, F06H
, 8-22-88 Purchased HT s, interested in JS and Sunlit, gave her order form. (JC)
9/24/91 Rec'd new adr, 114 Gorsuch Rd, Timonium MD invalid.-",12/10/2001 12:24:44
189,,"OldCP: ULM
OldAC: H
OldAA: HS

, L-04-251, P-08, P-10P,
, 1990 Would like to see Jacaranda Season and Beach Morning Glory in print. ""Reoccuringly, the Jacaranda and coconut palm are the most common symbols that most people appreciate and ask for. W
10/03/89 Purchased Yellow Iris 251-375
06/22/90 Purchased Colours, price 38.50
4/2/91 Purchased 2PS prepub @ 45.00
6/23/91 Purch NC.-",12/10/2001 12:24:44
192,,"OldCP: AU
OldAC: H
OldAA: HS

, P-02s, F02NC, F05PDB, F06H
, 2-15-87 HT s
7/27/92 Called RE: UCM (LS)-",12/10/2001 12:24:44
199,,"OldCP: A
OldAC: H
OldAA: HS

, F05PDB, F06H
, 08-11-87 NC-",12/10/2001 12:24:44
201,,"OldCP: R
OldAC: I
OldAA: HS

, F05PDB, F06W

I'm trying something with the dates to see if that is causing the problem and will let you know. Thanks for your help. It probably will be trial and error until something works. e :)

[djr33]

If you want to avoid problems, encode it somehow, such as:
http://www.php.net/manual/en/function.base64-encode.php
(And decode when you get the data out of the database).
But trial and error should help you figure out what the conflict is. MySQL can be picky about encoding and strange characters (which I guess includes returns), but there's usually some way to get it to work.

[kuau]

I can't run any php commands on the data because it is on my Windows computer.

[djr33]

You could upload it onto the server as needed (in parts, probably, so you don't use too much memory).

[kuau]

I guess I didn't explain this clearly. The whole problem is getting the data onto the server intact. Once it is on the server, there is no more problem.

I wasn't aware that there could be a memory issue. Could this be preventing the import? I'm trying something with the dates and if that doesn't work I'll try importing it in pieces. I still would like to know what you mean by "copy the 'return' character (using shift+arrow keys)" -- how do I represent the return character?

Thanks! e :)

[djr33]

There are different ways to get it on the server. You can upload as a .txt through ftp (or even through a form then save to a file with PHP, if that doesn't change the formatting oddly). Then use PHP to get the files' contents and insert them into the DB.

[kuau]

Dear Daniel: I was just out digging in the garden and it suddenly occurred to me that perhaps I could ftp the file and import it from the server. Great minds think alike haha. Don't know why I didn't think of that before. I've just never done it that way before.

I haven't quite finished getting the data ready (have to make the dates yyyy-mm-dd) but I plan to ftp the file then use php. Do I use something like:


$data = file_get_contents(data.txt);

INSERT INTO notes (`client_id`, `notes`, `adate`)
VALUES ( $data )

OK, I admit, I have no clue how to do it. :)

[djr33]

This is obviously the more complex way to do it. Using a database admin tool will be simpler, but if that doesn't work, using PHP is better.

First, you need to parse the file. Use explode() and such to split it into the sections you want.

Then you need to generate the queries. If they're all the same (luckily it looks like that's the case) you can just loop through the array generated by explode() and go from there.

(You'll probably need to explode the whole file (or the smaller chunks you split it into) into an array THEN loop through that and explode the bits to get the info separated; then of course you can just loop through and have the info in arrays like $data[0][0] will be the first element [name? etc] of the first entry.)

In other words, yes, basically what you have above but inside a loop that goes through each entry.



Alternatively you can search google for "csv parser php" or something like that and see what happens. The problem there is that it might be unpredictable.

Remember: save a copy of the data and work in a temporary database (or at least temporary table) if possible. Then find the right way to make it go together and then apply it to the real data. For the initial tests, use a shortened version of the code-- no use wasting processing time there.

[kuau]

Holy crap. I can't even follow that logically nevermind write code to execute it. I thought this was going to be easier. I'm going to try phpMyAdmin again once I have the data ready. God help me if that doesn't work! :)

[djr33]

Imagine this setup: 1,2,3;4,5,6;3,9,0;#,#,#;....

So you have two levels of repetition: groups of 3 numbers [which is itself a group].


//In order to separate it you use:
$string = '1,2,3;4,5,6;3,9,0;#,#,#;....';
$split = explode(';',$string); //split the entries apart in the data
foreach($split as $key=>$value) { //go through each entry then
$split[$key] = explode(',',$value); //split up the entry into its parts
}
//now $split is a two-level array-- an array of entries, each of which is an array of items

//now, to USE this you do:
foreach($split as $entry) {
echo $entry[0],$entry[1],$entry[2]; //prints out '123' or '456', '390', ###, ....
}

So it's just two layers of arrays: one to hold each entry and one FOR each entry that splits up the data into its parts.


(Note: this is the reason that databases are nice-- without them everything would be like this when organizing data.)



Also, be aware that whenever you are doing this level of string parsing, it is CRUCIAL that the data have no errors or inconsistencies. Batch processing will do very unpredictable things when there's even one error in a particularly wrong place. Error-checking is much harder to program, so it's better to make good input than fool-proof code. (Of course it's the opposite if you have users of your website inputting text-- then you must verify. But here you're the only one using it so it should be ok.)



I hope that clarifies somewhat. If you can get phpmysqladmin to work, great. If not, this is probably your only other option ('this' in the general sense-- using PHP to parse it. You might find another way to split the data).

[kuau]

Hey Daniel... After I manipulated the data a bit, I tried the import again and it worked!! Which is a huge relief because from Access I could find no way to specify a record separator and there didn't seem to be one (maybe a CR or LF).

Anyway, the data is all safe and sound on the server and I can go to bed. Thanks so much for your help. I actually think your for each code may work for doing something I thought was impossible with the data, now that it is accessible. But that is not the priority at the moment. Later... :)

[brudawson]

This script will export all the data in your table and also append the date and time of the export to the filename it is generating.

<?php
$host = 'localhost';
$user = 'mysqlUser';
$pass = 'myUserPass';
$db = 'myDatabase';
$table = 'products_info';
$file = 'export';

$link = mysql_connect($host, $user, $pass) or die("Can not connect." . mysql_error());
mysql_select_db($db) or die("Can not connect.");

$result = mysql_query("SHOW COLUMNS FROM ".$table."");
$i = 0;
if (mysql_num_rows($result) > 0) {
while ($row = mysql_fetch_assoc($result)) {
$csv_output .= $row['Field']."; ";
$i++;
}
}
$csv_output .= "\n";

$values = mysql_query("SELECT * FROM ".$table."");
while ($rowr = mysql_fetch_row($values)) {
for ($j=0;$j<$i;$j++) {
$csv_output .= $rowr[$j]."; ";
}
$csv_output .= "\n";
}

$filename = $file."_".date("Y-m-d_H-i",time());
header("Content-type: application/vnd.ms-excel");
header("Content-disposition: csv" . date("Y-m-d") . ".csv");
header( "Content-disposition: filename=".$filename.".csv");
print $csv_output;
exit;
?>