BCP/Bulk Insert assistance
-
Good Afternoon Everyone,
I am noticing some odd behavior with using BCP to import a fixed width file from one of our customers. BCP seems to be refusing to import at the line I am specifying as FIRSTROW and instead starts at the line after.
Here is a sample of what I am using on the command line and in the Bulk Insert statement. Unfortunately, I cannot show any data from the host file since it is sensitive information.
BCP:
bcp.exe Scratch.dbo.ObsoleteParts in f:\path\to\file.txt -ff:\path\to\FormatFile.fmt -
F2 -S(local) -T
BULK INSERT:
BULK INSERT Scratch.dbo.ObsoleteParts FROM 'f:\path\to\file.txt'
WITH (FORMATFILE = 'f:\path\to\FormatFile.fmt',FIRSTROW=2)
Regardless of what Row 2 is, the import always begins at row 3. I have looked at the file through a text editor and there are no hidden characters in the file. Has anyone run into this before?
Sample layout of the file is A header row, several data rows (500,000+) and a trailing row that summarizes the number of data rows.
Any help would be appreciated.
-
Sorry if this is appearing twice.....
Is this a production job that is all of the sudden having problems, or are you working on developing the BCP job from scratch?? There must be a problem with the column header whether you can see it in the file or not. How is your import file being generated? How many rows are you attempting to insert? If this is a production job that is having problems, what else has changed? If this is a development project, try manually rebuilding your import file in notepad with only the column name header and a few rows of data.
I have seen where files have been built with garbage in them, but you cannot see it when viewing it from notepad/wordpad. In my case, a mainframe was creating the file and inserting some strange (non-viewable) hex characters in the file at the end of the rows.
-
This is a new process, new file, from a client generated from a mainframe. I viewed the file in a hex editor (Ultra Edit) and did not see any high ascii or other unusual characters in the file.
I tried your suggestion to rip out the header row and that worked. Thank you. Now I just have to get the client to see if there is anything on thier end.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply