April 29, 2010 at 7:22 am
I am having a ridiculous time of trying to figure out what I am doing wrong with a bulk insert. It seems this should be relatively simple, I am just trying to bulk import the contents of a file into a table. here its the table
CREATE TABLE [dbo].[TranaudMaster](
[Batch] [int] NULL,
[Seq] [int] NULL,
[FI] [char](5) NULL,
[PatientNo] [varchar](20) NULL,
[PatientName] [varchar](50) NULL,
[SvcCode] [char](15) NULL,
[SvcDate] [char](15) NULL,
[Svc] [char](5) NULL,
[Qty] [int] NULL,
[CdType] [char](5) NULL,
[Glkey] [char](10) NULL,
[Pt] [char](5) NULL,
[Fc] [char](5) NULL,
[TotalAmount] [float] NULL,
[ProFee] [float] NULL,
[OverUnder] [float] NULL,
[DtarDate] [char](10) NULL
) ON [PRIMARY]
Here are a couple of rows of the file I am trying to import, this is a tab delimited file
0 1 I 10000000001 "DOE ,JOHN" 30000001 4/2/2010 GMX 1 RM 870 J M 3444 0 0
0 2 I 10000000002 "DAY ,JANE" 30000002 4/2/2010 GMD 1 RM 887 S D 3444 0 0
0 3 I 10000000003 "DIP ,MURIEL" 30000003 4/2/2010 SCU 1 RM 874 I H 6564 0 0
I have tried importing using
bulk insert TranaudMaster from 'C:\test.txt'
with (FIELDTERMINATOR ='\t')
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 16 (OverUnder).
so I tried creating a formatfile using
bcp sms.dbo.tranaudmaster format nul -c -f tranaud.fmt -T -S sqld1-dev01\dev01
which created
9.0
17
1 SQLCHAR 0 12 "\t" 1 Batch ""
2 SQLCHAR 0 12 "\t" 2 Seq ""
3 SQLCHAR 0 5 "\t" 3 FI SQL_Latin1_General_CP1_CI_AS
4 SQLCHAR 0 20 "\t" 4 PatientNo SQL_Latin1_General_CP1_CI_AS
5 SQLCHAR 0 50 "\t" 5 PatientName SQL_Latin1_General_CP1_CI_AS
6 SQLCHAR 0 15 "\t" 6 SvcCode SQL_Latin1_General_CP1_CI_AS
7 SQLCHAR 0 15 "\t" 7 SvcDate SQL_Latin1_General_CP1_CI_AS
8 SQLCHAR 0 5 "\t" 8 Svc SQL_Latin1_General_CP1_CI_AS
9 SQLCHAR 0 12 "\t" 9 Qty ""
10 SQLCHAR 0 5 "\t" 10 CdType SQL_Latin1_General_CP1_CI_AS
11 SQLCHAR 0 10 "\t" 11 Glkey SQL_Latin1_General_CP1_CI_AS
12 SQLCHAR 0 5 "\t" 12 Pt SQL_Latin1_General_CP1_CI_AS
13 SQLCHAR 0 5 "\t" 13 Fc SQL_Latin1_General_CP1_CI_AS
14 SQLCHAR 0 30 "\t" 14 TotalAmount ""
15 SQLCHAR 0 30 "\t" 15 ProFee ""
16 SQLCHAR 0 30 "\t" 16 OverUnder ""
17 SQLCHAR 0 10 "\r" 17 DtarDate SQL_Latin1_General_CP1_CI_AS
so I imported using
bulk insert TranaudMaster from 'C:\test.txt'
with (FORMATFILE = 'C:\tranaud.fmt')
and I get ...
Msg 4863, Level 16, State 1, Line 2
Bulk load data conversion error (truncation) for row 1, column 1 (Batch).
Msg 7399, Level 16, State 1, Line 2
The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.
Msg 7330, Level 16, State 2, Line 2
Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".
Now mind you I have spent hours trying a number of different things, creating another import file with just filler data.. trying some import using openrowset which just hangs for me.. I am sysadmin and bulkadmin on my testing area so I don't think its security or anything.. I can't use another delimiter other then tab as that is the way the file is generated. I am sure I am just missing something dumb as I am pretty sure this should be a no brainer.. So any help would be greatly appreciated and go a long way to restoring my sanity with this issue.
April 29, 2010 at 7:58 am
never mind .. im dumb .. missed how many columns there actually were and how many were in file.. and I counted them like 15 times .. dumb dumb dumb ..
April 29, 2010 at 8:50 am
That error has nothing to do with the batch size... it has everything to do with the column size of your target table. The error is saying that if it tries to insert a given field from the file, it would need to truncate it to make it fit the width of a column in the table.
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
April 30, 2010 at 6:52 am
yah...! actually you come across that error not from the disturbance of bulk insert or any other thing but its all because you are trying to insert data into a column where there's insufficient space for it, so the data will be truncated while being inserted.... :-D:-D:-D:-D:-D:-D:-D:-D:-D:-D:-D:-D:-D:-D:-D:-D:-D:-D:-D:-D:-D:-D:-D
April 30, 2010 at 7:03 am
Did you get solution ?
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
April 30, 2010 at 7:09 am
Yeah .. you are correct in your assessment .. but it was mainly due to my own oversight .. the file had 17 columns and the table had 16.. so it was trying to insert the that extra column and it was messing up the data that was SUPPOSED to be going into the 16th column .. thank you for your reply ..!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply