Importing data into one of your MySQL tables is really easy using the Load Data command. In this exercise we will look at importing a comma separated list of countries into our Country table. Below is the script you would run in your MySQL Query Browser (or phpMyAdmin tool):
LOAD DATA LOCAL INFILE '<span style="color: #0000ff;">c:/samples/countrylist.csv</span>' INTO TABLE <span style="color: #0000ff;">Country <span style="color: #000000;">IGNORE 1 LINES</span></span><code></code> FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' (<span style="color: #0000ff;">Value, Description, ActiveFlag, SequenceID</span>);
Now we’ll break down a couple of the lines above in more detail.
LOAD DATA LOCAL INFILE
The Load Data Infile statement loads in rows from your text file into the table specified in the next line (INTO TABLE). Above you’ll notice I inlcuded the LOCAL option. This means the file will be read locally from your PC, and you have two additional options for identifying the location:
- Full Path – include the entire path including the file name, but instead of using a backslash, use a forward slash (like above).
- Relative Path – just include the file name. Note: make sure the .csv file is in the same directory as your MySQL Query Browser.
If you do not include the LOCAL option, then the .csv file must be on the server. Like the LOCAL option, if you include the full path, it is pretty simple, the specified path must exist on the server. If you use the relative path, the plot thickens. There are two ways to identify the location:
- ./countrylist.csv – the leading ./ is treated as a reference to the server’s data directory.
- countrylist.csv – is read from the default database’s directory. What that means is if your default database is called PrimaryDatabase, and you want to upload the countrylist.csv file into a database called CountryDatabase (yeah, I know the sample names are lame!), you would have to modify the INTO statement like this:
INTO TABLE CountryDatabase.Country
IGNORE 1 LINES
This statement is typically used when you have a header record, if you have no header, you can leave it out.
FIELDS TERMINATED BY
This is the character that delimits each field. In the code snippet above, I’m using a comma. If you had a tab-delimited file, you would use ‘\t’ instead of the comma.
LINES TERMINATED BY
The TERMINATED BY portion is one of two options, and happens to be the option I always happen to take. This specifies the row delimiter. By default, it is the standard \n, although you could use \r\n. If you don’t specify TERMINATED BY, you and specify STARTING BY. What this does is allows you to prefix any row with a string of characters. For example, you could have the following statement:
LINES STARTED BY ‘pre_’
And then three rows of data that looks like this:
pre_”Joe”,”Smith”,1
pre_”Keith”,”Booth”,2
“Gary”,”Williams”,3
Only the first two rows would import because they have the pre_ STARTED BY prefix.
The last line is the list of fields . If you leave out that line, your file is expected to have a field for each column in the target table. If you are only importing a subset of the columns, then you need to specify the columns so the importer can properly map the data.
