Matlab Bulk Insert To SQL Question

Question

0 个投票

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.

0 个评论
显示 -2更早的评论隐藏 -2更早的评论

请先登录，再进行评论。

请先登录，再回答此问题。

Follow Question

Answer 1

Titus Edelhofer 2014-1-17

0 个投票

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

1 个评论
显示 -1更早的评论隐藏 -1更早的评论

A David 2014-1-17

编辑：A David 2014-1-17

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

请先登录，再进行评论。

Matlab Bulk Insert To SQL Question

0 个评论
显示 -2更早的评论隐藏 -2更早的评论

回答（1 个）

1 个评论
显示 -1更早的评论隐藏 -1更早的评论

类别

标签

Community Treasure Hunt

Matlab Bulk Insert To SQL Question

0 个评论 显示 -2更早的评论 隐藏 -2更早的评论

回答（1 个）

1 个评论 显示 -1更早的评论 隐藏 -1更早的评论

类别

标签

另请参阅

Community Treasure Hunt

0 个评论
显示 -2更早的评论隐藏 -2更早的评论

1 个评论
显示 -1更早的评论隐藏 -1更早的评论