Read a text file

[apascual]

hi! i have a problem regarding reading a text file. i want to read a text file (per line) then insert it to a field. can anyone help? i used the bulk insert command but the problem with here is that the syntax inserts the whole data of the text file. i just want to get the per line data. pls help! tnx.

 

[JayKusch]

This may do the trick for you.

If you are trying to import into a table,"TableName" each line of text in a file,"TextFile.txt" that is in the root directory,"C:\" then ...


INSERT INTO TableName

EXEC Master.dbo.xp_cmdshell 'type C:\TextFile.txt'


A couple of quick notes though ...

1. You will need to change the path in the "type" command if the text file does not reside in your root directory

2. Be careful, in a security sence, in using the xp_cmdshell SP.

Hope this helps



Thanks

J. Kusch

 

[apascual]

wat if the text file does not reside at the sql server. the file is stored in another pc. i dnt want to use mapped dir, coz sometimes others may disconnect it or sometimes othres may not enter the username & password during reconnection. do we have other ways to do this? m not familiar w/ UNC (universal naming convention). does unc has something to do with this?

 

[JayKusch]

You could use a UNC if permissions are set right. The code may look similiar to ...

INSERT INTO TableName

EXEC Master.dbo.xp_cmdshell

'type \\ServerName\ShareName\TextFile.txt'



Thanks

J. Kusch