How to use Bulk Insert

[amerbaig]

I want to Load a text file in a three column Table with three fields, all comma separated like:-

923335100025,Amer,Baig
923335100017,Asif,Qazi
923335100089,Maz
923335120084,Tahir,Ch
923335120085,Waseem


My format file is

7.0
3
1 SQLCHAR 0 20 "," 1 msisdn
2 SQLCHAR 0 20 "," 2 name
3 SQLCHAR 0 20 "\r\n" 3 lname


The problem is that the text file may contain all fields or not.


How to load such file. I have struggled a lot please help


Regards,

Amer

 

[NoCoolHandle]

Bulk Insert DatabaseName.dbo.TableName From 'C:\yourFile.txt'
With
(FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n')

And forget about BCP

 

[amerbaig]

It gives me error like ----

Server: Msg 4832, Level 16, State 1, Line 1
Bulk Insert: Unexpected end-of-file (EOF) encountered in data file.

When I chnage \n with \n\r the it then it appears

Server: Msg 4863, Level 16, State 1, Line 1
Bulk insert data conversion error (truncation) for row 1, column 3 (lname).

This has been appearing befor.

Please help

Regards,

Amer

 

[hmckillop]

You have specified a format file which doesnt match the data file.
I know this seems like an obvious answer, but if SQL expects 3 fields with two seperators (,) and you dont give it them, it will fail.
Is there no way you can get the output file to include seperators even if the data doesnt exist?