August 23, 2007 at 2:29 am
I have a database with the setting shown below.
[dbo].[Scansource](
[VENDOR PART#] [varchar](100) NULL,
[MANUFACTURER PART #] [varchar](100) NULL,
[DESCRIPTION] [varchar](100) NULL,
[MSRP] [varchar](100) NULL,
[CONTRACT_PRICE] [varchar](100) NULL,
[QUANTITY ON HAND] [varchar](100) NULL,
[MANUFACTURER_NAME] [varchar](100) NULL,
[CATEGORY1] [varchar](100) NULL,
[SERIAL_FLAG] [varchar](100) NULL
I have data in this format. In a simple text file with | seperating the columns.
VENDOR PART#|MANUFACTURER PART #|DESCRIPTION|MSRP|CONTRACT_PRICE|QUANTITY ON HAND|MANUFACTURER_NAME|CATEGORY1|SERIAL_FLAG|
APG-CD001A|CD-001A|CABLE A721.755.756 758 793 794 752|5.62|1.68|151|APG CASH DRAWER|APC|N|
APG-CD005A|CD-005A|CABLE RJ12 24V DMX/EPS/CTZ/TEC J2/SAM/AXH|5.62|3.35|0|APG CASH DRAWER|APC|N|
If I run bcp TEST.dbo.scansource in 832.txt -T -S VIDERP
Then I will get prompted for the field type length etc. If I enter these in manually then the import works fine. If I select the option to save the file then try to run it with the formatfile that has just been created using. (I have removed all records from the databse before re-running this)
bcp TEST.dbo.scansource in 832.txt -f 832.fmt -T -S VIDERP
Then I receive this error
SQLState = HY000, NativeError = 0
Error = [Microsoft][SQL Native Client]Incorrect host-column number found in BCP
format-file
The format file looks like this
9.0
9
1 SQLCHAR 0 100 "|" 1 VENDOR PART# Latin1_General_CI_AS
2 SQLCHAR 0 100 "|" 2 MANUFACTURER PART # Latin1_General_CI_AS
3 SQLCHAR 0 100 "|" 3 DESCRIPTION Latin1_General_CI_AS
4 SQLCHAR 0 100 "|" 4 MSRP Latin1_General_CI_AS
5 SQLCHAR 0 100 "|" 5 CONTRACT_PRICE Latin1_General_CI_AS
6 SQLCHAR 0 100 "|" 6 QUANTITY ON HAND Latin1_General_CI_AS
7 SQLCHAR 0 100 "|" 7 MANUFACTURER_NAME Latin1_General_CI_AS
8 SQLCHAR 0 100 "|" 8 CATEGORY1 Latin1_General_CI_AS
9 SQLCHAR 0 100 "|" 9 SERIAL_FLAG Latin1_General_CI_AS
I am importing into an SQL 2005 database.
Can anyone explain why it does not work specifiing the formatfile but works when I manually tell it the fiedl formats.
August 23, 2007 at 2:32 am
Okay found the problem it does not like spaces i nthe field names.
The simple solutions are always the ones you miss.
August 23, 2007 at 6:56 am
As a general rule of thumb, GLOBALLY, spaces in the middle of a name will either cause processes to not work as expected or force additional formatting and keystrokes to ensure that processes do work as expected.
That's what the underline/underscore character ('_') does - it fills the space. Of course, if your content is underlined (can't demonstrate via this browser), then the underscore is lost.
It is not a failing of computers that they don't read minds. We haven't invented the correct plug compatible interface to support mind reading yet.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy