Re: Batch load of data problems (fwd)
Ed, If the code below is exactly what you are using, please check 6.4.9 LOAD DATA INFILE Syntax for the default separator, terminator, etc rules. Regards, =dn - Original Message - From: William R. Mussatto [EMAIL PROTECTED] To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: 31 January 2002 01:01 Subject: Re: Batch load of data problems (fwd) I'm forwarding this to the list since I can't offer any more advice myself. -- Forwarded message -- Date: Wed, 30 Jan 2002 19:41:32 -0500 From: [EMAIL PROTECTED] To: William R. Mussatto [EMAIL PROTECTED] Subject: Re: Batch load of data problems I guess I didn't make myself very clear. Here's what I run: mysql create.sql This is create.sql: use test; create table if not exists persons (pid mediumint unsigned not null auto_increment primary key, name varchar(50)); load data infile test.dat replace into table persons; This is test.dat, located both under the data directory and the test database directory (tab-delimited): 1Joseph 2Bill The table looks like this after: select * from persons; PidName 1 NULL 2 NULL I don't have Joseph and Bill in the table persons. This is the problem. Have any ideas? Thanks, Eurico - 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 - 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
Batch load of data
Hi. I have a question regarding loading data from text files into tables using the LOAD DATA statement. I tried this under Win98 without success. Basically, I have a file called create.tbl which has CREATE TABLE statements. The last statements in this file are of the form: LOAD DATA INFILE file.dat REPLACE INTO TABLE tbl_name; Then I ran, mysql create.tbl at the DOS prompt. Although it gave no errors, the tables were filled with NULL values (other than the NOT NULL AUTO_INCREMENT fields). I tried this with just one simple table to start with - it is defined as: CREATE TABLE tbl_name (pid MEDIUMINT NOT NULL AUTO_INCREMENT, name VARCHAR(50), PRIMARY KEY (pid)); In the file.dat data file, I only put in 2 rows with one column (the name). I didn't put a column with numbers because it's defined as AUTO_INCREMENT, so I figure the DBMS will take care of creating it properly. The result is that I do have two pids (1 and 2) but the name field in the table is NULL. I tried putting the name values in the file.dat file without quotes and also with single and double quotes, but to no avail. I also put the data file in my current directory, in the data directory and in the database directory, but all gave the same result. In addition, everytime I ran mysql create.tbl, the table kept growing - so, instead of only having 2 entries in it, I now have about 20 or so, where the first id field numbers from 1 to 20 and the other field is all null. Any ideas? Must I put a consecutive number field in the data file anyway as the first column? Thanks, Eurico - 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: Batch load of data problems
Hi. Resubmitting a post... Basically, my question is if you have defined an INT NOT NULL AUTO_INCREMENT id field in a table, and you want to load a data file into the table using the LOAD DATA INFILE command, must you also put the ids in the data file? Thanks. Eurico ** Hi. I have a question regarding loading data from text files into tables using the LOAD DATA statement. I tried this under Win98 without success. Basically, I have a file called create.tbl which has CREATE TABLE statements. The last statements in this file are of the form: LOAD DATA INFILE file.dat REPLACE INTO TABLE tbl_name; Then I ran, mysql create.tbl at the DOS prompt. Although it gave no errors, the tables were filled with NULL values (other than the NOT NULL AUTO_INCREMENT fields). I tried this with just one simple table to start with - it is defined as: CREATE TABLE tbl_name (pid MEDIUMINT NOT NULL AUTO_INCREMENT, name VARCHAR(50), PRIMARY KEY (pid)); In the file.dat data file, I only put in 2 rows with one column (the name). I didn't put a column with numbers because it's defined as AUTO_INCREMENT, so I figure the DBMS will take care of creating it properly. The result is that I do have two pids (1 and 2) but the name field in the table is NULL. I tried putting the name values in the file.dat file without quotes and also with single and double quotes, but to no avail. I also put the data file in my current directory, in the data directory and in the database directory, but all gave the same result. In addition, everytime I ran mysql create.tbl, the table kept growing - so, instead of only having 2 entries in it, I now have about 20 or so, where the first id field numbers from 1 to 20 and the other field is all null. Any ideas? Must I put a consecutive number field in the data file anyway as the first column? Thanks, Eurico - 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: Batch load of data problems
On Wed, 30 Jan 2002, Eurico de Sousa wrote: Date: Wed, 30 Jan 2002 16:56:24 -0500 From: Eurico de Sousa [EMAIL PROTECTED] To: mysql post [EMAIL PROTECTED] Subject: Re: Batch load of data problems Hi. Resubmitting a post... Basically, my question is if you have defined an INT NOT NULL AUTO_INCREMENT id field in a table, and you want to load a data file into the table using the LOAD DATA INFILE command, must you also put the ids in the data file? Thanks. Put 0 as a value in the field. This will trigger the auto-increment behavior I believe. Eurico ** Hi. I have a question regarding loading data from text files into tables using the LOAD DATA statement. I tried this under Win98 without success. Basically, I have a file called create.tbl which has CREATE TABLE statements. The last statements in this file are of the form: LOAD DATA INFILE file.dat REPLACE INTO TABLE tbl_name; Then I ran, mysql create.tbl at the DOS prompt. Although it gave no errors, the tables were filled with NULL values (other than the NOT NULL AUTO_INCREMENT fields). I tried this with just one simple table to start with - it is defined as: CREATE TABLE tbl_name (pid MEDIUMINT NOT NULL AUTO_INCREMENT, name VARCHAR(50), PRIMARY KEY (pid)); In the file.dat data file, I only put in 2 rows with one column (the name). I didn't put a column with numbers because it's defined as AUTO_INCREMENT, so I figure the DBMS will take care of creating it properly. The result is that I do have two pids (1 and 2) but the name field in the table is NULL. I tried putting the name values in the file.dat file without quotes and also with single and double quotes, but to no avail. I also put the data file in my current directory, in the data directory and in the database directory, but all gave the same result. In addition, everytime I ran mysql create.tbl, the table kept growing - so, instead of only having 2 entries in it, I now have about 20 or so, where the first id field numbers from 1 to 20 and the other field is all null. Any ideas? Must I put a consecutive number field in the data file anyway as the first column? Thanks, Eurico - 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 Sincerely, William Mussatto, Senior Systems Engineer CyberStrategies, Inc ph. 909-920-9154 ext. 27 - 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
Batch load of data - got it!
I found out why my LOAD DATA INFILE did not work. The editor I was using translated the tabs I entered to spaces. That's why my load data infile didn't work! I used the following command to load the data successfully: load data infile test.dat replace into table persons fields terminated by ' ' terminated by '\r\n'; The fields terminated argument in the load command is actually a single space - I changed my file to have one single space between the ID-number and the Name instead of a tab. I have to specify that the lines are terminated by '\r\n' since I'm running under Windows. The default is '\n'. I'm not familiar with Windows - didn't know that the tabs I was entering would be translated. Finally got it after trial and error. I wish there were more examples and detail in the manual on this topic. Eurico William R. Mussatto wrote: I'm forwarding this to the list since I can't offer any more advice myself. -- Forwarded message -- Date: Wed, 30 Jan 2002 19:41:32 -0500 From: [EMAIL PROTECTED] To: William R. Mussatto [EMAIL PROTECTED] Subject: Re: Batch load of data problems I guess I didn't make myself very clear. Here's what I run: mysql create.sql This is create.sql: use test; create table if not exists persons (pid mediumint unsigned not null auto_increment primary key, name varchar(50)); load data infile test.dat replace into table persons; This is test.dat, located both under the data directory and the test database directory (tab-delimited): 1Joseph 2Bill The table looks like this after: select * from persons; PidName 1 NULL 2 NULL I don't have Joseph and Bill in the table persons. This is the problem. Have any ideas? Thanks, Eurico - 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 - 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
