Can't Bulk Load CSV with blank fields
Posted by: Michael Cooke-Russell
Date: February 07, 2016 05:41PM

In attempting to use the MySQLBulkloader in a Visual Studio program I have come across a problem when attempting to load (large CSV) files that use blank fields (,,) to indicate Null values. In would prefer not to have to preprocess these large text files to inject the word NULL where appropriate just to accomplish what should be a basic task.

I understand that converting a blank field to 0 or "" is not a bug in the LOAD DATA INFILE command but nevertheless it is not what I need it to do and there is no simple "switch" to make it behave the way I need. I was wanting to use the columns and SET feature of the LOAD DATA INFILE to accomplish the blank to Null conversion on the fly but have hit the following problems:

1) For the MySQLBulkloader class the Columns and Expressions properties are both read only - so I can't use that. Actually I'm not really sure what the point of these properties is if they are read only.

2) I've also tried to manually code the full LOAD DATA INFILE command into the MySQLCommand using the Query:

LOAD DATA INFILE 'myfile.csv' INTO TABLE MyTable
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(@Var1,@Var2)
SET Column1 = NULLIF(@Var1,''), Column2 = NULLIF(@Var2,'')

But unfortunately the MySQLCommand class realises that @Var1 and @Var2 are parameters and does not pass the SQL through literally to the server. Obviously I don't want to "set" there parameters as that would change the logic of the SQL.

So how can I accomplish my task?

Options: ReplyQuote


Subject
Written By
Posted
Can't Bulk Load CSV with blank fields
February 07, 2016 05:41PM


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.