June 27, 2002 at 2:51 am
I am using SQL2K Standard Version 8.00.384 (SP1) and I want to insert data from a text file into a table using the TSQL bulk
insert command.
Table def:-
CREATE TABLE [dbo].[a] (
[a] [int] NULL ,
[int] NULL ,
[c] [int] NULL
) ON [PRIMARY]
GO
If the data within c:\import\aj.rpt is :-
1,2,3
4,5,6
And the bulk insert command is:-
bulk insert a
from 'c:\import\aj.rpt'
with
(
fieldterminator = ','
, rowterminator = '\n'
,datafiletype = 'char'
,codepage = 'acp'
)
Then the data is inserted correctly.
But if the file has less columns than the table def. i.e.:-
1,2
3,4
I get the following error.
Server: Msg 4864, Level 16, State 1, Line 1
Bulk insert data conversion error (type mismatch) for row 1, column 2 (b).
So the question is, is it possible to insert into a table with more columns than in the file? In the example above the row terminator
does not seem to be being picked up. I tried with lots of different row terminators but had no luck.
Regards,
Andy Jones
Edited by - andyj93 on 06/27/2002 02:52:30 AM
.
June 27, 2002 at 9:18 am
I don't believe you can do this with bulk insert, but you can with something called the bcp utility. You can use this to create a format file, and then use this format file to do your imports. You can do a search for bcp in SQL Server books online for more info.
June 27, 2002 at 9:37 am
The problem I have is that the file is not in any pre-defined format for example it could be:-
1,2,3,4
1
1,2
1,2,3,4,5
so there is a random (known maximum - say 5) number of fields on a line. I want to load this data diretly into a table as a matrix, so a select on the table would give:-
1 2 3 4 <null>
1 <null> <null> <null> <null>
1,2 <null> <null> <null>
1,2,3,4,5
This is possible using a DTS transform data task, I was investigating using bulk insert but I'll just revert to the DTS method.
Regards,
Andy Jones
.
June 28, 2002 at 10:38 am
Can you add an extra column that always has a value?
IE
1,2,3,4,,x
1,,,,,x
1,2,,,,x
1,2,3,4,5,x
This way the delimitters will always be there. I use this when using bulk insert with Excel saved CSV files to force the delimiters, otherwise they are not there sometimes.
July 3, 2002 at 1:30 am
July 4, 2002 at 3:33 am
rewrite your table to have 1 column and the bulk insert to be tab seperated, then run a process on the result table to split out the comma seperated list in the one field.
Otherwise use a DTS transform
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply