When importing data into the discounts table, you can transform it into MySQL date format by using STR_TO_DATE() function:įirst, truncate the discounts table: TRUNCATE TABLE discounts Suppose the expired date column in the discount2.csv file is in the mm/dd/yyyy format: id,title,expired date,amountġ,Spring Break, 01/ 01/ 2014, 20 2,Back to School, 09/ 01/ 2014, 25 3,Summer Holiday, 08/ 25/ 2014, 10 Code language: PHP ( php ) In simple cases, you can transform it by using the SET clause in the LOAD DATA INFILE statement. Sometimes the format of the data does not match the target columns in the table. Notice that if you don’t place the file in the directory specified by the secure_file_priv variable, you’ll get the following error: ERROR 1290 (HY000): The MySQL server is running with the -secure-file-priv option so it cannot execute this statement Code language: JavaScript ( javascript ) Transforming data while importing | 3 | Summer Holiday | 2014 -08 -25 | 10.00 |ģ rows in set (0.00 sec) Code language: JavaScript ( javascript ) This is useful when the first row contains headers and should not be imported as data.įinally, retrieve the data from the discounts table to verify the import: SELECT * FROM discounts IGNORE 1 ROWS: Instructs the statement to ignore the first row in the input file.This indicates the end of a record (row) in the CSV file. LINES TERMINATED BY '\n': Specifies that each line in the input file is terminated by a newline character ( \n).This is common in CSV files to handle cases where a field may contain the delimiter. ENCLOSED BY '"': Specifies that the fields in the input file are enclosed by double quotation marks ( ").This indicates that the file is a CSV (Comma-Separated Values) file. FIELDS TERMINATED BY ',': Specifies that the fields in the input file are separated (terminated) by a comma ( ,).INTO TABLE discounts: Specifies the target table ( discounts) where you want to load the data.LOAD DATA INFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/discounts.csv': Specifies the path to the input file ( discounts.csv) that contains the data to be loaded into the table.The output indicates that the statement has loaded the file with three rows successfully. Records: 3 Deleted: 0 Skipped: 0 Warnings: 0 Code language: CSS ( css ) Sixth, import data from the discounts.csv file into the discounts table by executing the following statement: LOAD DATA INFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/discounts.csv' INTO TABLE discountsįIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n' IGNORE 1 ROWS Code language: SQL (Structured Query Language) ( sql ) The secure_file_priv option indicates the directory where you are allowed to store the input file and execute it with the LOAD DATA INFILE statement.įifth, copy the discounts.csv file to the directory specified by the secure_file_priv option. | C:\ProgramData\MySQL\MySQL Server 8.0\Uploads\ |ġ row in set (0.00 sec) Code language: JavaScript ( javascript ) Third, create a discounts table: CREATE TABLE discounts (įourth, show the value of the variable: SELECT Code language: CSS ( css ) Second, change the current database to classicmodels: use classicmodels Code language: PHP ( php ) To import the discounts.csv file into the discounts table, you follow these steps:įirst, open the Command Prompt on Windows or the Terminal on Unix-like systems and connect to the MySQL server: mysql -u root -p Suppose you have a table called discounts with the following structure:Īnd the following discounts.csv file contains the first line as column headings and the other three lines of data: id,title,expired date,amountġ,Spring Break, 2014 -01 -01, 20 2,Back to School, 2014 -09 -01, 25 3,Summer Holiday, 2014 -08 -25, 10 Code language: PHP ( php ) A MySQL user account that has FILE and INSERT privileges. A CSV file with data that matches the number of columns of the table and the type of data in each column.A table that you want to import data into.The LOAD DATA INFILE statement allows you to read data from a CSV file in a specified directory on the MySQL server and import its contents to into a table.īefore importing the file, you need to prepare the following: 1) Importing a CSV file on the MySQL server into a table using LOAD DATA INFILE statement Summary: in this tutorial, you will learn how to import a CSV file into a MySQL table using the LOAD DATA INFILE statement and MySQL Workbench.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |