How do I load my complete csv file into a table?

Question

I am trying to upload a .csv file into a mysql table, but currently it will only upload 1001 lines of movies in the movies table when there is a couple more thousand lines that it leaves out

I tried changing the int to a Bigint to give it more room because the ints were only giving a max of 4 characters, the biggest was 7220 when it goes up to 6 or 7 charcters in the csv file for example:

Lines included =

6116,Spice World,4.7,123
5902,A Bridge Too Far,6.9,207
Lines not included:
190955,Blood Ties,6,171
206647,Spectre,6.3,4466
13056,Punisher: War Zone,5.6,294

LAST line loaded:
7220,The Punisher,6.1,766 (LINE 1488)
last line not loaded:
7278,Meet the Spartans,3.7,359 (LINE 1608)

I notice it hits a point (7220) than doesnt bring in anything else higher but includes everything 7220 and below.

 statement.executeUpdate("CREATE TABLE Movies("
                    + "id BIGINT, "
                    + "title VARCHAR(100), "
                    + "vote_average VARCHAR(100), "
                    + "vote_count VARCHAR(100), "
                    + "PRIMARY KEY(id))" );

  String loadQuery = "LOAD DATA LOCAL INFILE '" + "movies.csv"
                    + "' INTO TABLE Movies FIELDS TERMINATED BY ',' ENCLOSED BY '\"'\n" +
                    "LINES TERMINATED BY '\\r\\n'\n" +
                    "IGNORE 1 LINES";
            stmt.execute(loadQuery);//executing the query

UPDATE: REMEMBER TO SELECT "NO LIMIT" WITHIN MYSQL, that explains why select was only returning 1000 lines.

Answer 1

I didn't run your code but, Try this:

CREATE TABLE discounts(
id INT NOT NULL AUTO_INCREMENT,
title VARCHAR(255) NOT NULL,
expired_date DATE NOT NULL,
amount DECIMAL(10 , 2 ) NULL,
PRIMARY KEY (id)
);

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

discount.csv

The following statement imports data from the c:\tmp\discounts.csv file into the discounts table.

LOAD DATA INFILE 'c:/tmp/discounts.csv' 
INTO TABLE discounts 
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;

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 '"'.

Each line of the csv file is terminated by a newline character indicated by LINES TERMINATED BY '\n' .

Because 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.

Now, we can check the discounts table to see whether the data is imported.

SELECT * FROM discounts;

table of discount

Hope this was helpful.