how to split and upload a text file from sql in sql

See more:

SQL-Server-2005

SQL-Server

my code is that

create procedure sp_textupload
go
bulk insert tablename from 'D:\text.txt'
with
(rowterminator='\n',
field terminnator='|'
go
)

so here i found output is correct if the database table column mataches with the text field splitted with delimitor else
Else the scenario are

if database column < delimited txtfile
like Database column=> C1 C2 C3 and delimited textfile contents=> f1|f2|f3|f4
output=> f1 f2 f3|f4

but my expected result : f1 f2 f3 (Rejecting |f4 value)

like Database column=> C1 C2 C3 and delimited textfile contents=> f1|f2
f21|f22 (the next line of text)
output=> f1 f2f21 f22

but my expected result : f1 f2 NULL
f21 f22 Null