Solved

BCP problem

Posted on 2004-03-31
15
1,403 Views
Last Modified: 2007-12-19
I am trying to use BCP to get a text file into a table. I have never used BCP before and am having trouble getting it to work. I thought if I showed you guys what I have so far, you may be able to show me where I am going wrong.

Table def(jetnet..j300)

ID                   int      4
[Text Line 1]      char      100
[Text Line 2]      char      100
[Text Line 3]      char      100
[Text Line 4]      char      100
[Text Line 5]      char      100
[Text Line 6]      char      100
[Address Line 1]      char      50
[Address Line 2]      char      50
[Address Line 3]      char      50
[Address Line 4]      char      50
[Address Line 5]      char      50
LookUpCode      char      10
[Filler 1]           char      40
[Postnet Barcode]      char      11
[Planet Barcode]      char      16
[Filler 2]          char      23
Field16            char      1
Done               int      4
DoneTime        datetime      8
StackZone        int      4
StackOp           int      4

My format file(\\Orbitty\Depts\DP\ALAN\example.fmt)

7.0
22
1    SQLNUMERIC    0    4    ""    1    ID
2    SQLCHAR    0    100    ""    2    Text Line 1
3    SQLCHAR    0    100    ""    3    Text Line 2
4    SQLCHAR    0    100    ""    4    Text Line 3
5    SQLCHAR    0    100    ""    5    Text Line 4
6    SQLCHAR    0    100    ""    6    Text Line 5
7    SQLCHAR    0    100    ""    7    Text Line 6
8    SQLCHAR    0    50    ""    8    Address Line 1
9    SQLCHAR    0    50    ""    9    Address Line 2
10    SQLCHAR    0    50    ""    10    Address Line 3
11    SQLCHAR    0    50    ""    11    Address Line 4
12    SQLCHAR    0    50    ""    12    Address Line 5
13    SQLCHAR    0    10    ""    13    LookUpCode
14    SQLCHAR    0    40    ""    14    Filler 1
15    SQLCHAR    0    11    ""    15    Postnet Barcode
16    SQLCHAR    0    16    ""    16    Planet Barcode
17    SQLCHAR    0    23    ""    17    Filler 2
18    SQLCHAR    0    1    "\r\n"    18    Field16
19    SQLNUMERIC    0    0    ""   0    Done
20    SQLDATETIME    0    0    ""    0    Done Time
21    SQLNUMERIC    0    0    ""   0    StackZone
22    SQLNUMERIC    0    0    ""   0    StackOp

Now when running the following command....

xp_cmdshell 'bcp jetnet..j300 in "\\Orbitty\Depts\DP\ALAN\example.txt" -f"\\Orbitty\Depts\DP\ALAN\example.fmt" -Usa -Ppassword'

I get the following return...

output                                                                                                                                                                                                       --------------------------------------------------------------------------------------------------------------------------------------
SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Incorrect host-column number found in BCP format-file


Any help will be greatly appreciated.


Thanks,

Alan


0
Comment
Question by:AlanJDM
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 7
15 Comments
 
LVL 69

Expert Comment

by:Callandor
ID: 10723634
Shouldn't these

19    SQLNUMERIC    0    0    ""   0    Done
20    SQLDATETIME    0    0    ""    0    Done Time
21    SQLNUMERIC    0    0    ""   0    StackZone
22    SQLNUMERIC    0    0    ""   0    StackOp

have non-zero column numbers, like your other columns?
0
 
LVL 9

Author Comment

by:AlanJDM
ID: 10723674
I tried this...

19    SQLNUMERIC    0    0    ""   19    Done
20    SQLDATETIME    0    0    ""    20    Done Time
21    SQLNUMERIC    0    0    ""   21    StackZone
22    SQLNUMERIC    0    0    ""   22    StackOp

and got the same error. The reason I set them to 0 was based on this snipet I found in a tutorial...

You need to create a format file to properly load this. In this case, look at the following format file (which you should copy if you want to run this example):

6.0
5
1    SQLCHAR    0    9    ","    1    cust_id
2    SQLCHAR    0    0    ""    0    cust_name
3    SQLCHAR    0    20    ","    3    city
4    SQLCHAR    0    2    ","    4    state
5    SQLCHAR    0    10    "\r\n"    5    zip

You have to change the server column number for cust_name to 0. This indicates to BCP that the data is not being passed in this file. You also have to reset the field length of this column to 0 and avoid specifying a row terminator.

The tutorial I am reffering to is http://www.ii.metu.edu.tr/~is503/library/TeachYourself_SQLServer65_21Days/ch09/ch09.htm#Heading6


Thanks,

Alan

0
 
LVL 69

Expert Comment

by:Callandor
ID: 10723942
Ok, I would say move the row terminator in 18

18    SQLCHAR    0    1    "\r\n"    18    Field16

to the last field (22).
0
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
LVL 9

Author Comment

by:AlanJDM
ID: 10724069
I tried that and got the same error. My thought for putting the \r\n in 18 is because the last field that is actually in the datais 18. I then tried it without the last for fields in the format file and got the same error.

My fear is that because it is not liking the host-column number it isn't even looking at the rest of the format file, which would explain why no matter what changes I make the error stays the same.


Alan
0
 
LVL 69

Expert Comment

by:Callandor
ID: 10724348
Your format file should reflect what you data file actually contains, so you have tried the original format file w/o items 19, 20, 21, and 22, and you have changed the second row to 18?
0
 
LVL 9

Author Comment

by:AlanJDM
ID: 10724397
Yes, I did try that and got the same error. The reason I put those fields in the format file is because the tutorial made it a point to include an example of a table that had more columns defined than there are in the data and they did include the extra field in the format file.


In the first example, you are going to load a table from a data file that doesn't have as many columns of data as the table. Your data file looks like this (comma delimited):

cust_id,city,state,zip_code

For instance, two sample rows might look like the following:

1,Seattle,WA,98102
2,Bellevue,WA,98004

You need to create a format file to properly load this. In this case, look at the following format file (which you should copy if you want to run this example):

6.0
5
1    SQLCHAR    0    9    ","    1    cust_id
2    SQLCHAR    0    0    ""    0    cust_name
3    SQLCHAR    0    20    ","    3    city
4    SQLCHAR    0    2    ","    4    state
5    SQLCHAR    0    10    "\r\n"    5    zip

You have to change the server column number for cust_name to 0. This indicates to BCP that the data is not being passed in this file. You also have to reset the field length of this column to 0 and avoid specifying a row terminator.


Alan


0
 
LVL 69

Expert Comment

by:Callandor
ID: 10724426
Are you not using a field delimeter?  I have always used one myself.  If you can test a sample row with comma-delimited data, that may work.
0
 
LVL 69

Expert Comment

by:Callandor
ID: 10724437
I think the combination of non-existent fields and no field delimeter is confusing BCP.
0
 
LVL 9

Author Comment

by:AlanJDM
ID: 10724442
I can test for the sake of knowing if it works or not, but either way that doesn't help me because the data I need to make this work with is not comma delimited. I will give it a shot though and let you know the results.


Alan
0
 
LVL 9

Author Comment

by:AlanJDM
ID: 10726271
ok, well I have made some progress but I now have another issue. My first problem was because I had my row delimiter incorrect...

"\r\n"

needed to be "\l"

because I am using a file created on a unix server and is delimited with single line feeds.

Anyway, now I get a "command successful" from the xp_cmdshell but it doesn't actually do anything. My tabel is empty after running it and I don't get any kind of messages at all. If I run it from the command line it executes, but doesn't return any message at all. I even added an error log but it is empty.

Here is where I am at...

xp_cmdshell 'bcp jetnet..j300 in "\\Orbitty\Depts\DP\ALAN\example.txt" -f"\\Orbitty\Depts\DP\ALAN\example.fmt" -e"\\Orbitty\Depts\DP\ALAN\error.log" -Usa -Ppassword'

7.0
21
1     SQLCHAR       1    100    ""      1    TextLine1
2     SQLCHAR       1    100    ""      2    TextLine2
3     SQLCHAR       1    100    ""      3    TextLine3
4     SQLCHAR       1    100    ""      4    TextLine4
5     SQLCHAR       1    100    ""      5    TextLine5
6     SQLCHAR       1    100    ""      6    TextLine6
7     SQLCHAR       1     50    ""      7    AddressLine1
8     SQLCHAR       1     50    ""      8    AddressLine2
9     SQLCHAR       1     50    ""      9    AddressLine3
10    SQLCHAR       1     50    ""     10    AddressLine4
11    SQLCHAR       1     50    ""     11    AddressLine5
12    SQLCHAR       1     10    ""     12    LookUpCode
13    SQLCHAR       1     40    ""     13    Filler1
14    SQLCHAR       1     11    ""     14    PostnetBarcode
15    SQLCHAR       1     16    ""     15    PlanetBarcode
16    SQLCHAR       1     23    ""     16    Filler2
17    SQLCHAR       1      1    "\l"   17    Field16
18    SQLNUMERIC    0      0    ""      0    Done
19    SQLNUMERIC    0      0    ""      0    DoneTime
20    SQLNUMERIC    0      0    ""      0    StackZone
21    SQLNUMERIC    0      0    ""      0    StackOp


Any ideas?
0
 
LVL 69

Expert Comment

by:Callandor
ID: 10726557
Does moving the "\|" delimeter to item 21 make a difference?  When bcp is working correctly, you should get a record count display every 2000 records, I think.
0
 
LVL 9

Author Comment

by:AlanJDM
ID: 10726832
Well, I did try that and it does seem to run longer now, but still finishes with a successful messag but nothing is uploaded to the table.



Alan
0
 
LVL 69

Expert Comment

by:Callandor
ID: 10726992
Does your version of bcp require -S servername as an argument?
0
 
LVL 9

Author Comment

by:AlanJDM
ID: 10727264
Well, good news... I got it working. The change I made to get it to work makes no sence to me, but I am just glad it works as my boss was about to pull the plug on this whole project.

The second column of the config file is described as the "prefix length" and based upon a section of the tutorial I was working with I had set all char fields to 1...

"The prefix length determines how much space is reserved to indicate how long this particular field is. For many types, this can be 0 bytes. For char or varchar data, this can be 1 byte long. For text or image datatypes, the default length is 4 bytes"

I don't know what made think to try this, but what I did is set them all to zero and voila - works great.

This is what finaly worked...

xp_cmdshell 'bcp jetnet..j300 in "\\Orbitty\Depts\DP\ALAN\example.txt" -f"\\Orbitty\Depts\DP\ALAN\example.fmt" -e"\\Orbitty\Depts\DP\ALAN\error.log" -Usa -Ppassword'

7.0
17
1     SQLCHAR       0    100    ""      1    TextLine1
2     SQLCHAR       0    100    ""      2    TextLine2
3     SQLCHAR       0    100    ""      3    TextLine3
4     SQLCHAR       0    100    ""      4    TextLine4
5     SQLCHAR       0    100    ""      5    TextLine5
6     SQLCHAR       0    100    ""      6    TextLine6
7     SQLCHAR       0     50    ""      7    AddressLine1
8     SQLCHAR       0     50    ""      8    AddressLine2
9     SQLCHAR       0     50    ""      9    AddressLine3
10    SQLCHAR       0     50    ""     10    AddressLine4
11    SQLCHAR       0     50    ""     11    AddressLine5
12    SQLCHAR       0     10    ""     12    LookUpCode
13    SQLCHAR       0     40    ""     13    Filler1
14    SQLCHAR       0     11    ""     14    PostnetBarcode
15    SQLCHAR       0     16    ""     15    PlanetBarcode
16    SQLCHAR       0     23    ""     16    Filler2
17    SQLCHAR       0      1    ""     17    Field16
18    SQLNUMERIC    0      0    ""      0    Done
19    SQLNUMERIC    0      0    ""      0    DoneTime
20    SQLNUMERIC    0      0    ""      0    StackZone
21    SQLNUMERIC    0      0    "\l"    0    StackOp


Thanks for your help. I will of course award you the points for this because you have certainly earned them sticking with me on this all day long, but if you can offer any information that could make me better understand that "prefix length" parameter, I would certainly appreciate it.

Thanks again for the help.


Alan



0
 
LVL 69

Accepted Solution

by:
Callandor earned 500 total points
ID: 10727319
I think this has the answer for you: http://www.schemamania.org/jkl/booksonline/SQLBOL70/html/impt_bcp_13.htm

When bulk copying data to SQL Server, the prefix length is the value specified when the data file was created originally. If the data file was not created with bcp, it is unlikely that length prefix characters exist. In this instance, specify 0 for the prefix length.

0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
find SQL job run average duration 24 57
SQL Server Sum Over Multiple Tables 20 33
Convert time stamp to date 2 57
Delete old Sharepoint backups 2 22
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question