May 25, 2007 at 3:52 pm
Hello,
I'm hoping someone experienced with BCP can point me in the right direction. I'm able to insert a file with 1 row of data. When I copy and paste that same data into the file again, so there are two rows of data, the bcp command fails. BTW, I have no control over the file format and I have truncated it down to the first row for testing.
Thanks in advance for your suggestions!
Command:
Bulk insert leadimport from 'C:\Program Files\Interflow\Internet\LMCC_raw.txt'
Error Message:
Server: Msg 4863, Level 16, State 1, Line 1
Bulk insert data conversion error (truncation) for row 1, column 15 (Model).
File LMCC_raw.txt:
"Jane" "" "Sheldon" "505 Morgan Street" "Petoey" "WI" "49670" "2813472123" "" "" "nshn@charter.net" "2007" "Buick" "Rainier" ""
"Jane" "" "Sheldon" "505 Morgan Street" "Petoey" "WI" "49670" "2813472123" "" "" "nshn@charter.net" "2007" "Buick" "Rainier" ""
Like I said if LMCC_raw.txt looks like this, the record is inserted into the table:
"Jane" "" "Sheldon" "505 Morgan Street" "Petoey" "WI" "49670" "2813472123" "" "" "nshn@charter.net" "2007" "Buick" "Rainier" ""
The table, if you need it for testing....
CREATE TABLE [LeadImport] (
[FirstName] [varchar] (75) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MiddleName] [varchar] (75) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LastName] [varchar] (75) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Address Line 1] [varchar] (75) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Address Line 2] [varchar] (75) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[City] [varchar] (75) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[State] [varchar] (75) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ZIP] [varchar] (75) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Phone1] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Phone2] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Phone3] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Email1] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Year] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Make] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Model] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
May 25, 2007 at 3:59 pm
YOu can use the following sample code:
SET @vchrBulkCmd = 'BULK INSERT #ALLFILES
FROM ''c:\AllFiles.txt''
WITH ( DATAFILETYPE= ''char'', ROWTERMINATOR = '''+CHAR(10)+''' ) '
EXEC(@vchrBulkCmd)
This code is dumping from allfiles.txt to a temp table #Allfiles
May 25, 2007 at 4:15 pm
Prasad,
You rock!! Many thanks. Now I can relax over the holiday week-end knowing this is taken care of 😉
Thank You, Kim
Final code ignoring the first row which shows the column names
declare @vchrBulkCmd varchar(5000)
SET @vchrBulkCmd = 'BULK INSERT leadimport
FROM ''C:\Program Files\Interflow\Internet\LMCC_raw.dat''
WITH ( DATAFILETYPE= ''char'', ROWTERMINATOR = '''+CHAR(10)+''',FIRSTROW=2 ) '
EXEC(@vchrBulkCmd)
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply