bulk insert with format file. no error; just not working??

Last Post 04 Jun 2012 02:01 PM by gunneyk. 3 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
new2sql
New Member
New Member

--
04 Jun 2012 12:10 PM
ok... the csv file is the same structure as the table it's self.
the format file is a basic format file produced via bcp from the destination table.

if there was an error i thought i would see it, but i'm baffled as all i get is
the usual ( 0 rows affected )

any way here is the script:

/************************************************************/
bulk insert MyDestinationTable
from 'H:\SourceFile\MySourceFlatFile.csv'
with
(
firstrow = 2
--maxerrors = 0
, fieldterminator = ','
, keepnulls
, ROWTERMINATOR = '\n'
formatfile = 'C:\FormatFiles\MySourceFormatFile.fmt'
);

Output:
(0 row(s) affected)
/************************************************************/

i believe the syntax is correct, but just wondering if you guys see
anything quirky.

thanks in advance
gunneyk
New Member
New Member

--
04 Jun 2012 12:51 PM
Why do you need the format file? What is special that you need to specify in a format file? Also why are you specifying the field and row terminators in the bcp syntax if you are using a format file? Shouldn't those already be int he format file?
new2sql
New Member
New Member

--
04 Jun 2012 01:05 PM
this is true... i didn't think they were needed so i tried both with and without terminators. still nothing. i mean not even an error? i've checked out many sample scripts online, and my approach seems to be atypical.

i am using a format file because there are minor differences across various columns across a number of tables. altering an insert script per each one is hard to maintain for the group. a text file is easier for them to alter.

the over all goal is to create a script that loads each and every table regularly, and any adjustments on the type of terminator or what have you can be made via the format files, and not within the actual script that is referencing it.

gunneyk
New Member
New Member

--
04 Jun 2012 02:01 PM
I would start with something as simple as possible first and work up to the actual full file contents and see where it stops working. By that I mean take out things such as the header in the file. I assume since you have firstrow = 2 that you have some kind of header. I fin'd them to be very problematic so edit one of the files and remove the header etc. Also take a look at the file with a good editor (not notepad) and make sure there are no hidden CR, LF etc. in there.
You are not authorized to post a reply.

Acceptable Use Policy