Is it possible to upload only the first 10 rows of a CSV file using MySQL - LOAD DATA LOCAL INFILE?

Question

Is it possible to only upload the first 10 rows of a CSV file using MySQL - LOAD DATA LOCAL INFILE? I tried using LIMIT but it’s not working.

Here is my PHP script:

    $sql = "LOAD DATA LOCAL INFILE '".@mysql_escape_string($this->file_name).
         "' INTO TABLE branches
              FIELDS TERMINATED BY ',' ENCLOSED BY '\"'
              LINES TERMINATED BY '\r\n'
              IGNORE 1 LINES                  
              (
                Name,
                Address_Line_1, 
                City, 
                State, 
                Country_Code,
                Postal_Code, 
                Main_Phone, 
                Google_Places_Link, 
                Custom_Directory_1, 
                Custom_Directory_2, 
                Custom_Directory_3,  
                business_id,
                username
              ) SET branches.business_id=(".$this->business_id."), branches.username=('".$this->username."') LIMIT 0,10  
              ";

Answer 1

The solution below provides a LIMIT-style behavior for LOAD DATA INFILE. Use IGNORE n LINES and set to the total rows - whatever small number you want to test.

This does not save on file scans, isn't elegant, and works backwards (literally). But for tables of moderate length less than a million rows, for me it's useful for testing. (It may work for much larger tables, but I have not had occasion to test that.)

For example, the last 20 rows of a large table with 101,773 rows would be

LOAD DATA INFILE 'c:/temp/input/datafile.csv'
    -- IGNORE
    INTO TABLE `mytable`
    
    CHARACTER SET utf8mb4
    FIELDS  TERMINATED BY '\t' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '\b'
    LINES TERMINATED BY '\r\n'
    IGNORE 101753 LINES