Mysql Wont Upload Some Symbols in Text
If you have data in a text file, you can easily upload them to one or more than tables in a database.
In MySQL database (or MariaDB), using "load data infile" control, you tin upload data from a text file to tables.
The load data infile command provides several flexible options to load diverse formats of data from text file to tables.
The following load information examples are covered in this tutorial:
- Basic Case to Load Data From Text File
- Upload Data Using "Fields terminated past" Option
- Upload Data Using "Enclosed by" Pick
- Use Escape Grapheme in Text File Data
- Upload Information Using "Lines terminated by" Option
- Ignore Line Prefix in Upload File Using "Starting By" Option
- Ignore Header Line from the Upload File
- Upload Merely Specific Columns (and Ignore Others) from Upload File
- Use Variable during Upload with "Set" Selection
- Write Shell Script to Load information from Text File
1. Basic Case to Load Data From Text File
In the following example, the employee1.txt file has the field values that are divide by tab.
# cat employee1.txt 100 Thomas Sales 5000 200 Jason Technology 5500 300 Mayla Technology 7000 400 Nisha Marketing 9500 500 Randy Engineering science 6000
Past default, the load data infile control uses TAB every bit the default field delimiter.
Outset, become 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 every bit shown below. This command doesn't use whatever actress options.
LOAD Information INFILE 'employee1.txt' INTO TABLE employee;
Note: In the above case, the command assumes that the employee1.txt file located under the database directory. For case, if y'all 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 to a higher place command.
Query OK, v 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 whatsoever error. It also says that full of 5 rows were uploaded to the table. This besides displays the time taken to upload the data from the text file to the table in seconds.
- The 2nd line displays the the full rows uploaded, how many were skipped and how many records displayed warnings during the upload.
After the information is loaded, the following is what nosotros'll run across in the employee table.
MariaDB [thegeekstuff]> select * from employee; +-----+--------+------------+--------+ | id | name | dept | salary | +-----+--------+------------+--------+ | 100 | Thomas | Sales | 5000 | | 200 | Jason | Applied science | 5500 | | 300 | Mayla | Technology | 7000 | | 400 | Nisha | Marketing | 9500 | | 500 | Randy | Technology | 6000 | +-----+--------+------------+--------+
Note: If you want to fill-in and restore your whole MySQL database, utilise mysqldump command.
two. Upload Information Using "Fields terminated by" Option
In the post-obit example, in the input file employee2.txt, the field values are separated by commas.
# true cat employee2.txt 100,Thomas,Sales,5000 200,Jason,Engineering,5500 300,Mayla,Technology,7000 400,Nisha,Marketing,9500 500,Randy,Technology,6000
To upload the above records to the employee tabular array, use the following command.
During the upload, using the "FIELDS TERMINATED BY" option, yous can specify the comma field delimiter equally shown below.
LOAD Data INFILE 'employee2.txt' INTO Table employee FIELDS TERMINATED By ',';
Over again, use this option only when the field values are separated by annihilation other than TAB. If the fields are terminted past colon, you'll utilise the post-obit option in the higher up command:
FIELDS TERMINATED Past ':';
If you are new to MySQL read this: MySQL Tutorial: Install, Create DB and Table, Insert and Select Records
The post-obit are few basic errors that might happen during MySQL upload
Error 1: If the text file is not located under the proper directory, yous might get the post-obit "ERROR 13 (HY000) Can't become stat of (Errcode: two)" fault bulletin.
MariaDB [thegeekstuff]> LOAD Data INFILE 'employee2.txt' INTO TABLE employee; Mistake 13 (HY000): Tin't get stat of '/var/lib/mysql/thegeekstuff/employee2.txt' (Errcode: two)
Also, you tin can specify the full path to the file in the load data infile control as shown below. If you exercise this, make sure the file can exist accessed by mysql. If non, change the ownership to mysql appropriately. If not, you'll go the load information infile permission denied fault message.
MariaDB [thegeekstuff]> LOAD Data INFILE '/data/employee2.txt' INTO TABLE employee;
Fault 2: If you don't specify the correct fields terminated by, then you'll run into some issues in the upload. In this example, but the first field "id" got uploaded. The value of all other fields are Zip. This is considering, the post-obit 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, xx warnings (0.00 sec) Records: 5 Deleted: 0 Skipped: 0 Warnings: 20 MariaDB [thegeekstuff]> select * from employee; +-----+------+------+--------+ | id | name | dept | salary | +-----+------+------+--------+ | 100 | Zip | Cypher | Goose egg | | 200 | NULL | Nothing | Zip | | 300 | Goose egg | NULL | NULL | | 400 | NULL | Zip | Zippo | | 500 | Nil | Nil | NULL | +-----+------+------+--------+
3. Upload Data Using "Enclosed by" Option
In the post-obit example, the input text file has the text field values enclosed by double-quotes. i.east The name and department values take double quotes effectually them.
# cat employee3.txt 100,"Thomas Smith","Sales & Marketing",5000 200,"Jason Bourne","Engineering",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 | Engineering | 6000 | +-----+--------------+-------------------+--------+
Delight note that when yous 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 mistake message:
FIELDS TERMINATED BY ',' FIELDS ENCLOSED By '"';
The above will brandish the following "Mistake 1064 (42000)" error:
Fault 1064 (42000): You have an fault in your SQL syntax; bank check the manual that corresponds to your MariaDB server version for the right syntax to use almost 'FIELDS ENCLOSED BY '"'' at line 4
4. Using Escape Graphic symbol in Text File Information
Let us say you have comma in the value of a particular field.
For instance, in the following example, the 2nd field proper 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 post-obit command, you'll meet that information technology will brandish "10 warnings"
MariaDB [thegeekstuff]> LOAD Information INFILE 'employee4.txt' -> INTO TABLE employee -> FIELDS TERMINATED BY ','; Query OK, five rows affected, 10 warnings (0.00 sec) Records: v Deleted: 0 Skipped: 0 Warnings: 10
The records are also not loaded properly because there is a comma in the value of ane 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 in a higher place problem, use dorsum-slash (\) in front of the comma in the proper name field value as shown beneath.
# true cat employee4.txt 100,Thomas\, Smith,Sales,5000 200,Jason\, Bourne,Technology,5500 300,Mayla\, Jones,Engineering science,7000 400,Nisha\, Patel,Marketing,9500 500,Randy\, Lee,Technology,6000
The following will work this time without any mistake, every bit 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 | Applied science | 7000 | | 400 | Nisha, Patel | Marketing | 9500 | | 500 | Randy, Lee | Engineering science | 6000 | +-----+---------------+------------+--------+
Yous can also employ a dissimilar escape character as shown below. In this example, we are using ^ equally the escapte graphic symbol 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,Engineering science,6000
In this case, utilise the "ESCAPED BY" selection as shown below.
LOAD Data INFILE 'employee41.txt' INTO TABLE employee FIELDS TERMINATED Past ',' ESCAPED Past '\^'
Delight note that some of the characters cannot be used equally escape grapheme. For case, if you use % as escape grapheme, you'll go the following error bulletin.
LOAD Information INFILE 'employee41.txt' INTO Table employee FIELDS TERMINATED By ',' ESCAPED By '\%' Error 1083 (42000): Field separator argument is not what is expected; cheque the manual
five. 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 tape 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,Engineering,6000
To upload the above file, use the lines terminated past selection as shown beneath.
LOAD DATA INFILE 'employee5.txt' INTO Table employee FIELDS TERMINATED BY ',' LINES TERMINATED Past '|';
The above command will upload the records from employee5.txt every bit shown beneath.
MariaDB [thegeekstuff]> select * from employee; +-----+--------+------------+--------+ | id | proper noun | dept | bacon | +-----+--------+------------+--------+ | 100 | Thomas | Sales | 5000 | | 200 | Jason | Technology | 5500 | | 300 | Mayla | Technology | 7000 | | 400 | Nisha | Marketing | 9500 | | 500 | Randy | Engineering science | 6000 | +-----+--------+------------+--------+
The post-obit are couple of points to keep in mind:
- If the input file is coming from a Windows machine, and so y'all may want to utilise this: LINES TERMINATED BY '\r\n'
- If you are using a CSV file to upload information to table, then try one of this 1) LINES TERMINATED BY '\r' 2) LINES TERMINATED Past '\r\n'
half dozen. Ignore Line Prefix in Upload File Using "Starting By" Selection
You tin too take some prefix to the records in your input text file which can exist ignored during the upload.
For instance, in the following employee6.txt file, for the 1st, 2nd and fifth record, we accept "Data:" at the kickoff of the line. Y'all can upload just these records by ignoring the line prefix.
# cat employee6.txt Data:100,Thomas,Sales,5000 Data:200,Jason,Technology,5500 300,Mayla,Engineering,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" pick as shown beneath.
LOAD Data INFILE 'employee6.txt' INTO TABLE employee FIELDS TERMINATED By ',' LINES STARTING Past 'Data:';
The post-obit 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 "Information:". This is helpful to selectively upload just the records that has certain prefix.
MariaDB [thegeekstuff]> select * from employee; +-----+--------+------------+--------+ | id | name | dept | salary | +-----+--------+------------+--------+ | 100 | Thomas | Sales | 5000 | | 200 | Jason | Applied science | 5500 | | 500 | Randy | Engineering science | 6000 | +-----+--------+------------+--------+ 3 rows in set (0.00 sec)
vii. Ignore Header Line from the Upload File
In the following input text file, the first line is the header line, which has the proper noun of the columns.
# true cat employee7.txt empid,name,section,salary 100,Thomas,Sales,5000 200,Jason,Technology,5500 300,Mayla,Technology,7000 400,Nisha,Marketing,9500 500,Randy,Technology,6000
During upload, nosotros desire to ignore the 1st header lien from the employee7.txt file. For this, utilize the IGNORE i lines selection as shown beneath.
LOAD DATA INFILE 'employee7.txt' INTO TABLE employee FIELDS TERMINATED Past ',' IGNORE 1 LINES;
Equally you see from the following output, fifty-fifty 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: v Deleted: 0 Skipped: 0 Warnings: 0 MariaDB [thegeekstuff]> select * from employee; +-----+--------+------------+--------+ | id | name | dept | bacon | +-----+--------+------------+--------+ | 100 | Thomas | Sales | 5000 | | 200 | Jason | Technology | 5500 | | 300 | Mayla | Engineering | 7000 | | 400 | Nisha | Marketing | 9500 | | 500 | Randy | Technology | 6000 | +-----+--------+------------+--------+
viii. Upload Simply Just Specific Column (and Ignore Others Columns) from Upload File
In the post-obit case, nosotros take values simply for three fields. Nosotros 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 tape to a specific cavalcade in the table, specify the column names during the load data infile as shown below. The terminal line in the following command has the column names that should exist 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 nosotros didn't specify the "dept" cavalcade in the to a higher place control, we'll see that this column is Nix every bit shown below.
MariaDB [thegeekstuff]> select * from employee; +-----+--------+------+--------+ | id | proper name | dept | bacon | +-----+--------+------+--------+ | 100 | Thomas | NULL | 5000 | | 200 | Jason | NULL | 5500 | | 300 | Mayla | NULL | 7000 | | 400 | Nisha | Zero | 9500 | | 500 | Randy | Goose egg | 6000 | +-----+--------+------+--------+
Again, proceed in mind that when you don't specify the listing of columns, the command will await all the columns to be present in the input file.
Also, if you lot don't specify the column list in the last line, you lot'll get syntax fault every bit shown below.
MariaDB [thegeekstuff]> LOAD DATA INFILE 'employee7.txt' -> INTO TABLE employee (id, proper noun, salary) -> FIELDS TERMINATED By ','; Mistake 1064 (42000): You lot accept an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to employ near 'FIELDS TERMINATED BY ','' at line 3
9. Apply Variables During Upload with "Set" Selection
For this case, permit us apply 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,Applied science,6000
In this instance, we want to increment the bacon past 500 before uploading information technology to the tabular array. For example, the salary for Thomas (which is the 1st record) is 5000. But, during upload we desire to increase it past 500 to 5500 and update this incremented value in the table.
For this, use the Set up command and utilize salary every bit a variable and do the increment as shown below.
LOAD DATA INFILE 'employee2.txt' INTO TABLE employee FIELDS TERMINATED BY ',' (id, name, dept, @bacon) SET salary = @bacon+500;
As you see from the post-obit output, the bacon column in incremented by 500 for all the records during the information upload from the text file.
MariaDB [thegeekstuff]> select * from employee; +-----+--------+------------+--------+ | id | proper noun | dept | salary | +-----+--------+------------+--------+ | 100 | Thomas | Sales | 5500 | | 200 | Jason | Technology | 6000 | | 300 | Mayla | Technology | 7500 | | 400 | Nisha | Marketing | 10000 | | 500 | Randy | Technology | 6500 | +-----+--------+------------+--------+
ten. Write Shell Script to Load data from Text File
Sometimes you may want to upload the information from a text file automatically without having to login to mysql prompt every time.
Let us say we want to put the post-obit command inside a shell script and execute this automatically on thegeekstuff database.
LOAD Information INFILE 'employee2.txt' INTO TABLE employee FIELDS TERMINATED BY ','
To execute the load from the command line, you'll use the -eastward option in the mysql command and execute it from the linux prompt equally shown below.
# mysql -e "LOAD DATA INFILE 'employee2.txt' INTO TABLE employee FIELDS TERMINATED By ','" \ -u root -pMySQLPassword thegeekstuff
Or, y'all tin put that inside a beat out script every bit shown below. In this instance, the load-information.sh shell script has the above mysql command.
# cat load-data.sh mysql -e "\ LOAD Data INFILE 'employee2.txt'\ INTO TABLE employee \ FIELDS TERMINATED Past ','\ " \ -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 tabular array. You can besides schedule this every bit 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
Source: https://www.thegeekstuff.com/2016/11/mysql-load-data-infile/
0 Response to "Mysql Wont Upload Some Symbols in Text"
Post a Comment