BCP Why does it never work?

Hello All,
I have dreaded this day for some time but I knoew it would arrive one
day...and thats where I need to use the BCP utility to bulk upload data to
my web hosting service (telstra - Australia). Unfortunately they do not
allow the use of the Transact SQL statement BULK INSERT, as you guessed it
that works. Here's the problem I have been working on for a couple of days.
I have a data file created from SQL2000 server here in the office, it
contains 4 fields:
PartNumber varcha(15)
Description varchar(25)
QtyOnHand int
Price money
Some sample data cut and pasted from the data file, fixed length no nasties
between fields and a ODOA at the end of each line.
1000FGM FUEL FILTER/WATER SE 0 542.05
1000FGP Fuel Filter, Water S 0 580
1000FH2 Fuel Filter, Water S 8 548.13
1000MA Fuel Filter, Water S 3 594.5
11007 Lid, Bowl & Base Gas 23 3.29
11040 Bowl Drain Fitting 1 16.87
110A Fuel Filter, Water S 2 195.55
11350 T Handle O'Ring 12 2.06
12003 LID GASKET 1 7.83
12014 GASKET LOWER LID 1 5.49
12041 Bowl Plug 1 2.46
120AS Fuel Filter, Water S 2 244.59
122R FUEL FILTER/WATER SE 0 222.61
130R-T-16S Fuel Filter, Water S 0 207.56
15005 Lid Gasket 2 2.27
15009 Bowl Gasket 5 7.15
I have let BCP create the format file, and this doen't work, I have defined
the format file myself and still does not work. Here's a sample of the
format file:
This is the format file I used to run the bcp last time.
7.0
4
1 SQLCHAR 0 15 "" 1
PartNumber Latin1_General_CI_AS
2 SQLCHAR 0 25 "" 2
Description Latin1_General_CI_AS
3 SQLINT 0 4 "" 3
QtyOnHand ""
4 SQLMONEY 1 8 "\r\n" 4 Price
""
When I run the bcp command this is the error that is generated:
SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Incorrect host-column number
found in BCP format-file
I am becoming extemely frustrated with this horrid little, but necessary
utility (Please Mr Telstra enable BULK INSERT command for me).
I have tried a number of variations, tab delimited files, no format file but
using the -n and -c switches but still no luck... If any body can provide
me with a solution it would be greatly appreciated.
Any suggestions '
Please reply directly to me at andrew.hull@.hcma.com.au
Thanks in advance.
Safe Sailing
AndrewBCP needs a NULL charater for empty fields. I strugled with this too.
Let Excel import it properly, then once you get it in your table, BCP it out
to a different file. THen compare the two files with LIST.EXE. Once you
are in LIST, hit H to go to Hex mode. You will see the NULL characters.
You cannot see them in NOTEPAD.
You would think that BCP is smart enough to move to a new record when it
hits the 0x0D 0x0A, but no, it is not.
"Plato" <andrew.hull@.hcma.com.au> wrote in message
news:OBk8%23OUiDHA.1964@.TK2MSFTNGP10.phx.gbl...
> Hello All,
> I have dreaded this day for some time but I knoew it would arrive one
> day...and thats where I need to use the BCP utility to bulk upload data
to
> my web hosting service (telstra - Australia). Unfortunately they do not
> allow the use of the Transact SQL statement BULK INSERT, as you guessed it
> that works. Here's the problem I have been working on for a couple of
days.
> I have a data file created from SQL2000 server here in the office, it
> contains 4 fields:
> PartNumber varcha(15)
> Description varchar(25)
> QtyOnHand int
> Price money
> Some sample data cut and pasted from the data file, fixed length no
nasties
> between fields and a ODOA at the end of each line.
> 1000FGM FUEL FILTER/WATER SE 0 542.05
> 1000FGP Fuel Filter, Water S 0 580
> 1000FH2 Fuel Filter, Water S 8 548.13
> 1000MA Fuel Filter, Water S 3 594.5
> 11007 Lid, Bowl & Base Gas 23 3.29
> 11040 Bowl Drain Fitting 1 16.87
> 110A Fuel Filter, Water S 2 195.55
> 11350 T Handle O'Ring 12 2.06
> 12003 LID GASKET 1 7.83
> 12014 GASKET LOWER LID 1 5.49
> 12041 Bowl Plug 1 2.46
> 120AS Fuel Filter, Water S 2 244.59
> 122R FUEL FILTER/WATER SE 0 222.61
> 130R-T-16S Fuel Filter, Water S 0 207.56
> 15005 Lid Gasket 2 2.27
> 15009 Bowl Gasket 5 7.15
> I have let BCP create the format file, and this doen't work, I have
defined
> the format file myself and still does not work. Here's a sample of the
> format file:
> This is the format file I used to run the bcp last time.
> 7.0
> 4
> 1 SQLCHAR 0 15 "" 1
> PartNumber Latin1_General_CI_AS
> 2 SQLCHAR 0 25 "" 2
> Description Latin1_General_CI_AS
> 3 SQLINT 0 4 "" 3
> QtyOnHand ""
> 4 SQLMONEY 1 8 "\r\n" 4 Price
> ""
> When I run the bcp command this is the error that is generated:
> SQLState = S1000, NativeError = 0
> Error = [Microsoft][ODBC SQL Server Driver]Incorrect host-column number
> found in BCP format-file
> I am becoming extemely frustrated with this horrid little, but necessary
> utility (Please Mr Telstra enable BULK INSERT command for me).
> I have tried a number of variations, tab delimited files, no format file
but
> using the -n and -c switches but still no luck... If any body can provide
> me with a solution it would be greatly appreciated.
>
> Any suggestions '
> Please reply directly to me at andrew.hull@.hcma.com.au
> Thanks in advance.
> Safe Sailing
> Andrew
>
>|||Hi Anthony,
The data itself is extracted from our ERP system and is massaged extensively
to produce a file where there are no blank fields and no null values, as you
know SQL and NULL are not good combination.
I have just got it working, using tab delimited file and using the -c switch
with no format file. All loads perfectly.
Over the last couple of days I have tried so many combinations that its not
funny. The simplest of them works but it took me ages to get to this point.
Now I have the process nailed to the wall so I will never forget the
pain...
Thanks again for your suggestion
Andrew
"Anthony Zessin" <Anthony.Zessin@.rrtc.com> wrote in message
news:e5I0KOWiDHA.3324@.TK2MSFTNGP11.phx.gbl...
> BCP needs a NULL charater for empty fields. I strugled with this too.
> Let Excel import it properly, then once you get it in your table, BCP it
out
> to a different file. THen compare the two files with LIST.EXE. Once you
> are in LIST, hit H to go to Hex mode. You will see the NULL characters.
> You cannot see them in NOTEPAD.
> You would think that BCP is smart enough to move to a new record when it
> hits the 0x0D 0x0A, but no, it is not.
>
>
> "Plato" <andrew.hull@.hcma.com.au> wrote in message
> news:OBk8%23OUiDHA.1964@.TK2MSFTNGP10.phx.gbl...
> > Hello All,
> >
> > I have dreaded this day for some time but I knoew it would arrive one
> > day...and thats where I need to use the BCP utility to bulk upload data
> to
> > my web hosting service (telstra - Australia). Unfortunately they do not
> > allow the use of the Transact SQL statement BULK INSERT, as you guessed
it
> > that works. Here's the problem I have been working on for a couple of
> days.
> >
> > I have a data file created from SQL2000 server here in the office, it
> > contains 4 fields:
> > PartNumber varcha(15)
> > Description varchar(25)
> > QtyOnHand int
> > Price money
> >
> > Some sample data cut and pasted from the data file, fixed length no
> nasties
> > between fields and a ODOA at the end of each line.
> >
> > 1000FGM FUEL FILTER/WATER SE 0 542.05
> > 1000FGP Fuel Filter, Water S 0 580
> > 1000FH2 Fuel Filter, Water S 8 548.13
> > 1000MA Fuel Filter, Water S 3 594.5
> > 11007 Lid, Bowl & Base Gas 23 3.29
> > 11040 Bowl Drain Fitting 1 16.87
> > 110A Fuel Filter, Water S 2 195.55
> > 11350 T Handle O'Ring 12 2.06
> > 12003 LID GASKET 1 7.83
> > 12014 GASKET LOWER LID 1 5.49
> > 12041 Bowl Plug 1 2.46
> > 120AS Fuel Filter, Water S 2 244.59
> > 122R FUEL FILTER/WATER SE 0 222.61
> > 130R-T-16S Fuel Filter, Water S 0 207.56
> > 15005 Lid Gasket 2 2.27
> > 15009 Bowl Gasket 5 7.15
> >
> > I have let BCP create the format file, and this doen't work, I have
> defined
> > the format file myself and still does not work. Here's a sample of the
> > format file:
> >
> > This is the format file I used to run the bcp last time.
> > 7.0
> > 4
> > 1 SQLCHAR 0 15 "" 1
> > PartNumber Latin1_General_CI_AS
> > 2 SQLCHAR 0 25 "" 2
> > Description Latin1_General_CI_AS
> > 3 SQLINT 0 4 "" 3
> > QtyOnHand ""
> > 4 SQLMONEY 1 8 "\r\n" 4
Price
> > ""
> >
> > When I run the bcp command this is the error that is generated:
> >
> > SQLState = S1000, NativeError = 0
> > Error = [Microsoft][ODBC SQL Server Driver]Incorrect host-column number
> > found in BCP format-file
> >
> > I am becoming extemely frustrated with this horrid little, but necessary
> > utility (Please Mr Telstra enable BULK INSERT command for me).
> >
> > I have tried a number of variations, tab delimited files, no format file
> but
> > using the -n and -c switches but still no luck... If any body can
provide
> > me with a solution it would be greatly appreciated.
> >
> >
> >
> > Any suggestions '
> >
> > Please reply directly to me at andrew.hull@.hcma.com.au
> >
> > Thanks in advance.
> > Safe Sailing
> > Andrew
> >
> >
> >
>