Re[2]: Creating Tab Delimited Textfile to Load Data Into MySQL Table
Steve, But still only one record, that first one, come in ... at least as evidenced by the SELECT * FROM [table_name]. What do I try next? Next thing is you send the CREATE TABLE statement of your table, plus at least two lines of your import text file (not as attachment, the list manager would skip it). Regards, -- Stefan Hinz [EMAIL PROTECTED] iConnect GmbH http://iConnect.de Heesestr. 6, 12169 Berlin (Germany) Telefon: +49 30 7970948-0 Fax: +49 30 7970948-3 [filter fodder: sql, mysql, query] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Creating Tab Delimited Textfile to Load Data Into MySQL Table
Stefan and Gerald-- Thanks for the input. Here's how I solved it ... I used a textfile that had worked for me the very first time I did such an exercise weeks ago. This particular textfile came on a CD with the book I was using. And by copying and pasting, so that all the spacing and line breaks were out of this particular textfile, but the actual data were new, I finally had a textfile that worked. It's annoying, but I can live with this for now. Steve Tiano Steve, But still only one record, that first one, come in ... at least as evidenced by the SELECT * FROM [table_name]. What do I try next? Next thing is you send the CREATE TABLE statement of your table, plus at least two lines of your import text file (not as attachment, the list manager would skip it). Regards, -- Stefan Hinz [EMAIL PROTECTED] iConnect GmbH http://iConnect.de Heesestr. 6, 12169 Berlin (Germany) Telefon: +49 30 7970948-0 Fax: +49 30 7970948-3 [filter fodder: sql, mysql, query]
Creating Tab Delimited Textfile to Load Data Into MySQL Table
I'm just starting out, doing tutorials and exercises up the wazoo, trying to learn MySQL and PHP. I've created a table in a test database. When I do a DESCRIBE [table_name], the columns come up just as they should. Populating them with data's another story. I've created a textfile in a text editor. The first row is the column heads; succeeding rows are actual data. The entries in a row are separated by a single tab; at the end of a row, I'm hitting RETURN. I entered twelve rows of data. The book I'm working from shows NULL as some entries; and the last column, shows a timestamp in each cell of that last column. I typed NULL every time my sample showed it and all fourteen digits of each timestamp entry. So far, so good? Back in MySQL, at the prompt, I entered: load data local infile ' and then I dragged the textfile into the Terminal window. I was greeted with: Query OK, 1 row affected (0.44 sec) Records: 1 Deleted: 0 Skipped: 0 Warnings: 3 Question: Where do I find these Warnings named and detailed? When I do a SELECT * FROM [table_name], the column heads appear just fine, where and as they should. But there's only a single row of data, when I typed in twelve rows in the textfile. Also, that one row of data is all wrong. Only the first column, entitled userid is correct, the number 1. The other entries merely parrot the column heads, and they're off by one, with the second and third in the second column, and all entries incorrectly moved one column to the left. The last column, which is where the timestamp should go, contains a batch of zeros. What do I need to do to fix this? Thank you. Steve Tiano - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Creating Tab Delimited Textfile to Load Data Into MySQL Table
Stephen Tiano wrote: I'm just starting out, doing tutorials and exercises up the wazoo, trying to learn MySQL and PHP. I've created a table in a test database. When I do a DESCRIBE [table_name], the columns come up just as they should. Populating them with data's another story. I've created a textfile in a text editor. The first row is the column heads; Why? succeeding rows are actual data. The entries in a row are separated by a single tab; at the end of a row, I'm hitting RETURN. Did you specify that lines be terminated by '\r' ? I entered twelve rows of data. The book I'm working from shows NULL as some entries; and the last column, shows a timestamp in each cell of that last column. I typed NULL I believe that should be \N every time my sample showed it and all fourteen digits of each timestamp entry. So far, so good? Back in MySQL, at the prompt, I entered: load data local infile ' and then I dragged the textfile into the Terminal window. I was greeted with: Query OK, 1 row affected (0.44 sec) Records: 1 Deleted: 0 Skipped: 0 Warnings: 3 Question: Where do I find these Warnings named and detailed? You have only one record, because you have only 1 line. RETURN is not the default line terminator. You can't see the warnings. When I do a SELECT * FROM [table_name], the column heads appear just fine, where and as they should. But there's only a single row of data, when I typed in twelve rows in the textfile. Also, that one row of data is all wrong. Only the first column, entitled userid is correct, the number 1. The other entries merely parrot the column heads, and they're off by one, with the second and third in the second column, and all entries incorrectly moved one column to the left. The last column, which is where the timestamp should go, contains a batch of zeros. What do I need to do to fix this? Fix your input file, and use the right options for the load data command. Posting bothe would have helped. Thank you. Steve Tiano - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Creating Tab Delimited Textfile to Load Data Into MySQL Table
Stephen, The entries in a row are separated by a single tab; at the end of a row, I'm hitting RETURN. I entered twelve rows of data. The book I'm working from shows NULL as some entries; and the last column, shows a timestamp in each cell of that last column. I typed NULL every time my sample showed it and all fourteen digits of each timestamp entry. So far, so good? For your import file, you should use \N instead of NULL. \N is the placeholder for NULL values in import files. Also, if you happen to work under Windows, you should think of the fact the some Windows editors will store Return as \r\n. MySQL, however, will expext \n (new line) by default. If your editor is clever enough, tell it to save the file in Unix format (the only difference is it won't store \r\n but \n only). If you cannot do this, use the LOAD DATA command like this: LOAD DATA ... LINES TERMINATED BY '\r\n' Back in MySQL, at the prompt, I entered: load data local infile ' and then I dragged the textfile into the Terminal window. I was greeted with: mysql (the command line tool) is not intended for use as a drag and drop utility. Anyway, it seems to have worked _somehow_: Query OK, 1 row affected (0.44 sec) Records: 1 Deleted: 0 Skipped: 0 Warnings: 3 Good chance you get the warnings because of \r\n, see above. Question: Where do I find these Warnings named and detailed? Hmm, you have to edit my.cnf (my.ini, if you're under Windows) and enter the following in the [mysqld] section: warnings Then, restart the MySQL server. From now on, warnings will be written to the error log file, too. This log file sits in the datadir by default. The datadir is the place where MySQL places database directories. Under Windows, the installation default is c:\mysql\data. The error file ends with .err; under Windows, the name is mysql.err. Last not least, here's my advice to read the manual :) http://www.mysql.com/doc/en Regards, -- Stefan Hinz [EMAIL PROTECTED] iConnect GmbH http://iConnect.de Heesestr. 6, 12169 Berlin (Germany) Telefon: +49 30 7970948-0 Fax: +49 30 7970948-3 [filter fodder: sql, mysql, query] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Creating Tab Delimited Textfile to Load Data Into MySQL Table
Thanks to Gerald and Stefan for taking the time. Here's where I am ... Perhaps because I'm still wet behind the ears, I brainlessly included the column heads in the textfile. Of course, that's unnecessary. I substituted \N for NULL and stuck \n at the end of each line--the latter because I'm not on Windows. I'm actually on a Macintosh G3, running OS X.2, which is why the drag and drop maneuver works. I did continue to press RETURN at the end of each line, else I'd've had one incredible run-on. But still only one record, that first one, come in ... at least as evidenced by the SELECT * FROM [table_name]. What do I try next? And thanks again Steve Tiano Stephen, The entries in a row are separated by a single tab; at the end of a row, I'm hitting RETURN. I entered twelve rows of data. The book I'm working from shows NULL as some entries; and the last column, shows a timestamp in each cell of that last column. I typed NULL every time my sample showed it and all fourteen digits of each timestamp entry. So far, so good? For your import file, you should use \N instead of NULL. \N is the placeholder for NULL values in import files. Also, if you happen to work under Windows, you should think of the fact the some Windows editors will store Return as \r\n. MySQL, however, will expext \n (new line) by default. If your editor is clever enough, tell it to save the file in Unix format (the only difference is it won't store \r\n but \n only). If you cannot do this, use the LOAD DATA command like this: LOAD DATA ... LINES TERMINATED BY '\r\n' Back in MySQL, at the prompt, I entered: load data local infile ' and then I dragged the textfile into the Terminal window. I was greeted with: mysql (the command line tool) is not intended for use as a drag and drop utility. Anyway, it seems to have worked _somehow_: Query OK, 1 row affected (0.44 sec) Records: 1 Deleted: 0 Skipped: 0 Warnings: Good chance you get the warnings because of \r\n, see above.
