July 20, 2006 at 12:28 pm
I am being sent a file that looks like:
Line 1 $$$$$$$$$ED20060407145337690110EMPLOYEE DEMOGRAPHICS
Line 2 10000496380393131214025ANDERSON TRIGVY M19680105BOX 752 TEULON MB R0C3B0886330820020041018
Line 3 10005266089042071213089BUCHANAN VALERIE F19480331BOX 1501 STONEWALL, MB R0C2Z0467719420120041125
Line 4 22708186450569951113060KALER RAMANDEEP F19710313185 KINVER AVENUE WINNIPEG MB R2R1G9632599650220041125
Line 5 99999999900004814
The words Line n just indicate the line number of the data, they don't actually appear in the file. I need to use BCP (Not Bulk Insert or DTS) since an end user will be periodically copying the data. BulK Insert and DTS read files on the server or using a UNC file name and I would like the user to run this themselves based on a file on their local C Drive. IN any case I created the following BCP statement:
bcp PROtrain.dbo.Demographics in c:\Drive_C\test1.txt -S172.19.40.63 -T -eC:\Drive_C\bcperror.txt -F1 -L2 -fc:\Drive_C\Format1.txt
The format file (created by the the bcp command) looks like
8.0
15
1 SYBCHAR 0 7 "" 1 Empl_Empno ""
2 SYBCHAR 0 9 "" 2 Filler ""
3 SYBCHAR 0 2 "" 3 Empl_Region ""
4 SYBCHAR 0 3 "" 4 Empl_Office ""
5 SYBCHAR 0 2 "" 5 Empl_Staff ""
6 SYBCHAR 0 20 "" 6 Empl_Surname ""
7 SYBCHAR 0 15 "" 7 Empl_Given_Name ""
8 SYBCHAR 0 1 "" 8 Empl_Gender ""
9 SYBCHAR 0 8 "" 9 Empl_Birth_Date ""
10 SYBCHAR 0 25 "" 10 Empl_Address_Line1 ""
11 SYBCHAR 0 20 "" 11 Empl_Address_Line2 ""
12 SYBCHAR 0 6 "" 12 Empl_Postal_Code ""
13 SYBCHAR 0 7 "" 13 Empl_Home_Phone_Num ""
14 SYBCHAR 0 3 "" 14 Filler2 ""
15 SYBCHAR 0 8 "" 15 Empl_Termination_Date ""
The SQL table looks like:
CREATE TABLE [dbo].[Demographics] (
[Empl_Empno] [char] (7) NOT NULL ,
[Filler] [char] (9) NULL ,
[Empl_Region] [char] (2) NULL ,
[Empl_Office] [char] (3) NULL ,
[Empl_Staff] [char] (2) NULL ,
[Empl_Surname] [char] (20) NULL ,
[Empl_Given_Name] [char] (15) NULL ,
[Empl_Gender] [char] (1) NULL ,
[Empl_Birth_Date] [char] (8) NULL ,
[Empl_Address_Line1] [char] (25) NULL ,
[Empl_Address_Line2] [char] (20) NULL ,
[Empl_Postal_Code] [char] (6) NULL ,
[Empl_Home_Phone_Num] [char] (7) NULL ,
[Filler2] [char] (3) NULL ,
[Empl_Termination_Date] [char] (8) NULL
) ON [PRIMARY]
GO
I run the BCP command and no errors are produced but now a SELECT * from this table gives these results:
Empl_Empno Filler Empl_Region Empl_Office Empl_Staff Empl_Surname Empl_Given_Name Empl_Gender Empl_Birth_Date Empl_Address_Line1 Empl_Address_Line2 Empl_Postal_Code Empl_Home_Phone_Num Filler2 Empl_Termination_Date ---------- --------- ----------- ----------- ---------- -------------------- --------------- ----------- --------------- ------------------------- -------------------- ---------------- ------------------- ------- --------------------- $$$$$$$ $$ED20060 40 714 53 37690110EMPLOYEE DEM OGRAPHICS 1000 0 49638039 3131214025ANDERSON TRIGVY M19680 105BOX 752 TEULON M B R0C3B08863308 20020041018 10 0 05266089 042071213089BUCHANAN VALERIE F194 80331BO X 1 501
UGLY!!
It didn't skip the first row. Nothing is aligned correctly. I tried changing the SYBCHAR to SQLCHAR in the format file. No difference.
Can anyone help me bcp this file?
Francis
July 20, 2006 at 1:20 pm
First, use -F2 -L3 so you get rows 2 & 3. -F1 is never, ever going to skip the first row.
Next, since you've specified line numbers, I'm assuming there's a [CR][LF] at the end of each row. On the last line of the format file, in the first set of dbl-quotes, add the row terminator. It should look like this:
15 SYBCHAR 0 8 "\r\n" 15 Empl_Termination_Date ""
If the file orignated on a mainframe or unix box, then you may need to omit either the "\r" or the "\n", depending on the terminator used. That should do it. But one more question...
Did you create the bcp format file with your bcp? If so, do you have an old set of Sybase tools installed?
Another comment, DTS only runs at the server, and therefore only reads files from the server, when the dtsrun command is executed at the server, ie: via SQLAgent, stored proc, SQLEM when terminaled in, etc. If your user has the client tools, they've got dtsrun.exe. You can create a DTS package on the server, and they can execute it from the command line, even passing in the filename as a global var by using the /A switch.
July 20, 2006 at 2:53 pm
The "\r\n" worked fine. thank you. I tried changing the F2 to F2 and it really doesn't work as I expect. I get the error Error = [Microsoft][ODBC SQL Server Driver]Unexpected EOF encountered in BCP dat
If I delete the first line of the file and leave off the -F switch it works fine now. My guess is bcp notices the first line does follow the format and produces the error regardless if I said skip the first line. Maybe I need to write a DOS script to skip the first line?
>>Did you create the bcp format file with your bcp? If so, do you have an old set of Sybase tools installed?
No I hand wrote this. If I let bcp create the format file for me then is uses SQLCHAR not SYBCHAR. (Long story.. I'm not hand writing these files anymore)
Francis
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply