- 3.3 Creating and Using a Database
- 3.3.1 Creating and Selecting a Database
- 3.3.2 Creating a Table
- 3.3.3 Loading Data into a Table
- 3.3.4 Retrieving Information from a Table
After creating your table, you need to populate it. The
LOAD DATA and
INSERT statements are useful for
this.
Suppose that your pet records can be described as shown here.
(Observe that MySQL expects dates in
'YYYY-MM-DD' format; this may be different
from what you are used to.)
| name | owner | species | sex | birth | death |
| Fluffy | Harold | cat | f | 1993-02-04 | |
| Claws | Gwen | cat | m | 1994-03-17 | |
| Buffy | Harold | dog | f | 1989-05-13 | |
| Fang | Benny | dog | m | 1990-08-27 | |
| Bowser | Diane | dog | m | 1979-08-31 | 1995-07-29 |
| Chirpy | Gwen | bird | f | 1998-09-11 | |
| Whistler | Gwen | bird | 1997-12-09 | ||
| Slim | Benny | snake | m | 1996-04-29 |
Because you are beginning with an empty table, an easy way to populate it is to create a text file containing a row for each of your animals, then load the contents of the file into the table with a single statement.
You could create a text file pet.txt
containing one record per line, with values separated by tabs,
and given in the order in which the columns were listed in the
CREATE TABLE statement. For
missing values (such as unknown sexes or death dates for animals
that are still living), you can use NULL
values. To represent these in your text file, use
\N (backslash, capital-N). For example, the
record for Whistler the bird would look like this (where the
whitespace between values is a single tab character):
Whistler Gwen bird \N 1997-12-09 \N
To load the text file pet.txt into the
pet table, use this statement:
mysql> LOAD DATA LOCAL INFILE '/path/pet.txt' INTO TABLE pet;
If you created the file on Windows with an editor that uses
\r\n as a line terminator, you should use
this statement instead:
mysql>LOAD DATA LOCAL INFILE '/path/pet.txt' INTO TABLE pet->LINES TERMINATED BY '\r\n';
(On an Apple machine running OS X, you would likely want to use
LINES TERMINATED BY '\r'.)
You can specify the column value separator and end of line
marker explicitly in the LOAD
DATA statement if you wish, but the defaults are tab
and linefeed. These are sufficient for the statement to read the
file pet.txt properly.
If the statement fails, it is likely that your MySQL
installation does not have local file capability enabled by
default. See Section 5.3.5, “Security Issues with LOAD
DATA LOCAL”, for information
on how to change this.
When you want to add new records one at a time, the
INSERT statement is useful. In
its simplest form, you supply values for each column, in the
order in which the columns were listed in the
CREATE TABLE statement. Suppose
that Diane gets a new hamster named “Puffball.” You
could add a new record using an
INSERT statement like this:
mysql>INSERT INTO pet->VALUES ('Puffball','Diane','hamster','f','1999-03-30',NULL);
String and date values are specified as quoted strings here.
Also, with INSERT, you can insert
NULL directly to represent a missing value.
You do not use \N like you do with
LOAD DATA.
From this example, you should be able to see that there would be
a lot more typing involved to load your records initially using
several INSERT statements rather
than a single LOAD DATA
statement.
User Comments
With Apple OS X: Use the terminal's drag and drop capability to insert the full path of the import file. This cuts down on the amount of typing, if you don't want deal with adding the import file into MySQL's data folder.
example:
%mysql --local-infile -u <username> -p <DatabaseName>
Enter password:<password>
mysql>load data local infile '<drag input file here>' into table <TableName>;
you can also drag windows files to the command window, but you'll need to change the backslashes to double-backslashes or forwardslashes, and remove the c: at the beginning. If you have quotes around the path, you'll need to delete them as well.
mysql> LOAD DATA LOCAL INFILE '<dir>/pet.txt' INTO TABLE pet FIELDS terminated by '<delimiter>';
--> just in case anyone experienced some discomfort following above instructions.
I used the full path name 'c:/<path to file>' and it worked fine, but my defaults aren't THE defaults. :-)
When dragging file in Windows I found the quotes needed to be kept in.
Doug Halls trick also works on Linux/BSD using the GNOME or KDE terminal emulator programs.
I was having some problems getting this working in XP but got it working after checking my syntax multiple times and then ENABLING local infiles.
Also after draging the file into the command window I had to replace the windows style backslashes with linux style forward slashes.
mysql> LOAD DATA LOCAL INFILE "C:\Documents and Settings\Dennis\Desktop\menagerie\pet.txt" INTO TABL
E PET;
ERROR 2 (HY000): File 'C:Documents and SettingsDennisDesktopmenageriepet.txt' not found (Errcode: 2)
mysql> LOAD DATA LOCAL INFILE "C:/Documents and Settings/Dennis/Desktop/menagerie/pet.txt" INTO TABL
E PET;
Query OK, 8 rows affected (0.00 sec)
Records: 8 Deleted: 0 Skipped: 0 Warnings: 0
I'm thrilled and amazed about being able to drop files into a console to get the path, but
Worth keeping an eye on.for people not using the mouse, you can put a path from your home directory using '~/' in the path.
Eg if your file is 'pets.txt' in your home directory
LOAD DATA LOCAL INFILE '~/pets.txt' INTO TABLE pet;
NOTE: This only works on *nix opperating systems and OSX (as far as i know).
NOTE: A blank line at the end of your infile gives wierd results:
One thing it took me a moment to understand is that 's didn't seem to work. "s did.
I noticed that in case you want to use LOAD DATA LOCAL INFILE or the INSERT commands to fill your table fields with data, special care should be taken for INTEGER AUTO_INCREMENT fields. In the case of "LOAD DATA LOCAL INFILE" command I pressed TAB where was supposed to enter AUTO_INCREMENT INT value, and it was accepted. In the case of "INSERT INTO table VALUES (.." command I just entered the name of the column <without quotes> where was supposed to supply the value of that field, and it worked! Actually in all cases it worked also when entering any string value like: 'i' or 's'. It will alwys be converted to the required INT value. I thought this may help!
Used \n for NULL but not working..It takes 0 value for tht(date value).Even tried to write NULL but takes 0.
Insert is accepting value NULL but not load command
With a defalut installation from FreeBSD ports, I had to use the command line
mysql -u user -p --local-infile menagerie
to start the mysql monitor, else the LOAD DATA LOCAL command failed with an error like the following:
ERROR 1148 (42000): The used command is not allowed with this MySQL version
Also, the pet.txt downloaded from the mysql.com link (tgz archive) appears to have ended the lines with '\n' instead of '\r\n'.
I tried preparing the text for load data local infile (pet.txt) on my mac (osX). Wasted a lot of time because there was no clear example of the text file format. The example should state specifically for us newbies:
Buffy,Gwen,cat,f,1998-03-02,/N,
Snuffy,Tom,dog,m,1999-0209,/N,
Additionally, the command should read:
mysql> LOAD DATA LOCAL INFILE '/path/pets.txt' INTO TABLE pet fields terminated by ',';
Using 5.0.19 on linux kernel 2.6.9-11, i found that i had to specify FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n';
without that, i couldn't import any records from my file. For some reason I was under the impression that commas and linefeeds were the default, but without specifying them I had no success.
If you are uploading to a remote server, check the file after uploading before using "LINES TERMINATED BY. For example, because Microsoft Excel on my Macintosh writes line endings as '\r' (0x0d), I used the clause LINES TERMINATED BY '\r' in my command. But this gave me a syntax error at the end of the first line. When I remove this clause, it worked! Further investigation showed that the file on the remote server had \n (0x0a) line endings. Apparently, my web hosting provider (DreamHost) automatically changes line endings of uploaded .txt files to the Unix standard.
mysql> load data local infile 'pet.txt' into table pet;
ERROR 1148 (42000): The used command is not allowed with this MySQL version
it is because :
"If LOAD DATA LOCAL INFILE is disabled, either in the server or the client, a client that attempts to issue such a statement receives the following error message:
ERROR 1148: The used command is not allowed with this MySQL version"
hi,
i got the following error as well
ERROR 1148 (42000): The used command is not allowed with this MySQL version
it was after an upgrade and was due to not running
/usr/bin/mysql_fix_privilege_tables
correctly
hth
jpd
It seems like mysql ignore trailing whitespaces. Here is my input:
Fluffy.TAB.Harold.TAB.cat.TAB.f.TAB.1993-02-04.TAB.\N
Claws.TAB.Gwen.TAB.cat.TAB.m.TAB.1994-03-17.TAB.\N
Buffy.TAB.Harold.TAB.dog.TAB.f.TAB.1989-05-13.TAB.\N
Fang.TAB.Benny.TAB.dog.TAB.m.TAB.1990-08-27.TAB.\N
Bowser.TAB.Diane.TAB.dog.TAB.m.TAB.1979-08-31 .TAB.1995-07-29
Chirpy.TAB.Gwen.TAB.bird.TAB.f.TAB.1998-09-11.TAB.\N
Whistler.TAB.Gwen.TAB.bird.TAB.\N.TAB.1997-12-09 .TAB.\N
Slim.TAB.Benny.TAB.snake.TAB.m.TAB. 1996-04-29.TAB.\N
my tab token is ".TAB." (no whitespace in between). There are few instances where I had a tab and a whitespace and it works as normal. For the example, in the last line, there is a tab, a space and the date 1996-04-29 and it works okay. So mysql ignores white spaces between token.
if you get this
> mysql menagerie -u root -p < load_pet_tbl.sql
Enter password:
ERROR 1148 (42000) at line 5: The used command is not allowed with this MySQL version
you can use this to get around it
> mysql menagerie --local_infile=1 -u root -p < load_pet_tbl.sql
Enter password:
im using mysql 5.0 on windows xp pro... i tried loading data from a txt file using drag and drop but cant seem to work it out. here's what i did. THIS EXAMPLE IS MEANT FOR MAC OS, BUT IT WORKED IN WINDOWS AS WELL.
mysql> INSERT INTO TABLE_PET VALUES('Puffball','Diane','hamster','f','1999-03-30',NULL);
on Windows 2003 Server, you need to use double quotes instead of single quotes for the value of LINES TERMINATED:
LOAD DATA LOCAL INFILE "C:/pets.txt" INTO TABLE pet LINES TERMINATED BY "\r\n";
OS = Slackware with 2.4.29 kernel
mySQL = 5.0.24a
connection = ssh
editor = vim 6.3.7
I was able to load a 4K row tab delimited text file with
"mysql>load data infile 'filepath/file' into table yourtable;"
I was NOT able to load the same file using
"mysql>load data local infile 'filepath/file' into table yourtable;"
I was receiving the following:
"ERROR 1148 (42000): The used command is not allowed with this MySQL version"
The tutorial above does not show an example of using the command without the word "local" in it.
To ensure that even special characters in the text file are added to the table as records the complete command we use is
load data local infile 'file.txt' into table tablename fields terminated by '\t' optionally enclosed by '\\' escaped by '\\' lines terminated by '\n';
So a quick way to clarify this in Windows XP, running mysql 5.0
Load your local infile as follows
LOAD:(Just change the \ to /
mysql> LOAD DATA LOCAL INFILE 'C:/menagerie-db/pet.txt' INTO TABLE pet;
Query OK, 8 rows affected (0.00 sec)
Records: 8 Deleted: 0 Skipped: 0 Warnings: 0
Note that a tab should be added after each element in the .txt version of the table, even after the last elements in each row.
I skipped the last tab at each row and ended up with a table with "0000-00-00" as death dates instead of NULL, the only death dates which where really correct was death dates for the dog that actually died, and the pet at the very last row.
On Mac OS X, lines of a file are normally terminated by '\n' just like on any other Unix system. The line termination of '\r' is used on the previous Mac operating systems such as OS9. Also, if a file was created on Mac OS9 but used on a Mac OS X system then you need to use '\r' as well. Of course, this last comment goes for any file -- you need to be aware of where it was created and not so much as to where you are using it.
I am using Windows. I originally used the backslash to separate directories as in DOS
mysql> load data local infile 'C:\Users\Matt DesVoigne\Documents\pet.txt' into table pet
-> LINES TERMINATED BY '\r\n';
That did NOT work. I had to use forward slash to separate directories:
mysql> load data local infile 'C:/Users/Matt DesVoigne/Documents/pet.txt' into table pet
-> LINES TERMINATED BY '\r\n';
Thank you. Matt
I found on my computer (running Windows XP) that if I loaded the file without specifying "Lines terminated by '\r\n'" that the death dates that were supposed to be null were all '0000-00-00'.
I deleted the information and reinserted the file specifying the line terminator, and it inserted the NULLs correctly.
On a Windows-based system, for the INFILE parameter if using backslashes for the filepath, you'll need to escape the backslashes with an additional backslash:
LOAD DATA LOCAL INFILE 'C:\\directory\\path\\to\\file\\pet.txt' into TABLE pet fields terminated by ',' lines terminated by '\n';
The alternative as others have already mentioned is to use forward slashes instead of backslashes.
Below is the input file that I used. In the above LOAD DATA command, I specified "fields terminated by ','" and "lines terminated by '\n'":
Fluffy,Harold,cat,f,1993-02-04,\N,
Claws,Gwen,cat,m,1994-03-17,\N,
Buffy,Harold,dog,f,1989-05-13,\N,
Fang,Benny,dog,m,1990-08-27,\N,
Bowser,Diane,dog,m,1979-08-31,1995-07-29,
Chirpy,Gwen,bird,f,1998-09-11,\N,
Whistler,Gwen,bird,\N,1997-12-09,\N,
Slim,Benny,snake,m,1996-04-29,\N,
Note, depending on how (e.g. text editor used) and/or where (system type) the text file was created, the line termination can be different:
- Line Feed (LF): \n
- Carriage Return (CR): \r
- Carriage Return and Line Feed (CR+LF): \r\n
Wikipedia has an article for 'newline' which goes into much greater detail on this:
http://en.wikipedia.org/wiki/Newline
I used the following pet.txt example on windows vista using the Mysql version 5.1.45 Community:
Fluffy Harold cat f 1993-02-04 \N
Claws Gwen cat m 1994-03-17 \N
Buffy Harold dog f 1989-05-13 \N
Fang Benny dog m 1990-08-27 \N
Bowser Diane dog m 1979-08-31 1995-07-29
Chirpy Gwen bird f 1998-09-11 \N
Whistler Gwen bird \N 1997-12-09 \N
Slim Benny snake m 1996-04-29 \N
* notice the whitespace in between each field is a tab and also **there is a tab following each line**, besides the last line.
I used the syntax:
mysql> LOAD DATA LOCAL INFILE 'C:/Databases/pet.txt' into table pet
-> LINES TERMINATED BY '\r\n';
* This allowed the file to load with no errors or warnings.
I had to piece parts together from a few different peoples posts on here in order to come to this conclusion and have everything working right.
Add your own comment.