CSV import error invalid syntax type

#1

Trying to import a csv file and inserting the records into a database, I am getting this error:

  1. code: “22P02”
  2. file: “/var/www/html/dmxConnectLib/lib/db/Connection.php”
  3. line: 114
  4. message: "SQLSTATE[22P02]: Invalid text representation: 7 ERROR: invalid input syntax for type numeric: “242,43"”

I changed the field type of the destination field in the database to “Decimal”, where it was an integer before, but it gets changed back every time after applying the database changes. Creation of new fields of Decimal type are also saved as Integer type fields after applying the db changes.

#2

You are trying to insert a number with comma separator, while the dot is expected as decimal separator.

You should convert it first

#3

Did that manually in the csv and tried again, got the same error. Could this be the cause?

Also, when I tried to Allow null for another field and applied the database changes, the changes were successfull according to the Wappler dialog, but afterwards the settings are back to the old in the Database manager…

Database manager not updating field types and "Allow null' after applying changes
#4

Bypassed this temporarily by adding static data for the database fields I want to “Allow null” but don’t get changed. There are no errors returned by XHR, but the data are not inserted in the database, here’s the XHR output:

    {,…}
    csvImport1: [,…]
    0: {product_name: "Product1", stock_quantity: "4", product_price: "242.45", sales_price: "99.99"}
    1: {product_name: "Product2", stock_quantity: "10", product_price: "143.56", sales_price: "79.99"}
    2: {product_name: "Product3", stock_quantity: "8", product_price: "173.42", sales_price: "69.99"}
    3: {product_name: "Product4", stock_quantity: "7", product_price: "23.54", sales_price: "19.99"}
    4: {product_name: "Product5", stock_quantity: "5", product_price: "25.86", sales_price: "9.99"}
    record_repeat: [{product_insert: {identity: null, affected: 1}}, {product_insert: {identity: null, affected: 1}},…]
    0: {product_insert: {identity: null, affected: 1}}
    product_insert: {identity: null, affected: 1}
    1: {product_insert: {identity: null, affected: 1}}
    product_insert: {identity: null, affected: 1}
    2: {product_insert: {identity: null, affected: 1}}
    product_insert: {identity: null, affected: 1}
    3: {product_insert: {identity: null, affected: 1}}
    product_insert: {identity: null, affected: 1}
    4: {product_insert: {identity: null, affected: 1}}
    product_insert: {identity: null, affected: 1}

    upload_file: {name: "products.csv", type: "application/vnd.ms-excel", tmp_name: "/tmp/phppgLCKf", error: 0,…}
    error: 0
    isFile: true
    name: "products_15.csv"
    path: "/feeds/products_15.csv"
    processed: true
    size: 346
    tmp_name: "/tmp/phppgLCKf"
    type: "application/vnd.ms-excel"
    url: "/feeds/products_15.csv"

Seems to be successfully imported. Any clue why the database records are not added?

#5

It’s not easy to answer “why no records are added” without seeing your database, field types, your server action steps, your import step setup etc…

#6

I understand, I will send you a DM with details. Thanks.