Bulk insert

Hi,

I need to load a file of which the row terminator is only a linefeed (0A). How can I load this file? BULK INSERT only has \n (0D0A) and \r (0D) as row terminators. If you use \n, BULK INSERT od BCP always add the 0D.

tnx.
LVL 2
erdeAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
dewpatConnect With a Mentor Commented:
You can create a command with the LF included :

declare @command varchar(1000)
declare @lf char(1)
select @lf=char(10)
select @command='
BULK INSERT test
   FROM ''d:\temp\testfile''
   WITH
     (
        FIELDTERMINATOR = ''\t'',
        ROWTERMINATOR = '''+@lf+'''
      )'
exec (@command)

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
see this article:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_impt_bcp_0fqq.asp
<...>
When you use bcp interactively and specify \n (newline) as the row terminator, bcp prefixes the \r (carriage return) character automatically.
<...>

I suggest that you specify the parameters using -f<formatfile> options:

-f format_file

Specifies the full path of the format file that contains stored responses from a previous use of bcp on the same table or view. Use this option when using a format file created with the format option to bulk copy data in or out. Creation of the format file is optional. After prompting you with format questions, bcp prompts whether to save the answers in a format file. The default file name is Bcp.fmt. bcp can refer to a format file when bulk copying data; therefore, reentering previous format responses interactively is not necessary. If this option is not used and -n, -c, -w, -6, or -N is not specified, bcp prompts for format information


CHeers
0
 
erdeAuthor Commented:
Works fine. Thanks.
0
 
brokeMyLegBikingCommented:
solved my problem as well!

-grateful bystander
0
 
DanielcmorrisCommented:
very slick!  thanks dewpat
0
All Courses

From novice to tech pro — start learning today.