dbTalk Databases Forums  

Bulk Import w/Format File and Text Fields - Why is it not working?

Go Back dbTalk Databases Forums Databases microsoft.public.sqlserver.tools Bulk Import w/Format File and Text Fields - Why is it not working?

microsoft.public.sqlserver.tools microsoft.public.sqlserver.tools


Discuss Bulk Import w/Format File and Text Fields - Why is it not working? in the microsoft.public.sqlserver.tools forum.



Reply
Page 1 of 4 1 2 3 4 >
 
Thread Tools Display Modes
  #1  
Old   
Brad
 
Posts: n/a

Default Bulk Import w/Format File and Text Fields - Why is it not working? - 08-04-2008 , 11:35 AM





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....


Reply With Quote
  #2  
Old   
Erland Sommarskog
 
Posts: n/a

Default Re: Bulk Import w/Format File and Text Fields - Why is it not working? - 08-04-2008 , 04:45 PM





Brad (bfreshour (AT) gmail (DOT) com) writes:
Quote:
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 (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx


Reply With Quote
  #3  
Old   
Erland Sommarskog
 
Posts: n/a

Default Re: Bulk Import w/Format File and Text Fields - Why is it not working? - 08-04-2008 , 04:45 PM


Brad (bfreshour (AT) gmail (DOT) com) writes:
Quote:
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 (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx


Reply With Quote
  #4  
Old   
Erland Sommarskog
 
Posts: n/a

Default Re: Bulk Import w/Format File and Text Fields - Why is it not working? - 08-04-2008 , 04:45 PM


Brad (bfreshour (AT) gmail (DOT) com) writes:
Quote:
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 (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx


Reply With Quote
  #5  
Old   
Erland Sommarskog
 
Posts: n/a

Default Re: Bulk Import w/Format File and Text Fields - Why is it not working? - 08-04-2008 , 04:45 PM


Brad (bfreshour (AT) gmail (DOT) com) writes:
Quote:
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 (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx


Reply With Quote
  #6  
Old   
Erland Sommarskog
 
Posts: n/a

Default Re: Bulk Import w/Format File and Text Fields - Why is it not working? - 08-04-2008 , 04:45 PM


Brad (bfreshour (AT) gmail (DOT) com) writes:
Quote:
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 (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx


Reply With Quote
  #7  
Old   
Erland Sommarskog
 
Posts: n/a

Default Re: Bulk Import w/Format File and Text Fields - Why is it not working? - 08-04-2008 , 04:45 PM


Brad (bfreshour (AT) gmail (DOT) com) writes:
Quote:
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 (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx


Reply With Quote
  #8  
Old   
Erland Sommarskog
 
Posts: n/a

Default Re: Bulk Import w/Format File and Text Fields - Why is it not working? - 08-04-2008 , 04:45 PM


Brad (bfreshour (AT) gmail (DOT) com) writes:
Quote:
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 (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx


Reply With Quote
  #9  
Old   
Erland Sommarskog
 
Posts: n/a

Default Re: Bulk Import w/Format File and Text Fields - Why is it not working? - 08-04-2008 , 04:45 PM


Brad (bfreshour (AT) gmail (DOT) com) writes:
Quote:
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 (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx


Reply With Quote
  #10  
Old   
Erland Sommarskog
 
Posts: n/a

Default Re: Bulk Import w/Format File and Text Fields - Why is it not working? - 08-04-2008 , 04:45 PM


Brad (bfreshour (AT) gmail (DOT) com) writes:
Quote:
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 (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx


Reply With Quote
Reply
Page 1 of 4 1 2 3 4 >

« Previous Thread | Next Thread »



Thread Tools
Display Modes
Linear Mode Linear Mode

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
vB code is On
Smilies are On
[IMG] code is On
HTML code is Off



Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.


Contact Us - dbTalk Databases Forums - Archive - Top