This folder is not configurable or determined by MySQL. The file will be uploaded into the database server operating system’s temporary folder e.g., C:\windows\temp on Windows or /tmp on Linux. When you use the LOCAL option in the LOAD DATA INFILE, the client program reads the file on the client and sends it to the MySQL server. It is possible to import data from client (local computer) to a remote MySQL database server using the LOAD DATA INFILE statement. When importing data into the discounts table, we have to transform it into MySQL date format by using str_to_date() function as follows: LOAD DATA INFILE 'c:/tmp/discounts_2.csv' INTO TABLE discountsįIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n' IGNORE 1 expired_date = '%m/%d/%Y') Code language: SQL (Structured Query Language) ( sql ) Importing file from client to a remote MySQL database server Suppose the expired date column in the discount_2.csv file is in mm/dd/yyyy format. 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. SELECT * FROM discounts Transforming data while importing Now, we can check the discounts table to see whether the data is imported. īecause the file has the first line that contains the column headings, which should not be imported into the table, therefore we ignore it by specifying IGNORE 1 ROWS option. The field of the file is terminated by a comma indicated by FIELD TERMINATED BY ',' and enclosed by double quotation marks specified by ENCLOSED BY '" ‘.Įach line of the CSV file is terminated by a newline character indicated by LINES TERMINATED BY '\n'. LOAD DATA INFILE 'c:/tmp/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 following statement imports data from the c:\tmp\discounts.csv file into the discounts table. The following discounts.csv file contains the first line as column headings and other three lines of data. ) Code language: SQL (Structured Query Language) ( sql ) We use CREATE TABLE statement to create the discounts table as follows: CREATE TABLE discounts ( Suppose we have a table named discounts with the following structure: The account, which connects to the MySQL database server, has FILE and INSERT privileges.A CSV file with data that matches with the number of columns of the table and the type of data in each column.A database table to which the data from the file will be imported.The LOAD DATA INFILE statement allows you to read data from a text file and import the file’s data into a database table very fast.īefore importing the file, you need to prepare the following: This tutorial shows you how to use the LOAD DATA INFILE statement to import CSV file into MySQL table.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |