 |
|
|
|
Next: SQL 2000 Corrupt LDF File
|
| Author |
Message |
External
Since: Aug 04, 2008
Posts: 1
|
(Msg. 1) Posted: Mon Aug 04, 2008 9:35 am
Post subject: Bulk Import w/Format File and Text Fields - Why is it not working?
Archived from groups: microsoft>public>sqlserver>tools (more info?)
|
|
|
Here's what my data looks like:
"12312331 ","DOE, JOHN","3423423",0,1900-01-01
00:00:00,"","@@
","","JOHN DOE PROCESSING PLANT"
Here's my format file:
7.0
10
1 SQLCHAR 0 0 "\"" 0 ""
2 SQLCHAR 0 250 "\",\"" 1 "c1"
3 SQLCHAR 0 250 "\",\"" 2 "c2"
4 SQLCHAR 0 250 "\"," 3 "c3"
5 SQLCHAR 0 250 "," 4 "c4"
6 SQLCHAR 0 250 ",\"" 5 "c5"
7 SQLCHAR 0 250 "\",\"" 6 "c6"
8 SQLCHAR 0 250 "\",\"" 7 "c7"
9 SQLCHAR 0 250 "\",\"" 8 "c8"
10 SQLCHAR 0 250 "\"\r\n" 9 "c9"
I even tried simplying it into the simplist form possible because I
kept getting an error...
7.0
9
1 SQLCHAR 0 250 "," 1 "c1"
2 SQLCHAR 0 250 "," 2 "c2"
3 SQLCHAR 0 250 "," 3 "c3"
4 SQLCHAR 0 250 "," 4 "c4"
5 SQLCHAR 0 250 "," 5 "c5"
6 SQLCHAR 0 250 "," 6 "c6"
7 SQLCHAR 0 250 "," 7 "c7"
8 SQLCHAR 0 250 "," 8 "c8"
9 SQLCHAR 0 250 "\"\r\n" 9 "c9"
Which should just remove the last quote in the final field. No matter
what I try, I get the same error each time:
Could not bulk insert. Invalid column number in format file 'D:\UPSDATA
\DBUS1000\LeadImprovement\FORMAT3.fmt'.
However this works:
7.0
9
1 SQLCHAR 0 250 "," 1 "c1"
2 SQLCHAR 0 250 "," 2 "c2"
3 SQLCHAR 0 250 "," 3 "c3"
4 SQLCHAR 0 250 "," 4 "c4"
5 SQLCHAR 0 250 "," 5 "c5"
6 SQLCHAR 0 250 "," 6 "c6"
7 SQLCHAR 0 250 "," 7 "c7"
8 SQLCHAR 0 250 "," 8 "c8"
9 SQLCHAR 0 250 "\r\n" 9 "c9"
It seems it dislikes the escaped " as a field delimiter no matter
what.... |
|
| Back to top |
|
 | |
External
Since: May 30, 2004
Posts: 1649
|
(Msg. 2) Posted: Mon Aug 04, 2008 2:45 pm
Post subject: Re: Bulk Import w/Format File and Text Fields - Why is it not working? [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
Brad ( ) writes:
> Here's what my data looks like:
>
> "12312331 ","DOE, JOHN","3423423",0,1900-01-01
> 00:00:00,"","@@
> ","","JOHN DOE PROCESSING PLANT"
>
> Here's my format file:
>
> 7.0
> 10
> 1 SQLCHAR 0 0 "\"" 0 ""
> 2 SQLCHAR 0 250 "\",\"" 1 "c1"
> 3 SQLCHAR 0 250 "\",\"" 2 "c2"
> 4 SQLCHAR 0 250 "\"," 3 "c3"
> 5 SQLCHAR 0 250 "," 4 "c4"
> 6 SQLCHAR 0 250 ",\"" 5 "c5"
> 7 SQLCHAR 0 250 "\",\"" 6 "c6"
> 8 SQLCHAR 0 250 "\",\"" 7 "c7"
> 9 SQLCHAR 0 250 "\",\"" 8 "c8"
> 10 SQLCHAR 0 250 "\"\r\n" 9 "c9"
>
> I even tried simplying it into the simplist form possible because I
> kept getting an error...
You don't say which version of SQL Server you are using, but I tried
you sample data and format file on SQL 7, SQL 2000 and SQL 2005. On the
latter two it works fine, whereas on SQL 7, I got "Unexpected EOF
encountered in BCP data file".
If you are on SQL 7, do you know that SQL 2008 is due come out this quarter?
Maybe time to upgrade? 
Joke aside, I think your best option is to download SQL 2005 Express from
Microsoft and install it, if only get access to a better BCP. To wit, you
can run BCP 2005 against SQL 7 without problem.
--
Erland Sommarskog, SQL Server MVP, esquel DeleteThis @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 |
|
| Back to top |
|
| |
External
Since: Mar 03, 2006
Posts: 234
|
(Msg. 3) Posted: Sun Aug 10, 2008 11:43 am
Post subject: RE: Bulk Import w/Format File and Text Fields - Why is it not working? [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
Personally, I'd rather pre-process the file to get it into the right format
to avoid using a format file. I find the BCP format file more trouble than
it's worth in most cases.
Linchi
"Brad" wrote:
> Here's what my data looks like:
>
> "12312331 ","DOE, JOHN","3423423",0,1900-01-01
> 00:00:00,"","@@
> ","","JOHN DOE PROCESSING PLANT"
>
> Here's my format file:
>
> 7.0
> 10
> 1 SQLCHAR 0 0 "\"" 0 ""
> 2 SQLCHAR 0 250 "\",\"" 1 "c1"
> 3 SQLCHAR 0 250 "\",\"" 2 "c2"
> 4 SQLCHAR 0 250 "\"," 3 "c3"
> 5 SQLCHAR 0 250 "," 4 "c4"
> 6 SQLCHAR 0 250 ",\"" 5 "c5"
> 7 SQLCHAR 0 250 "\",\"" 6 "c6"
> 8 SQLCHAR 0 250 "\",\"" 7 "c7"
> 9 SQLCHAR 0 250 "\",\"" 8 "c8"
> 10 SQLCHAR 0 250 "\"\r\n" 9 "c9"
>
> I even tried simplying it into the simplist form possible because I
> kept getting an error...
>
> 7.0
> 9
> 1 SQLCHAR 0 250 "," 1 "c1"
> 2 SQLCHAR 0 250 "," 2 "c2"
> 3 SQLCHAR 0 250 "," 3 "c3"
> 4 SQLCHAR 0 250 "," 4 "c4"
> 5 SQLCHAR 0 250 "," 5 "c5"
> 6 SQLCHAR 0 250 "," 6 "c6"
> 7 SQLCHAR 0 250 "," 7 "c7"
> 8 SQLCHAR 0 250 "," 8 "c8"
> 9 SQLCHAR 0 250 "\"\r\n" 9 "c9"
>
> Which should just remove the last quote in the final field. No matter
> what I try, I get the same error each time:
>
> Could not bulk insert. Invalid column number in format file 'D:\UPSDATA
> \DBUS1000\LeadImprovement\FORMAT3.fmt'.
>
> However this works:
>
>
> 7.0
> 9
> 1 SQLCHAR 0 250 "," 1 "c1"
> 2 SQLCHAR 0 250 "," 2 "c2"
> 3 SQLCHAR 0 250 "," 3 "c3"
> 4 SQLCHAR 0 250 "," 4 "c4"
> 5 SQLCHAR 0 250 "," 5 "c5"
> 6 SQLCHAR 0 250 "," 6 "c6"
> 7 SQLCHAR 0 250 "," 7 "c7"
> 8 SQLCHAR 0 250 "," 8 "c8"
> 9 SQLCHAR 0 250 "\r\n" 9 "c9"
>
> It seems it dislikes the escaped " as a field delimiter no matter
> what....
>
> >> Stay informed about: Bulk Import w/Format File and Text Fields - Why is it not .. |
|
| Back to top |
|
| |
External
Since: May 30, 2004
Posts: 1649
|
(Msg. 4) Posted: Sun Aug 10, 2008 2:17 pm
Post subject: RE: Bulk Import w/Format File and Text Fields - Why is it not working? [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
|
|
| Back to top |
|
| |
External
Since: Mar 03, 2006
Posts: 234
|
(Msg. 5) Posted: Mon Aug 11, 2008 9:22 am
Post subject: RE: Bulk Import w/Format File and Text Fields - Why is it not work [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
This may be a matter of experience and cognitive style, just like the
question of which GUI design is more intuitive.
But the fact you stayed away from it for 8~10 years says a lot about its
usibility already. I like to preprocess a file also because it has the added
value of validating the data in the file, which makes it much easier to debug
data import problems.
> Pre-processing a 1MB is no issue. Pre-processing a 1GB is likely to be.
Pre-processing a 1GB file is really rather trivial these days. Now, if it's
100GB, one may have to think a bit harder. Even there, going through that
file sequentially in one shot shouldn't be a huge issue as long as you don't
try to load the whole thing into memory as notepad likes to do. Considering
the benefit of precisely locate any data format problems during the
pre-process, I'd probably still go with pre-processing it.
Linchi
"Erland Sommarskog" wrote:
> Linchi Shea (LinchiShea@discussions.microsoft.com) writes:
> > Personally, I'd rather pre-process the file to get it into the right
> > format to avoid using a format file. I find the BCP format file more
> > trouble than it's worth in most cases.
>
> Hey, that's only because you are not used to them. I think I also stayed
> away from them for the first 8-10 years or so I used SQL Server, but
> since then I have become a convert! They are quite intellectual challenges.
> Sometimes you find that they can do a lot more than you initially expected.
> True, it is also quite common that you hit a road-block.
>
> Pre-processing a 1MB is no issue. Pre-processing a 1GB is likely to be.
>
> .... now, it only remains to see how long I will stay away from SSIS. This
> far I have been successful.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel.TakeThisOut@sommarskog.se
>
> Links for SQL Server Books Online:
> SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
> SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
> SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
>
> >> Stay informed about: Bulk Import w/Format File and Text Fields - Why is it not .. |
|
| Back to top |
|
| |
|
You can post new topics in this forum
You can reply to topics in this forum
You can edit your posts in this forum
You can delete your posts in this forum
You can vote in polls in this forum
|
|
|
|
|
|
|
FAQ
Search
Profile
Private Messages
Log in
