|
|
Posted by Ted on 10/02/20 11:55
Erland Sommarskog wrote:
> I don't remember, but did you say that your first field is quoted,
> like this:
>
> "field1","field2","field3",4,5
>
> Then you need a little trick to handle the first quote:
>
> 8.0
> 6
> 1 SQLCHAR 0 0 "\"" 0 "" ""
> 2 SQLCHAR 0 0 "\","\" 1 col1 ""
> 3 SQLCHAR 0 0 "\","\" 2 col2 ""
> 4 SQLCHAR 0 0 "\"," 3 col3 ""
> 5 SQLCHAR 0 0 "," 4 col4 ""
> 6 SQLCHAR 0 0 "\r\n" 5 col5 ""
>
> The trick is that you say that you say that there is an empty field before
> the first quote. Then you specify 0 for the database column, meaning that
> you don't import it.
>
This works great, but there is a fly in the ointment.
Suppose we have records like:
"field1","field2","field3",4,5
But for which field2 can be null. E.G.
"value11","value12","value13",1,2
"value21",,"value23",12,22
"value31","value32","value33",13,23
"value41",,"value43",14,24
One can obtain something like this, e.g., by exporting data from a
spreadsheet.
I don't see a way for a format file to handle this except possibly by
using only a comma or tab or end of record string as the delimiter or
field terminator, leaving the quotes included with the text in the
field, and then use a combination of the functions SUBSTRING and LEN to
remove the leading and trailing quote character after the data has been
loaded.
Any ideas for alternatives?
Cheers,
Ted
Navigation:
[Reply to this message]
|