dealing with numbers that contain comma in mysql

Question

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?

What is the MySQL datatype for column "litres" ? – Jannis Alexakis Jun 17 '13 at 09:47 — Jannis Alexakis, Jun 17 '13 at 09:47

score 1 · Accepted Answer · answered Jun 17 '13 at 10:04

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.

OP mentioned in comment *its not separated numbers with commas. Is float numbers.* — Ajinkya, Jun 17 '13 at 10:08
@Karna That's precisely what this solution expects: fields are delimited by tabs; commas remain a part of the float, enabling the call of `REPLACE` to succeed. — Sergey Kalinichenko, Jun 17 '13 at 10:09

Ajinkya · Answer 2 · 2013-06-17T09:48:25.760

0

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.

edited Jun 17 '13 at 09:48

answered Jun 17 '13 at 09:42

Ajinkya

22,324
33
110
161

I ve also put double in the field of litres but i get exactly the same exception. – dedmar Jun 17 '13 at 09:48
@dedmar: Can you add the `litres`column definition ? – Ajinkya Jun 17 '13 at 09:49
No, i am using litres as float at the code that i am showing you above. – dedmar Jun 17 '13 at 09:49
Field: litres Type:float NULL:YES DEFAULT:Null – dedmar Jun 17 '13 at 09:55
@dedmar: Not sure if float allow `,` seperated numbers,you should use `varchar` instead – Ajinkya Jun 17 '13 at 10:00
its not separated numbers with commas. Is float numbers. I would add an image to be more clarified. – dedmar Jun 17 '13 at 10:04
@dedmar: Use dasblinkenlight solution. and FYI `0,234` `12,234` and `3,004` are not float numbers. – Ajinkya Jun 17 '13 at 10:11

score 0 · Answer 3 · edited May 23 '17 at 12:13

0

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

edited May 23 '17 at 12:13

Community

1
1

answered Jun 17 '13 at 09:58

Minesh

2,284
1
14
22

dealing with numbers that contain comma in mysql

3 Answers3