|
|
Posted by Erland Sommarskog on 10/02/59 11:55
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.
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
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.
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.
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Navigation:
[Reply to this message]
|