Re: Unable to load CSV
Posted by: Peter Brawley
Date: May 17, 2022 11:52AM

Load Data is finicky, to put it mildly. Best approach I know is to leave nothing to chance, leave no ambiguities, Debugging Load Data gets more tiresome as the column count grows. With your code ...

- null for Load Data is \N, not \\N

- optionally enclosed by... causes misreads

- lines terminated by ... seems to be necessary

Quoting char values in an input file with just your first line of data ...

'-1','322d8ac7-4e00-4304','CONFERENCE','74698900000','webdriver.user',1879803950,\N,\N,'REGISTRATION','PREDEFINED','webdriver.user-1@test.com','NY',\N,1,0,\N,1.64067E+12,1.64067E+12,\N,\N,\N,\N,1.64067E+12,\N,'SHORT_ANSWER','city',3053931,\N

... and this cmd ...

LOAD DATA INFILE 'c:/in/in.csv'
IGNORE
INTO TABLE QUESTIONNAIRESUBMISSION_FACT_MIGRATION
FIELDS TERMINATED BY ','
lines terminated by '\n'
IGNORE 1 LINES
(SESSIONKEY,REFERENCEKEY,REFERENCETYPE,USERKEY,REPORTINGUSERKEY,QUESTIONNAIREKEY,QUESTIONNAIRETITLE,QUESTIONNAIREVERSION,QUESTIONNAIRETYPEKEY,QUESTIONNAIRESUBTYPE,EMAIL,PRODUCTKEY,PLATFORMKEY,NUMQUESTIONASKED,NUMQUESTIONANSWERED,NUMCORRECTANSWER,DBTIMESTAMP,TRANSACTIONTIME,REPORTINGUSRKEY,ANSWERKEY,ANSWERSORTORDER,ANSWERTEXT,ANSWERTIME,ISCORRECT,QUESTIONTYPE);

.. gets rid of your error but elicits "Row 1 was truncated; it contained more data than there were input columns", which indicates that MySQL is still misreading something in that row. The only way I know to debug that is to make a series of test tables and matching input files till you identify problem input values.

Note too that IGNORE can't work without a primary key.

HTH.

Options: ReplyQuote


Subject
Written By
Posted
May 16, 2022 04:35AM
May 16, 2022 08:52AM
May 16, 2022 10:49PM
Re: Unable to load CSV
May 17, 2022 11:52AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.