|
|
Posted by Ted on 10/02/02 11:55
Erland Sommarskog wrote:
> Ted (r.ted.byers@rogers.com) writes:
> > I finally have it working, but with a bit of a kludge. I imported the
> > data file into Open Office Calc and then exporteds it without the
> > quotes. And then I modified the format file so it didn't expect quotes
> > around text.
This is OK for the data files (loaded once only, when the database is
first created) used for initializing key tables used for parameters for
analysis of data retrieved daily.
Unfortunately, the data files retrieved every work day is formatted the
same way.
>
> That's OK for a one-off, but certainly not you don't want to do as a
> matter of routine.
>
> I don't remember, but did you say that your first field is quoted,
> like this:
>
> "field1","field2","field3",4,5
>
Yes, that is correct.
> 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.
>
Guess what I'm trying next? ;-)
This should allow me to process the daily feed without messing with its
format. :-)
Thanks.
> In a way BCP and format files are fascinating. I still discover how to do
> things, that I did not think were possible. But the collorary of that is
> that the solutions are far from obvious.
>
I'll stop learning new things only when I'm dead! Don't you love a
challenge?
Thanks
Ted
Navigation:
[Reply to this message]
|