|
|
Posted by John Bell on 09/08/05 20:34
Hi
Try a format file such as
8.0
2
1 SQLINT 0 0 "" 1 RecID
""
2 SQLCHAR 0 167 "\r\n" 2 Data
Latin1_General_CI_AS
John
"JJA" <johna@cbmiweb.com> wrote in message
news:1126194328.478796.201930@o13g2000cwo.googlegroups.com...
>I have fixed length records (167 bytes) in a .txt file and want to load
> this data exactly as is into a staging table where I hope to be able to
> later get at selected columns using the SUBSTRING function. Here is my
> target table:
> CREATE TABLE JJA_BCP_NHO_DAT (
> RecID int IDENTITY(1,1) NOT NULL
> , Data VARCHAR(167) NOT NULL
> )
> Here is my bcp command:
> bcp DevMDW.dbo.JJA_BCP_NHO_DAT in
> N:\RawData_MDS\Purchase\UnzipArea\2005_08_Aug_01497407.txt -f bcp.fmt
>
> Here is the .fmt file:
> 8.0
> 1
> 1 SQLCHAR 0 167 "" 2
> Data SQL_Latin1_General_CP1_CI_AS
> Here are the first 3 lines of data in the .txt file (note how 1st 2
> bytes are blank; last 2 bytes of each record are 78, 79, 80):
>
> 02160168C013CMA20050819 328
> UUU AGAWAM 36422005072901001 BERKSHIRE
> BANK 25013 78
> 01940155C001 MA20050805 254
> UUU AGAWAM 28072005071801001 WEBSTER
> BANK 25013 79
> 02350188C014CMA20050729 067
> UUU AGAWAM 24812005070701001 FIRST
> PIONEER FARM CR 25013 80
>
> The bcp command runs OK but the output is "shifted" in the column
> called DATA in the table. The IDENTITY column looks good in all rows,
> but only the first row is OK in the DATA column. Starting in the 2nd
> row, all bytes are truncated or shifted by what appears to be 2
> characters. This is hard to show with pasting results but I ran this
> query:
>
> SET ROWCOUNT 10
> SELECT RecID
> , SUBSTRING(Data,1,10) as FirstTen
> , SUBSTRING(Data,158,10) as LastTen
> , DATALENGTH(DATA) AS LEN_Data FROM JJA_BCP_NHO_DAT
>
> 1 02160168 25013 78 167
> 2 019401 25013 167
> 3 79 0235 CR 2501 167
> 4 3 80 01 CO 25 167
> 5 013 80 CORP 167
> 6 25013 80 REG SYS 167
> 7 25013 98 ANK 167
> 8 25013 VGS BK 167
> 9 2501 21 MTG CO 167
> 10 RP 25 RYWIDE HOM 167
>
> Every line in the .txt file ends after column 167 with x'0d0a'. I've
> tried a field terminator of \r\n and that produces this error:
>
> SQLState = S1000, NativeError = 0
> Error = [Microsoft][ODBC SQL Server Driver]Unexpected EOF encountered
> in BCP data-file
>
> Thanks in advance for some help on this.
>
Navigation:
[Reply to this message]
|