Matlab Bulk Insert To SQL Question

Hi,

I have a text file (comma-delimited) and (next-line row delimited) 10 x 116 (116 is the number of columns and were mapped to the corresponding fields/data types in the sql), I am trying to use the Matlab bulk insert, it was ok until I was given a file with NaN instead of Null for numerical data types. How can I handle the NaN values of type float or double from the textfile so that when it is inserted into the database table it is displayed as NULL.

exec(dbconn,['bulk insert ' Table_Name ' from '...
'''temp\FILENAME.txt'' with (fieldterminator = '','', '...
'rowterminator = ''\n'')']);

Thanks.

Answer 1

[Titus Edelhofer]

Hi,

you need to preprocess the data. So, use fopen/fread to read the full file (or large parts, if the file is huge) into memory as one string. Use strrep to replace the NaN by either nothing (empty) or 'null'. Use fopen/fwrite to write a new file. Read this into sql.

Titus

[A David]

Thanks.

Slight change of plan I am now getting a matrix instead of text file. Sometimes the size of the matrix is large over 100K x 116. Here is my current logic.

Steps 1 to 3 are done in in batches of 500 rows per iteration. I am still investigating the optimal way of doing this.

1) Convert the matrix into a string using sprintf.

2) strrep(matrix_to_string, 'NaN', 'NULL'); To replace NaN with Null

3). Write to a text file fprintf statement.

4) Read the text file and perform a Bulk Insert