|
|
Posted by Vizion on 11/07/05 05:42
While I think this is primarily a mysql syntax problem I have asked on the
mysql forum but not got a reply. I am trying using php to set up a system for
loading a new data base using the Load Data Infile statement. I could not get
it to work properly with php so I thought I would try some command line test.
Does anyone know the loaction of a suitable php script that would handle a
list of files and tables for loading into a database (preferably doing an
initial dummy run using temporary tables with an error report). If not when I
get this wretched syntax issue solved I guess I might have one available
fairly soon.
Here are my notes:
This exercise was a test in preparation for a plan to use Load Data Infile for
initallising a new database comprising over 80 tables. The database currently
has no data. The first field is auto-increment.
What am I doing wrong here?
If I cannot do it using Load Data Infile what alternatives would you suggest?
___________________________________________________________
Here is mysql command line dialogue on a win XP development machine:
____________________________________________________________
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 85 to server version: 5.0.13-rc-nt-log
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> use NewBuddies;
Database changed
mysql> load data local infile
'E:/Development/LBuddies/Documemntation/lbcm_diet_
types.txt' REPLACE into table lbcm_diet_types fields terminated by ','
ENCLOSED
by '"';
ERROR 1265 (01000): Data truncated for column 'Help' at row 1
mysql> describe lbcm_diet_types;
+-----------------+-------------+------+-----+-------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+-------------+------+-----+-------------+----------------+
| Diet_Type_ID | int(11) | NO | PRI | NULL | auto_increment |
| Diet_Type_Title | varchar(30) | NO | UNI | Enter Title | |
| Diet_Type_Descr | text | NO | | | |
| Help | int(11) | NO | | 1 | |
+-----------------+-------------+------+-----+-------------+----------------+
4 rows in set (0.01 sec)
mysql> select * from lbcm_diet_types;
Empty set (0.00 sec)
mysql>
_______________________________________________________________________________
Here is the content of the first file created as a text file using editor
macromedia:
_______________________________________________________________________________
"1","Unlimited","Anything goes","1"
"2","Lacto-Vegetarian","Vegetarian consuming milk products, no meat, no
fish","1"
"3","Fish_Lacto-Vegetarian","Lacto Vegetarian plus fish","1"
I then realized my obvious mistake two "'"s in the third field on line two. I
removed "REPLACE" from the command and ran it again:
mysql> load data local infile
'E:/Development/LBuddies/Documemntation/lbcm_diet_
types.txt' into table lbcm_diet_types fields terminated by ',' ENCLOSED by
'"';
Query OK, 2 rows affected, 3 warnings (0.00 sec)
Records: 2 Deleted: 0 Skipped: 0 Warnings: 3
mysql> select * from lbcm_diet_types;
+--------------+-----------------------+----------------------------+------+
| Diet_Type_ID | Diet_Type_Title | Diet_Type_Descr | Help |
+--------------+-----------------------+----------------------------+------+
| 1 | Unlimited | Anything goes | 1 |
| 3 | Fish_Lacto-Vegetarian | Lacto Vegetarian plus fish | 0 |
+--------------+-----------------------+----------------------------+------+
2 rows in set (0.00 sec)
/* OK we have progress BUT */
/*What happened to line 2? */
/* Why 0 for the help ID on line 3? */
/* So I amended the file to read: */
"4","Diabetic","See Diet Sheet","1"
"5","Glutenfree","No wheat products or other glutenous grains","1"
"6","Nut allergic","Avoid all nut products","1"
/* and with the command:*/
mysql> load data local infile
'E:/Development/LBuddies/Documemntation/lbcm_diet_
types.txt' into table lbcm_diet_types fields terminated by ',' ENCLOSED by
'"';
Query OK, 2 rows affected, 3 warnings (0.00 sec)
Records: 2 Deleted: 0 Skipped: 0 Warnings: 3
/* I got:*/
mysql> select * from lbcm_diet_types;
+--------------+-----------------------+----------------------------+------+
| Diet_Type_ID | Diet_Type_Title | Diet_Type_Descr | Help |
+--------------+-----------------------+----------------------------+------+
| 1 | Unlimited | Anything goes | 1 |
| 3 | Fish_Lacto-Vegetarian | Lacto Vegetarian plus fish | 0 |
| 4 | Diabetic | See Diet Sheet | 1 |
| 6 | Nut allergic | Avoid all nut products | 0 |
+--------------+-----------------------+----------------------------+------+
4 rows in set (0.00 sec)
mysql>
/* showing that the second line from this file is again being dropped and the
last line gets 0 for help! - I must be missing something obvious but I am
damned if I can see it <groans>
/* So I realize it might be due to the windows CR/LF oddity therefore I try:
*/
mysql> load data local infile
'E:/Development/LBuddies/Documemntation/lbcm_diet_
types.txt' into table lbcm_diet_types fields terminated by ',' ENCLOSED by
'"'L
ines terminated by '\r\n';
Query OK, 1 row affected (0.00 sec)
Records: 3 Deleted: 0 Skipped: 2 Warnings: 0
/* This sounds more promising */
mysql> select * from lbcm_diet_types;
+--------------+-----------------------+----------------------------------------
-----+------+
| Diet_Type_ID | Diet_Type_Title | Diet_Type_Descr
| Help |
+--------------+-----------------------+----------------------------------------
-----+------+
| 1 | Unlimited | Anything goes
| 1 |
| 3 | Fish_Lacto-Vegetarian | Lacto Vegetarian plus fish
| 0 |
| 4 | Diabetic | See Diet Sheet
| 1 |
| 5 | Glutenfree | No wheat products or other glutenous gr
ains | 1 |
| 6 | Nut allergic | Avoid all nut products
| 0 |
+--------------+-----------------------+----------------------------------------
-----+------+
5 rows in set (0.00 sec)
/* Part of the problem solved - but what about those darned zeros? */
Any ideas?
Thanks in advance
david
--
40 yrs navigating and computing in blue waters.
English Owner & Captain of British Registered 60' bluewater Ketch S/V Taurus.
Currently in San Diego, CA. Sailing bound for Europe via Panama Canal after
completing engineroom refit.
[Back to original message]
|