Bulk Insert Problem

Question

Post reply

Bulk Insert Problem

nagarajan.santhan

Valued Member

Points: 53
More actions
September 7, 2010 at 6:31 am

#239949

Hi
I have a text file having new records with random columns...
say example...
1,LASTNAME,FIRSTNAME,AGE,SEX,DOB
2,LASTNAME,FIRSTNAME
3,LASTNAME,FIRSTNAME,AGE,SEX
4,LASTNAME,FIRSTNAME,AGE,SEX,DOB
5,LASTNAME,FIRSTNAME,AGE
6,LASTNAME
When using BULK INSERT command it says "Bulk insert data conversion error (type mismatch) for row 2, column 4 (AGE)"
How can resolve this issue...
Thanks in advance
Regards
Nagarajan

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Madhivanan-208264 SSCertifiable Points: 7516 More actions · Answer 1

It is not possible to import data where there are different number of columns

Madhivanan

Failing to plan is Planning to fail

nagarajan.santhan Valued Member Points: 53 More actions · Answer 2

nagarajan.santhan

Valued Member

Points: 53

September 7, 2010 at 6:36 am

#1217274

Thanks Madhivanan 🙁

steve-893342 SSCarpal Tunnel Points: 4439 More actions · Answer 3

nagarajan.santhan (9/7/2010)
Hi
I have a text file having new records with random columns...
say example...
1,LASTNAME,FIRSTNAME,AGE,SEX,DOB
2,LASTNAME,FIRSTNAME
3,LASTNAME,FIRSTNAME,AGE,SEX
4,LASTNAME,FIRSTNAME,AGE,SEX,DOB
5,LASTNAME,FIRSTNAME,AGE
6,LASTNAME
When using BULK INSERT command it says "Bulk insert data conversion error (type mismatch) for row 2, column 4 (AGE)"
How can resolve this issue...
Thanks in advance
Regards
Nagarajan

Try this

IF NOT OBJECT_ID('tempdb.dbo.#t', 'U') IS NULL DROP TABLE #t;

CREATE TABLE #t(BulkColumn VARCHAR(8000));

BULK INSERT #t FROM 'C:\YourFile.txt';

WITH cteTally (N) AS

(

SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0))

FROM master..syscolumns

)

SELECT [1] AS ID, [2] AS LASTNAME, [3] AS FIRSTNAME, [4] AS AGE, [5] AS SEX, [6] AS DOB

FROM #t

CROSS APPLY

(

SELECT [1],[2],[3],[4],[5],[6]

FROM

(

SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)),

SUBSTRING(BulkColumn + ',', N, CHARINDEX(',', BulkColumn + ',', N) - N)

FROM cteTally

WHERE N < LEN(BulkColumn) + 2 AND SUBSTRING(',' + BulkColumn + ',', N, 1) = ','

) AS Z (ROW, Value)

PIVOT (MAX(Value) FOR ROW IN ([1],[2],[3],[4],[5],[6])) AS pvt

) AS Y;