<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
Does anyone have much experience of importing data into MySQL? I've got
some ancient experience using SQL (Oracle) and it's beginning to come
back to me as I potter away (using the documentation at
<a class="moz-txt-link-freetext" href="http://dev.mysql.com/doc">http://dev.mysql.com/doc</a> - much better than my ancient O'Reilly book).<br>
<br>
What I'm trying to do is create a recipe database. And ideally, export
the data into my Palm using the HannDBase application. But for the
moment my short term aim is to get my existing information from the
Palm into my MySQL table. <br>
<br>
The problem is that my exported data is not neat. The fields are
multi-lined, and I can't figure out how to get MySQL to identify
records and fields. <br>
<br>
For instance, here's the first record as exported from HandBase.
Exported as CSV, with fields enclosed in quotation marks. As you can
see, the fields are multilined.<br>
<br>
<blockquote><tt>recipe,srvs,source,sourcenotes,ingredients,method,time,cooktime,Notes,SC<br>
"Quick chicken korma<br>
prep=15m<br>
cook=20m (plus rice)<br>
SC=?<br>
serves=4","4","Rosemary Conley Low Fat Cookbook Two<br>
","OR uses Normandy fromage frais, skimmed milk.","4 skinless chicken
breasts cut into chunks<br>
2 medium onions finely chopped<br>
2 crushed garlic cloves<br>
1 veg stock cube dissolved in 150ml boiling water<br>
1 tbsp plain flour<br>
300ml milk<br>
1 tbsp chopped fresh flat leaf pasley<br>
2 tbsp fromage frais<br>
","1. Season the chicken pieces with salt and pepper and fry for 7
minutes. Remove from pan and set aside.<br>
2. Add the onions and garlic to the pan and cook gently until soft.
Sprinkle the curry powder over, andd 2tbsp stock and mix well. Add the
flour and cook for 1 minute stirring continuously.<br>
3. Gradually add the remaining stock and the milk, stirring
continuously to prevent any lumps forming.<br>
4. Return the chicken to the pan. Add the parsley and simmer gently for
8-10 minutes to ensure chicken is fully cooked.<br>
5. Remove from the heat and stir in the fromage frais.<br>
6. Serve with rice.<br>
","15 minutes<br>
20 minutes (+ rice)","","Source=Rosemary Conley Low Fat Cookbook Two<br>
OR uses Normandy fromage frais, skimmed milk.<br>
Cream might be more reliable than fromage frais and less likely to
curdle.<br>
","0"</tt><br>
<br>
</blockquote>
Here's my MySQL table:<br>
<blockquote><tt>mysql> use food<br>
Reading table information for completion of table and column names<br>
You can turn off this feature to get a quicker startup with -A<br>
<br>
Database changed<br>
mysql> describe recipe;<br>
+---------------+----------------------+------+-----+---------+-------+<br>
| Field | Type | Null | Key | Default | Extra |<br>
+---------------+----------------------+------+-----+---------+-------+<br>
| name | varchar(200) | YES | | NULL | |<br>
| ingredients | text | YES | | NULL | |<br>
| method | text | YES | | NULL | |<br>
| notes | text | YES | | NULL | |<br>
| time | varchar(20) | YES | | NULL | |<br>
| cooktime | tinyint(3) unsigned | YES | | NULL | |<br>
| storecupboard | tinyint(5) unsigned | YES | | NULL | |<br>
| scnotes | varchar(100) | YES | | NULL | |<br>
| serves | tinyint(20) unsigned | YES | | NULL | |<br>
| source | varchar(200) | YES | | NULL | |<br>
| sourcenotes | varchar(200) | YES | | NULL | |<br>
+---------------+----------------------+------+-----+---------+-------+</tt><br>
</blockquote>
and I'm trying things along the lines off ...<br>
<br>
<blockquote><tt>load data infile '/tmp/k2' into table recipe fields
enclosed by '"' ;</tt><br>
</blockquote>
<br>
which doesn't do anything particularly useful. I've experimented
variations on the field seperator and also the LINES attribute. What
has made me finally through in the towel is that I tried putting all
the fields on one line and prefixing each line with a double
underscore, removing the leading ", changing the field seperator to %
and running<br>
<br>
<blockquote><tt>load data infile '/tmp/k2' into table recipe fields
enclosed by '"' lines starting by '__' ;</tt><br>
</blockquote>
<br>
<br>
and various other experiments on the same theme. I'm clearly missing
something, or it ain't possible. The closest I've managed is to get 13
records imported, but they were mostly NULL values. <br>
<br>
It's no big deal really. There are only 13 records to import, and I can
easily start again, but I would like to know if it's possible for my
own pride and sanity. <br>
<br>
Finally, I'm normally a CLI fan and avoid GUIs. But if I'm going to be
typing in a load of recipes I thought I'd investigate the GUI
front-ends to mysql. Does anyone use any or have any recommendations?
I'm running Debian.<br>
<br>
Cheers,<br>
<br>
Dougie<br>
<br>
</body>
</html>