Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Not Able to load the binary data using the LOAD file- having the whitespace after quoted data ERROR #1602

Open
4 tasks
mrmayuur opened this issue Aug 8, 2024 · 0 comments

Comments

@mrmayuur
Copy link

mrmayuur commented Aug 8, 2024

We are migrating the Oracle to PostgreSQL and we are facing the issue for the pgloader where i need to insert the compressed blob data in the XML_BILL_OBJ coloumn where it is tagged by and , I have replaced the encoding lang as well but still getting the error.
I have even tried of changing the datatype which i though may be the issue but it didnt worked.

1. show what you did,
CODE im working on -

LOAD CSV
FROM 'file Path.dat'
INTO postgresql://dbconnection detail
TARGET TABLE tablename
(
RAMP_II,
BILL_ISSUE_DT,
BILL_SEQ_NBR,
PRINT_LANG_CD,
ARCV_IND,
BILL_LOAD_DT,
PE,
ACCT_TYPE,
ACCT_TYPE_2,
ACCT_TYPE_3,
SAMPLE_CD,
PRESENT_MEDIA_CD,
ADJ_IND,
BILL_TEL_NBR,
OBLG_ID,
CONF_DT,
RAMP_BILL_ISSUE_DT,
PYMT_METH_CD,
TOT_BILL_AMT,
TOT_CURR_REV,
TOT_PAST_DUE_AMT,
LEC_ID,
GEOCODE,
XML_BILL_OBJ
)
WITH
fields terminated by '|',
fields optionally enclosed by '"',
fields escaped by '"'
--)
--fields optionally enclosed by '"',
--fields escaped by '"'

SET client_encoding to 'UTF-8'
;

2. show the result you got,

2.1- ERROR non whitespace after quoted data #<CSV-READER LINE-IDX:1 CHARACTER-LINE-IDX:4 CHARACTER-IDX:461

  • these error is popping when im inserting the compressed blob data into the xml_billl_obj column.

2.2 - ESRAP-PARSE-ERROR: At

fields terminated by '|',
fields optionally enclosed by '"' for column XML_BILL_OBJ
^ (Line 33, Column 33, Position 657)

In context COMMAND:

-For the ( fields terminated by '|', fields optionally enclosed by '"', fields escaped by '") when i try to assign the enclosed by field to only xml_bill_obj column it gives me syntax error

  1. explain how the result is not what you expected.

pgloader --version

pgloader version "3.6.7~devel"
compiled with SBCL 2.3.2

  • did you search for other similar issues?
    I have searched all over te internet but not able to find any solution tried many ways to sort it out.

  • how can I reproduce the bug?
    --
    find the above load file -

  • pgloader output you obtain

PASTE HERE THE OUTPUT OF THE PGLOADER COMMAND

2024-08-08T10:16:09.011000Z LOG pgloader version "3.6.7devel"
2024-08-08T10:16:09.336008Z ERROR PostgreSQL [""dps"."bills_bkp""] Database error 22P02: invalid input syntax for type bytea
CONTEXT: COPY bills_bkp, line 1, column xml_bill_obj: ""x<9c><85>ZÝr<9b>È^R¾OÕy<87><89>vkë<9c>*[FÈNì¬C<8a>?Ûl^PÒ^BN¢ÜP^H<8d>-Ê^Rh^AÙÑy<8c>óħ{^FÐ<80>ÀÎE"¦ûë..."
2024-08-08T10:16:09.384010Z ERROR PostgreSQL [""dps"."bills_bkp""] Database error 22021: invalid byte sequence for encoding "UTF8": 0x00
CONTEXT: COPY bills_bkp, line 1
2024-08-08T10:16:09.432012Z ERROR PostgreSQL [""dps"."bills_bkp""] Database error 22021: invalid byte sequence for encoding "UTF8": 0x00
CONTEXT: COPY bills_bkp, line 1
2024-08-08T10:16:09.480013Z ERROR PostgreSQL [""dps"."bills_bkp""] Database error 22021: invalid byte sequence for encoding "UTF8": 0x00
CONTEXT: COPY bills_bkp, line 2
2024-08-08T10:16:09.536014Z ERROR PostgreSQL [""dps"."bills_bkp""] Database error 22007: invalid input syntax for type date: " X§9]
"¿<97>ø#権î^F<9e>îØNóýa$^?<84>¯×ÿ<8c>Ë=^N"ÚÂmí^G<9b>Óª,øþ<83>4ºúx!]<8e>¡x½/ám¦.!l^L^Z)<95>#^HÈ<92>Ò^K^B<8f><9e>^Rmú<83>à®mܱ?פ±¢«îÔ&à^]^SbÙ<84>iÚ%d"
CONTEXT: COPY bills_bkp, line 1, column bill_issue_dt: " X§9]
"¿<97>ø#権î^F<9e>îØNóýa$^?<84>¯×ÿ<8c>Ë=^N"ÚÂmí^G<9b>Óª,øþ<83>4ºúx!]<8e>¡x½/ám¦.!..."
2024-08-08T10:16:09.539014Z ERROR PostgreSQL [""dps"."bills_bkp""] Database error 22021: invalid byte sequence for encoding "UTF8": 0x00
CONTEXT: COPY bills_bkp, line 3
2024-08-08T10:16:09.546014Z LOG report summary reset
table name errors rows bytes total time


              fetch          0          0                     0.003s

  "dps"."bills_bkp"         12          3     4.9 kB          0.312s

    Files Processed          0          1                     0.023s

COPY Threads Completion 0 2 0.313s


  Total import time         12          3     4.9 kB          0.336s
  • data that is being loaded, if relevant
PASTE HERE THE DATA THAT HAS BEEN LOADED

samplrec.txt

The samplrec.dat is the input file which we have changed it to txt file.

Please let know what should be done here, Thank you in advance.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

No branches or pull requests

1 participant