CODEWITHSUNDEEP
javamysql
dedmar
dedmar

Reputation: 411

dealing with numbers that contain comma in mysql

i am making a program where I would read data from txt files and store them in tables in mysql. In my program I am making the table with the fields that I would like to have and then with the command of "Load Data Infile" I am inserting the values in the table.

The files that I have contains a column (named as litres)
enter image description here
with data that are float numbers such as

0,234

12,234

3,004 etc.

At the beginning of my program when i create the table, the litres field is described as FLOAT. But when i ran my program I get an SQLException : Data truncated for column 'litres' at row 1.

The code for creating the table and inserting the values are the below:

private static String getCreateTable1(Connection con, String tablename) {

        try {
            Class.forName("com.mysql.jdbc.Driver");
            Statement stmt = con.createStatement();
            String createtable = "CREATE TABLE " + tablename
                    + " ( text VARCHAR(255), price INT , day VARCHAR(255), litres FLOAT )";
            System.out.println("Create a new table in the database");
            stmt.executeUpdate(createtable);
        } catch (Exception e) {
            System.out.println(((SQLException) e).getSQLState());
            System.out.println(e.getMessage());
            e.printStackTrace();
        }

        return null;
    }

    private static String importData(Connection con, File txtFile,
            String tablename) {

        try {
            Statement stmt;

            stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
                    ResultSet.CONCUR_UPDATABLE);
            String path = txtFile.getAbsolutePath();
            String importingdata = "LOAD DATA INFILE '"
                    + path.replace('\\', '/')
                    + "' INTO TABLE " + tablename
                    + " FIELDS TERMINATED BY '\t'";
            System.out.println("fill the table");
            stmt.executeUpdate(importingdata);

        } catch (Exception e) {
            System.out.println(((SQLException) e).getSQLState());
            System.out.println(e.getMessage());
            e.printStackTrace();

        }
        return null;
    }

How can i fix it? Could anyone help me? I think that the problem is the comma? How can i make it recognize it?

Upvotes: 1

Views: 1849

Answers (3)

Sergey Kalinichenko
Sergey Kalinichenko

Reputation: 726609

Parsing decimal numbers is locale-specific. You need to change your statement to substitute the comma character ',' with a dot character '.' before giving it to MySQL. One way to do it is adding a cast to your LOAD DATA INFILE statement:

LOAD DATA INFILE 'myfile.txt'
INTO TABLE tablename
FIELDS TERMINATED BY '\t'
(COL1, @litres, COL3, ...) -- Note the @ in front of the float column
SET litres = REPLACE(@litres, ',', '.')

You need to list all the columns that you are importing. The columns which you import "as is" need to be listed without an "at" sign @; the columns where you must replace commas with dots need to be listed with an "at" sign @. For each float column you need to add a replace call on the last line.

Upvotes: 1

Minesh
Minesh

Reputation: 2302

It might be that there are empty records in your file, that is throwing the error message.

See similar issue here: Getting a mysql Error Code: 1265. Data truncated for column when doing a LOAD DATA LOCAL INFILE

Upvotes: 0

Ajinkya
Ajinkya

Reputation: 22710

This exception means values you are inserting into litres column are bigger than the column can accommodate. Looking at the values you mentioned seems like your using liters as a varchar, check if you have the size limit on it.
Update your table to allow desired values.

Upvotes: 0

Related Questions