If you have data in a text file, you can easily upload them to one or more tables in a database.
In MySQL database (or MariaDB), using “load data infile” command, you can upload data from a text file to tables.
The load data infile command provides several flexible options to load various formats of data from text file to tables.
The following load data examples are covered in this tutorial:
- Basic Example to Load Data From Text File
- Upload Data Using “Fields terminated by” Option
- Upload Data Using “Enclosed by” Option
- Use Escape Character in Text File Data
- Upload Data Using “Lines terminated by” Option
- Ignore Line Prefix in Upload File Using “Starting By” Option
- Ignore Header Line from the Upload File
- Upload Only Specific Columns (and Ignore Others) from Upload File
- Use Variable during Upload with “Set” Option
- Write Shell Script to Load data from Text File
1. Basic Example to Load Data From Text File
In the following example, the employee1.txt file has the field values that are separate by tab.
# cat employee1.txt 100 Thomas Sales 5000 200 Jason Technology 5500 300 Mayla Technology 7000 400 Nisha Marketing 9500 500 Randy Technology 6000
By default, the load data infile command uses TAB as the default field delimiter.
First, go to the database where you want to upload the text file. In this example, we’ll upload the above employee1.txt file to the employee table located under thegeekstuff mysql database.
USE thegeekstuff;
The following MySQL command will load the records from the above employee1.txt file to the employee table as shown below. This command doesn’t use any extra options.
LOAD DATA INFILE 'employee1.txt' INTO TABLE employee;
Note: In the above example, the command assumes that the employee1.txt file located under the database directory. For example, if you are executing the above command in thegeekstuff database, then place the file under: /var/lib/mysql/thegeekstuff/
The following will be the output of the above command.
Query OK, 5 rows affected (0.00 sec) Records: 5 Deleted: 0 Skipped: 0 Warnings: 0
In the above:
- The first line “Query OK” says that the query got executed without any error. It also says that total of 5 rows were uploaded to the table. This also displays the time taken to upload the data from the text file to the table in seconds.
- The second line displays the the total rows uploaded, how many were skipped and how many records displayed warnings during the upload.
After the data is loaded, the following is what we’ll see in the employee table.
MariaDB [thegeekstuff]> select * from employee; +-----+--------+------------+--------+ | id | name | dept | salary | +-----+--------+------------+--------+ | 100 | Thomas | Sales | 5000 | | 200 | Jason | Technology | 5500 | | 300 | Mayla | Technology | 7000 | | 400 | Nisha | Marketing | 9500 | | 500 | Randy | Technology | 6000 | +-----+--------+------------+--------+
Note: If you want to backup and restore your whole MySQL database, use mysqldump command.
2. Upload Data Using “Fields terminated by” Option
In the following example, in the input file employee2.txt, the field values are separated by commas.
# cat employee2.txt 100,Thomas,Sales,5000 200,Jason,Technology,5500 300,Mayla,Technology,7000 400,Nisha,Marketing,9500 500,Randy,Technology,6000
To upload the above records to the employee table, use the following command.
During the upload, using the “FIELDS TERMINATED BY” option, you can specify the comma field delimiter as shown below.
LOAD DATA INFILE 'employee2.txt' INTO TABLE employee FIELDS TERMINATED BY ',';
Again, use this option only when the field values are separated by anything other than TAB. If the fields are terminted by colon, you’ll use the following option in the above command:
FIELDS TERMINATED BY ':';
If you are new to MySQL read this: MySQL Tutorial: Install, Create DB and Table, Insert and Select Records
The following are few basic errors that might happen during MySQL upload
Error 1: If the text file is not located under the proper directory, you might get the following “ERROR 13 (HY000) Can’t get stat of (Errcode: 2)” error message.
MariaDB [thegeekstuff]> LOAD DATA INFILE 'employee2.txt' INTO TABLE employee; ERROR 13 (HY000): Can't get stat of '/var/lib/mysql/thegeekstuff/employee2.txt' (Errcode: 2)
Also, you can specify the full path to the file in the load data infile command as shown below. If you do this, make sure the file can be accessed by mysql. If not, change the ownership to mysql appropriately. If not, you’ll get the load data infile permission denied error message.
MariaDB [thegeekstuff]> LOAD DATA INFILE '/data/employee2.txt' INTO TABLE employee;
Error 2: If you don’t specify the correct fields terminated by, then you’ll see some issues in the upload. In this example, only the first field “id” got uploaded. The value of all other fields are NULL. This is because, the following command doesn’t specify the field terminated by option, as the input file has comma as the field delimiter.
MariaDB [thegeekstuff]> LOAD DATA INFILE 'employee2.txt' INTO TABLE employee; Query OK, 5 rows affected, 20 warnings (0.00 sec) Records: 5 Deleted: 0 Skipped: 0 Warnings: 20 MariaDB [thegeekstuff]> select * from employee; +-----+------+------+--------+ | id | name | dept | salary | +-----+------+------+--------+ | 100 | NULL | NULL | NULL | | 200 | NULL | NULL | NULL | | 300 | NULL | NULL | NULL | | 400 | NULL | NULL | NULL | | 500 | NULL | NULL | NULL | +-----+------+------+--------+
3. Upload Data Using “Enclosed by” Option
In the following example, the input text file has the text field values enclosed by double-quotes. i.e The name and department values have double quotes around them.
# cat employee3.txt 100,"Thomas Smith","Sales & Marketing",5000 200,"Jason Bourne","Technology",5500 300,"Mayla Jones","Technology",7000 400,"Nisha Patel","Sales & Marketing",9500 500,"Randy Lee","Technology",6000
In this case, use the “enclosed by” option as shown below.
LOAD DATA INFILE 'employee3.txt' INTO TABLE employee FIELDS TERMINATED BY ',' ENCLOSED BY '"';
The above command will upload the records properly as shown below by the mysql select command:
MariaDB [thegeekstuff]> select * from employee; +-----+--------------+-------------------+--------+ | id | name | dept | salary | +-----+--------------+-------------------+--------+ | 100 | Thomas Smith | Sales & Marketing | 5000 | | 200 | Jason Bourne | Technology | 5500 | | 300 | Mayla Jones | Technology | 7000 | | 400 | Nisha Patel | Sales & Marketing | 9500 | | 500 | Randy Lee | Technology | 6000 | +-----+--------------+-------------------+--------+
Please note that when you are combining fields terminated by and fields enclosed by, you don’t have to use the keyword “FIELDS” twice as shown below, which will display the following error message:
FIELDS TERMINATED BY ',' FIELDS ENCLOSED BY '"';
The above will display the following “ERROR 1064 (42000)” error:
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'FIELDS ENCLOSED BY '"'' at line 4
4. Using Escape Character in Text File Data
Let us say you have comma in the value of a particular field.
For example, in the following example, the 2nd field name has the value in this format: “firstname, lastname”.
# cat employee4.txt 100,Thomas, Smith,Sales,5000 200,Jason, Bourne,Technology,5500 300,Mayla, Jones,Technology,7000 400,Nisha, Patel,Marketing,9500 500,Randy, Lee,Technology,6000
If you load the above file using the following command, you’ll see that it will display “10 warnings”
MariaDB [thegeekstuff]> LOAD DATA INFILE 'employee4.txt'
-> INTO TABLE employee
-> FIELDS TERMINATED BY ',';
Query OK, 5 rows affected, 10 warnings (0.00 sec)
Records: 5 Deleted: 0 Skipped: 0 Warnings: 10
The records are also not loaded properly because there is a comma in the value of one of the fields.
MariaDB [thegeekstuff]> select * from employee; +-----+--------+---------+--------+ | id | name | dept | salary | +-----+--------+---------+--------+ | 100 | Thomas | Smith | 0 | | 200 | Jason | Bourne | 0 | | 300 | Mayla | Jones | 0 | | 400 | Nisha | Patel | 0 | | 500 | Randy | Lee | 0 | +-----+--------+---------+--------+
Correct File: To solve the above problem, use back-slash (\) in front of the comma in the name field value as shown below.
# cat employee4.txt 100,Thomas\, Smith,Sales,5000 200,Jason\, Bourne,Technology,5500 300,Mayla\, Jones,Technology,7000 400,Nisha\, Patel,Marketing,9500 500,Randy\, Lee,Technology,6000
The following will work this time without any error, as we have \ as escape character.
MariaDB [thegeekstuff]> LOAD DATA INFILE 'employee4.txt'
-> INTO TABLE employee
-> FIELDS TERMINATED BY ',';
MariaDB [thegeekstuff]> select * from employee;
+-----+---------------+------------+--------+
| id | name | dept | salary |
+-----+---------------+------------+--------+
| 100 | Thomas, Smith | Sales | 5000 |
| 200 | Jason, Bourne | Technology | 5500 |
| 300 | Mayla, Jones | Technology | 7000 |
| 400 | Nisha, Patel | Marketing | 9500 |
| 500 | Randy, Lee | Technology | 6000 |
+-----+---------------+------------+--------+
You can also use a different escape character as shown below. In this example, we are using ^ as the escapte character instead of the defualt \.
# cat employee41.txt 100,Thomas^, Smith,Sales,5000 200,Jason^, Bourne,Technology,5500 300,Mayla^, Jones,Technology,7000 400,Nisha^, Patel,Marketing,9500 500,Randy^, Lee,Technology,6000
In this case, use the “ESCAPED BY” option as shown below.
LOAD DATA INFILE 'employee41.txt' INTO TABLE employee FIELDS TERMINATED BY ',' ESCAPED BY '\^'
Please note that some of the characters cannot be used as escape character. For example, if you use % as escape character, you’ll get the following error message.
LOAD DATA INFILE 'employee41.txt' INTO TABLE employee FIELDS TERMINATED BY ',' ESCAPED BY '\%' ERROR 1083 (42000): Field separator argument is not what is expected; check the manual
5. Upload Data Using “Lines terminated by” Option
Instead of having all the records on a separate line, you can also have them on the same line.
In the following example, each and every record is separated by | symbol.
# cat employee5.txt 100,Thomas,Sales,5000|200,Jason,Technology,5500|300,Mayla,Technology,7000|400,Nisha,Marketing,9500|500,Randy,Technology,6000
To upload the above file, use the lines terminated by option as shown below.
LOAD DATA INFILE 'employee5.txt' INTO TABLE employee FIELDS TERMINATED BY ',' LINES TERMINATED BY '|';
The above command will upload the records from employee5.txt as shown below.
MariaDB [thegeekstuff]> select * from employee; +-----+--------+------------+--------+ | id | name | dept | salary | +-----+--------+------------+--------+ | 100 | Thomas | Sales | 5000 | | 200 | Jason | Technology | 5500 | | 300 | Mayla | Technology | 7000 | | 400 | Nisha | Marketing | 9500 | | 500 | Randy | Technology | 6000 | +-----+--------+------------+--------+
The following are couple of points to keep in mind:
- If the input file is coming from a Windows machine, then you may want to use this: LINES TERMINATED BY ‘\r\n’
- If you are using a CSV file to upload data to table, then try one of this 1) LINES TERMINATED BY ‘\r’ 2) LINES TERMINATED BY ‘\r\n’
6. Ignore Line Prefix in Upload File Using “Starting By” Option
You can also have some prefix to the records in your input text file which can be ignored during the upload.
For example, in the following employee6.txt file, for the 1st, 2nd and 5th record, we have “Data:” at the beginning of the line. You can upload only these records by ignoring the line prefix.
# cat employee6.txt Data:100,Thomas,Sales,5000 Data:200,Jason,Technology,5500 300,Mayla,Technology,7000 400,Nisha,Marketing,9500 Data:500,Randy,Technology,6000
To ignore the line prefix and upload these records, (for example: “Data:” in the above file), use the “lines starting by” option as shown below.
LOAD DATA INFILE 'employee6.txt' INTO TABLE employee FIELDS TERMINATED BY ',' LINES STARTING BY 'Data:';
The following is the output of the above command:
Query OK, 3 rows affected (0.00 sec) Records: 3 Deleted: 0 Skipped: 0 Warnings: 0
As you see below, the above command has uploaded only the records that started with the prefix “Data:”. This is helpful to selectively upload only the records that has certain prefix.
MariaDB [thegeekstuff]> select * from employee; +-----+--------+------------+--------+ | id | name | dept | salary | +-----+--------+------------+--------+ | 100 | Thomas | Sales | 5000 | | 200 | Jason | Technology | 5500 | | 500 | Randy | Technology | 6000 | +-----+--------+------------+--------+ 3 rows in set (0.00 sec)
7. Ignore Header Line from the Upload File
In the following input text file, the first line is the header line, which has the name of the columns.
# cat employee7.txt empid,name,department,salary 100,Thomas,Sales,5000 200,Jason,Technology,5500 300,Mayla,Technology,7000 400,Nisha,Marketing,9500 500,Randy,Technology,6000
During upload, we want to ignore the 1st header lien from the employee7.txt file. For this, use the IGNORE 1 lines option as shown below.
LOAD DATA INFILE 'employee7.txt' INTO TABLE employee FIELDS TERMINATED BY ',' IGNORE 1 LINES;
As you see from the following output, even though the input file has 6 lines, it ignored the 1st line (which is header line) and uploaded the remaining 5 lines.
Query OK, 5 rows affected (0.00 sec) Records: 5 Deleted: 0 Skipped: 0 Warnings: 0 MariaDB [thegeekstuff]> select * from employee; +-----+--------+------------+--------+ | id | name | dept | salary | +-----+--------+------------+--------+ | 100 | Thomas | Sales | 5000 | | 200 | Jason | Technology | 5500 | | 300 | Mayla | Technology | 7000 | | 400 | Nisha | Marketing | 9500 | | 500 | Randy | Technology | 6000 | +-----+--------+------------+--------+
8. Upload Only Only Specific Column (and Ignore Others Columns) from Upload File
In the following example, we have values only for three fields. We don’t have department column in this example file.
# cat employee8.txt 100,Thomas,5000 200,Jason,5500 300,Mayla,7000 400,Nisha,9500 500,Randy,6000
To upload values from the input record to a specific column in the table, specify the column names during the load data infile as shown below. The last line in the following command has the column names that should be used to upload the records from the input text file.
LOAD DATA INFILE 'employee8.txt' INTO TABLE employee FIELDS TERMINATED BY ',' (id, name, salary);
Since we didn’t specify the “dept” column in the above command, we’ll see that this column is NULL as shown below.
MariaDB [thegeekstuff]> select * from employee; +-----+--------+------+--------+ | id | name | dept | salary | +-----+--------+------+--------+ | 100 | Thomas | NULL | 5000 | | 200 | Jason | NULL | 5500 | | 300 | Mayla | NULL | 7000 | | 400 | Nisha | NULL | 9500 | | 500 | Randy | NULL | 6000 | +-----+--------+------+--------+
Again, keep in mind that when you don’t specify the list of columns, the command will expect all the columns to be present in the input file.
Also, if you don’t specify the column list in the last line, you’ll get syntax error as shown below.
MariaDB [thegeekstuff]> LOAD DATA INFILE 'employee7.txt'
-> INTO TABLE employee (id, name, salary)
-> FIELDS TERMINATED BY ',';
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'FIELDS TERMINATED BY ','' at line 3
9. Use Variables During Upload with “Set” Option
For this example, let us use the following employee2.txt file.
# cat employee2.txt 100,Thomas,Sales,5000 200,Jason,Technology,5500 300,Mayla,Technology,7000 400,Nisha,Marketing,9500 500,Randy,Technology,6000
In this example, we want to increment the salary by 500 before uploading it to the table. For example, the salary for Thomas (which is the 1st record) is 5000. But, during upload we want to increment it by 500 to 5500 and update this incremented value in the table.
For this, use the SET command and use salary as a variable and do the increment as shown below.
LOAD DATA INFILE 'employee2.txt' INTO TABLE employee FIELDS TERMINATED BY ',' (id, name, dept, @salary) SET salary = @salary+500;
As you see from the following output, the salary column in incremented by 500 for all the records during the data upload from the text file.
MariaDB [thegeekstuff]> select * from employee; +-----+--------+------------+--------+ | id | name | dept | salary | +-----+--------+------------+--------+ | 100 | Thomas | Sales | 5500 | | 200 | Jason | Technology | 6000 | | 300 | Mayla | Technology | 7500 | | 400 | Nisha | Marketing | 10000 | | 500 | Randy | Technology | 6500 | +-----+--------+------------+--------+
10. Write Shell Script to Load data from Text File
Sometimes you may want to upload the data from a text file automatically without having to login to mysql prompt every time.
Let us say we want to put the following command inside a shell script and execute this automatically on thegeekstuff database.
LOAD DATA INFILE 'employee2.txt' INTO TABLE employee FIELDS TERMINATED BY ','
To execute the load from the command line, you’ll use the -e option in the mysql command and execute it from the linux prompt as shown below.
# mysql -e "LOAD DATA INFILE 'employee2.txt' INTO TABLE employee FIELDS TERMINATED BY ','" \ -u root -pMySQLPassword thegeekstuff
Or, you can put that inside a shell script as shown below. In this example, the load-data.sh shell script has the above mysql command.
# cat load-data.sh
mysql -e "\
LOAD DATA INFILE 'employee2.txt'\
INTO TABLE employee \
FIELDS TERMINATED BY ','\
" \
-u root -pMySQLPwd4MDN! test
Give the execute permission to this load-data.sh script, and execute it from the command line, which will load the data automatically to the table. You can also schedule this as cronjob to load the data from the file automatically to the table at a scheduled interval.
# chmod u+x load-data.sh # ./load-data.sh
My name is Ramesh Natarajan. I will be posting instruction guides, how-to, troubleshooting tips and tricks on Linux, database, hardware, security and web. My focus is to write articles that will either teach you or help you resolve a problem. Read more about
Comments on this entry are closed.
Very power full stuff, but this is just over the roof
http://www.ibtimes.co.uk/theres-going-be-mega-supermoon-heres-how-watch-it-live-online-1590327?utm_source=yahoo&utm_medium=referral&utm_campaign=rss&utm_content=/rss/yahoous&yptr=yahoo