Removing the First Row in a Csv File Upload to Mysql Database

This tutorial shows you how to use theLOAD DATA INFILE statement to import CSV file into MySQL table.

The LOAD DATA INFILE statement allows y'all to read data from a text file and import the file's information into a database tabular array very fast.

Before importing the file, you demand to fix the following:

• A database table to which the data from the file will be imported.
• A CSV file with information that matches with the number of columns of the table and the type of data in each column.
• The account, which connects to the MySQL database server, has FILE and INSERT privileges.

Suppose we have a table named discounts with the following construction:

We use CREATE Table statement to create the discounts table as follows:

            
                              CREATE                TABLE                discounts (                id                INT                NOT                Zero                AUTO_INCREMENT,     championship                VARCHAR(255)                NOT                NULL,     expired_date                DATE                NOT                Zilch,     amount                DECIMAL(10                ,                ii                )                Nada,     PRIMARY                KEY                (id) );            
                          Lawmaking language:              SQL (Structured Query Linguistic communication)              (              sql              )                      

The following discounts.csv file contains the first line equally cavalcade headings and other three lines of data.

The post-obit statement imports data from thec:\tmp\discounts.csv file into the discounts tabular array.

            
                              LOAD                Information                INFILE                'c:/tmp/discounts.csv'                INTO                Table                discounts                FIELDS                TERMINATED                BY                ','                ENCLOSED                By                '"'                LINES                TERMINATED                Past                '\north'                IGNORE                1                ROWS;            
                          Code language:              SQL (Structured Query Language)              (              sql              )                      

The field of the file is terminated by a comma indicated past FIELD TERMINATED By ',' and enclosed by double quotation marks specified by ENCLOSED BY '" '.

Each line of the CSV file is terminated past a newline grapheme indicated by LINES TERMINATED By '\n' .

Because the file has the offset line that contains the column headings, which should non be imported into the table, therefore we ignore it by specifying IGNORE 1 ROWS option.

Now, we can bank check the discounts table to run across whether the data is imported.

            
              SELECT * FROM discounts;            
          

Transforming data while importing

Sometimes the format of the data does not match the target columns in the table. In simple cases, yous tin transform information technology past using the Prepare clause in the LOAD Data INFILE statement.

Suppose the expired appointment column in the discount_2.csv file is in mm/dd/yyyy format.

When importing data into the discounts tabular array, we have to transform information technology into MySQL date format by using str_to_date() function equally follows:

            
                              LOAD                Information                INFILE                'c:/tmp/discounts_2.csv'                INTO                Tabular array                discounts                FIELDS                TERMINATED                By                ','                ENCLOSED                BY                '"'                LINES                TERMINATED                By                '\due north'                IGNORE                1                ROWS                (title,@expired_date,corporeality)                Prepare                expired_date =                STR_TO_DATE(@expired_date,                '%m/%d/%Y');            
                          Code language:              SQL (Structured Query Language)              (              sql              )                      

Importing file from client to a remote MySQL database server

It is possible to import data from client (local calculator) to a remote MySQL database server using the LOAD DATA INFILE statement.

When you lot employ theLOCAL pick in theLOAD Data INFILE , the client programme reads the file on the client and sends information technology to the MySQL server. The file volition exist uploaded into the database server operating organization's temporary binder due east.g., C:\windows\temp on Windows or /tmp on Linux. This folder is non configurable or determined by MySQL.

Allow's take a look at the post-obit example:

            
                              LOAD                Data                LOCAL                INFILE                'c:/tmp/discounts.csv'                INTO                Tabular array                discounts                FIELDS                TERMINATED                BY                ','                ENCLOSED                Past                '"'                LINES                TERMINATED                BY                '\northward'                IGNORE                1                ROWS;            
                          Code language:              SQL (Structured Query Linguistic communication)              (              sql              )                      

The but difference is the LOCAL option in the statement. If y'all load a big CSV file, you lot will come across that with theLOCAL selection, it volition exist a little fleck slower to load the file because information technology takes fourth dimension to transfer the file to the database server.

The account that connects to MySQL server doesn't need to have the FILE privilege to import the file when yous utilize the LOCAL option.

Importing the file from client to a remote database server using LOAD Data LOCAL has some security problems that you should be aware of to avert potential security risks.

Importing CSV file using MySQL Workbench

MySQL workbench provides a tool to import data into a tabular array. It allows you to edit data before making changes.

The following are steps that you want to import data into a table:

Open table to which the information is loaded.

Click Import button, choose a CSV file and click Open button

Review the information, click Apply button.

MySQL workbench will display a dialog  "Apply SQL Script to Database", click Apply button to insert data into the table.

We have shown you how to import CSV into MySQL table using LOAD DATA LOCAL and using MySQL Workbench. With these techniques, you can load information from other text file formats such equally tab-delimited.

Was this tutorial helpful?

comeauseturs.blogspot.com

Source: https://www.mysqltutorial.org/import-csv-file-mysql-table/

Share this post