1

I am trying to insert csv file in postgresql database using java

the code is

CopyManager copyManager = new CopyManager((BaseConnection) conn);

            FileReader fileReader = new FileReader(filename);

            copyManager.copyIn("COPY meta.fk_payment_temp(\n"+
                    "settlement_ref_no, order_type, fulfilment_type, seller_sku, wsn, \n" +
"            order_id, order_item_id, order_date, dispatch_date, delivery_date, \n" +
"            cancellation_date, settlement_date, order_status, quantity, order_item_value, \n" +
"            refund, protection_fund, total_marketplace_fee, service_tax, \n" +
"            settlement_value, commission_rate, commission, fee_discount, \n" +
"            cancellation_fee, fixed_fee, emi_fee, total_weight, shipping_fee, \n" +
"            reverse_shipping_fee, shipping_zone, token_of_apology, pick_and_pack_fee, \n" +
"            storage_fee, removal_fee, invoice_id, invoice_date, invoice_amount, \n" +
"            sub_category, total_offer_amount, my_offer_share, flipkart_offer_share)\n" +
"     FROM STDIN with csv header delimiter ','", fileReader );

there is 41 column in csv file and as well as in table

the error detail is:

Where: COPY fk_payment_temp, line 2: "NFT-150331087GN00107XXXXXXX,prepaid,NON-FA,BD46-157,,OD102411813209536003,166288248,"Mar 25, 2015","..."

as we seen error is in "Mar 25, 2015" but the data is

NFT-150331087GN00107XXXXXXX prepaid NON-FA BD46-157 OD102411813209536003 166288248 25-Mar-15 26-Mar-15 27-Mar-15 31-Mar-15 delivered 1 339 339 0 0 -85.26 -10.54 243.2 15 -50.86 0 0 -5 0 0.3 -29.4 0 LOCAL 0 0 0 0 IN27248 26-Mar-15 309 babydoll 0 0 0

how i can solve this ?

  • Um... don't put commas in the date format? If it's not in the source data and it is there when it reaches the database then it's being added in the Java. – Richard Huxton May 28 '15 at 18:59
  • @RichardHuxton how it can add in java ? –  May 28 '15 at 19:08
  • You've shown it yourself in your post. The file has "25-Mar-15" but the error complains about "Mar 25, 2015". The only thing between the file and the database is your java I'm afraid. You can run a copy directly through psql if you don't believe me. Something is parsing the date field and returning it with a different format. That wouldn't be a problem except the format contains a comma and you aren't properly quoting the values, so... you get the error. – Richard Huxton May 28 '15 at 21:47
  • @RichardHuxton I try on psql command but it also give same error so I think my file returning it with a different format . but i dont know how to solve this –  May 29 '15 at 06:57
  • 1
    But you showed what the file contained. Either it contains that or it doesn't. Open up the file in your text editor and check. – Richard Huxton May 29 '15 at 08:08
  • 1
    This [Link][1] solve my problem . http://stackoverflow.com/questions/769621/dealing-with-commas-in-a-csv-file [1]: http://stackoverflow.com/questions/769621/dealing-with-commas-in-a-csv-file –  Jun 04 '15 at 07:11

1 Answers1

0

My experience is that this is almost always caused by malformed csv data. There really is no substitute for knowing the format, how to review material for proper escaping, etc. And CSV is not simple. There are lots of rules regarding things like embedded end of line characters and the like.

A good place to start (if you hadn't already found your answer) would be running the data through a spreadsheet and see what seems to end up on the wrong columns.

Chris Travers
  • 25,424
  • 6
  • 65
  • 182