[Nelug] MySQL and Importing

[Scott Wilcox]

Installing yourself a copy of PHP My Admin (http://www.phpmyadmin.net 
iirc). It'll clean up and import CSV and its been flawless in my use of 
it. I've never had anything it wouldn't import up to you.

Scott.

Dougie Nisbet wrote:

> 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 
> http://dev.mysql.com/doc - much better than my ancient O'Reilly book).
>
> 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.
>
> 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.
>
> 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.
>
>     recipe,srvs,source,sourcenotes,ingredients,method,time,cooktime,Notes,SC
>     "Quick chicken korma
>     prep=15m
>     cook=20m (plus rice)
>     SC=?
>     serves=4","4","Rosemary Conley Low Fat Cookbook Two
>     ","OR uses Normandy fromage frais, skimmed milk.","4 skinless
>     chicken breasts cut into chunks
>     2 medium onions finely chopped
>     2 crushed garlic cloves
>     1 veg stock cube dissolved in 150ml boiling water
>     1 tbsp plain flour
>     300ml milk
>     1 tbsp chopped fresh flat leaf pasley
>     2 tbsp fromage frais
>     ","1. Season the chicken pieces with salt and pepper and fry for 7
>     minutes. Remove from pan and set aside.
>     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.
>     3. Gradually add the remaining stock and the milk, stirring
>     continuously to prevent any lumps forming.
>     4. Return the chicken to the pan. Add the parsley and simmer
>     gently for 8-10 minutes to ensure chicken is fully cooked.
>     5. Remove from the heat and stir in the fromage frais.
>     6. Serve with rice.
>     ","15 minutes
>     20 minutes (+ rice)","","Source=Rosemary Conley Low Fat Cookbook Two
>     OR uses Normandy fromage frais, skimmed milk.
>     Cream might be more reliable than fromage frais and less likely to
>     curdle.
>     ","0"
>
> Here's my MySQL table:
>
>     mysql> use food
>     Reading table information for completion of table and column names
>     You can turn off this feature to get a quicker startup with -A
>
>     Database changed
>     mysql> describe recipe;
>     +---------------+----------------------+------+-----+---------+-------+
>     | Field         | Type                 | Null | Key | Default |
>     Extra |
>     +---------------+----------------------+------+-----+---------+-------+
>     | name          | varchar(200)         | YES  |     | NULL   
>     |       |
>     | ingredients   | text                 | YES  |     | NULL   
>     |       |
>     | method        | text                 | YES  |     | NULL   
>     |       |
>     | notes         | text                 | YES  |     | NULL   
>     |       |
>     | time          | varchar(20)          | YES  |     | NULL   
>     |       |
>     | cooktime      | tinyint(3) unsigned  | YES  |     | NULL   
>     |       |
>     | storecupboard | tinyint(5) unsigned  | YES  |     | NULL   
>     |       |
>     | scnotes       | varchar(100)         | YES  |     | NULL   
>     |       |
>     | serves        | tinyint(20) unsigned | YES  |     | NULL   
>     |       |
>     | source        | varchar(200)         | YES  |     | NULL   
>     |       |
>     | sourcenotes   | varchar(200)         | YES  |     | NULL   
>     |       |
>     +---------------+----------------------+------+-----+---------+-------+
>
> and I'm trying things along the lines off ...
>
>     load data infile '/tmp/k2' into table recipe fields enclosed by '"' ;
>
>
> 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
>
>     load data infile '/tmp/k2' into table recipe fields enclosed by
>     '"' lines starting by '__' ;
>
>
>
> 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.
>
> 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.
>
> 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.
>
> Cheers,
>
> Dougie
>
>------------------------------------------------------------------------
>
>_______________________________________________
>Nelug mailing list   -   Nelug at mailman.lug.org.uk
>http://mailman.lug.org.uk/mailman/listinfo/nelug
>http://www.nelug.org.uk/
>
>------------------------------------------------------------------------
>
>No virus found in this incoming message.
>Checked by AVG Free Edition.
>Version: 7.1.375 / Virus Database: 267.14.23/242 - Release Date: 26/01/2006
>
>  
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://mailman.lug.org.uk/pipermail/durham/attachments/20060127/eeb23439/attachment.htm