Is it possible to write a format file that skips a few bytes of header in data file?

[Bob]

Hi,

I am trying to use BULK INSERT with format file. All of our data has
few bytes of header in the data file which I would like to skip before
doing BULK INSERT.

Is it possible to write format file to skip these few bytes of
header before doing BULK INSERT? For example, I have a 1 GB data file
with 1000 byte header. Except for first 1000 bytes, rest of the data is
good for BULK INSERT.

Thanks in advance. Sorry if it is really a dumb question as I am new
to BULK INSERT and practicing still.

Bob

[Erland Sommarskog]

Re: Is it possible to write a format file that skips a few bytes of header in data file?

Bob (ballampa@i-o.com) writes:[color=blue]
> I am trying to use BULK INSERT with format file. All of our data has
> few bytes of header in the data file which I would like to skip before
> doing BULK INSERT.
>
> Is it possible to write format file to skip these few bytes of
> header before doing BULK INSERT? For example, I have a 1 GB data file
> with 1000 byte header. Except for first 1000 bytes, rest of the data is
> good for BULK INSERT.
>
> Thanks in advance. Sorry if it is really a dumb question as I am new
> to BULK INSERT and practicing still.[/color]

The answer is - maybe. I should hasten to add that with the numbers given,
the prospects are bleak.

BULK INSERT/BCP is a quite powerful tool, but unfortunately this quite
common scenario is nothing it has any support for. Bulk-load looks at
the file as a stream of characters that is an even repetition of the
fields in the format file. You call tell it to start loading at row
2, but that really means to skip the first record bulk-load is able
to identify.

So the only possibility to skip a header, is if it can be part of a
header. To illustrate here is an example where it is possible. We
have a file that looks like this:

fielda,fieldb,f ieldc,lastfield
"data",15," more data",,
"next data",125,"what data",,

Here you can skip the header. This is because when you write the
format file, the first field in the file is terminated by the "
and you say that this field is not be loaded. So the header will
just become part of the first field for the first record.

As you see that this works is more or less by chance. Had the first
field in been numeric (or for some other reason unquoted), we would
not have been able to skip the header. Then again, this file would
work too:

fielda,fieldb,f ieldc,lastfield
data,15,more data,,
next data,125,what data,,

Because here the , is the single delimiter, and we can simply skip
the first record in the file.

Your best bet may be to write a program that reads the file, skips
the header, and then bulk-loads from variable using the bulk-load
API. But that requires knowledge of programming in C or some other
language.



--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Ross Presser]

Re: Is it possible to write a format file that skips a few bytes of header in data file?

On Tue, 22 Mar 2005 22:28:18 +0000 (UTC), Erland Sommarskog wrote:
[color=blue]
> Bob (ballampa@i-o.com) writes:[color=green]
>> I am trying to use BULK INSERT with format file. All of our data has
>> few bytes of header in the data file which I would like to skip before
>> doing BULK INSERT.
>>
>> Is it possible to write format file to skip these few bytes of
>> header before doing BULK INSERT? For example, I have a 1 GB data file
>> with 1000 byte header. Except for first 1000 bytes, rest of the data is
>> good for BULK INSERT.
>>
>> Thanks in advance. Sorry if it is really a dumb question as I am new
>> to BULK INSERT and practicing still.[/color]
>
> The answer is - maybe. I should hasten to add that with the numbers given,
> the prospects are bleak.
>
> BULK INSERT/BCP is a quite powerful tool, but unfortunately this quite
> common scenario is nothing it has any support for. Bulk-load looks at
> the file as a stream of characters that is an even repetition of the
> fields in the format file. You call tell it to start loading at row
> 2, but that really means to skip the first record bulk-load is able
> to identify.
>
> So the only possibility to skip a header, is if it can be part of a
> header. To illustrate here is an example where it is possible. We
> have a file that looks like this:
>
> fielda,fieldb,f ieldc,lastfield
> "data",15," more data",,
> "next data",125,"what data",,
>
> Here you can skip the header. This is because when you write the
> format file, the first field in the file is terminated by the "
> and you say that this field is not be loaded. So the header will
> just become part of the first field for the first record.
>
> As you see that this works is more or less by chance. Had the first
> field in been numeric (or for some other reason unquoted), we would
> not have been able to skip the header. Then again, this file would
> work too:
>
> fielda,fieldb,f ieldc,lastfield
> data,15,more data,,
> next data,125,what data,,
>
> Because here the , is the single delimiter, and we can simply skip
> the first record in the file.
>
> Your best bet may be to write a program that reads the file, skips
> the header, and then bulk-loads from variable using the bulk-load
> API. But that requires knowledge of programming in C or some other
> language.[/color]

A DTS package can specify the starting and ending row numbers in the source
file that will be bulk inserted. So if the header to be skipped ends with
the same line delimiter, or if its length is an exact multiple of the
(fixed format) row length, then this can do the trick.

Of course, this only confirms Erland's last paragraph - DTSRUN.exe is
itself a program that uses the bulk-load API, presumably programmed in C :)

[Bob]

Re: Is it possible to write a format file that skips a few bytes of header in data file?

It looks like there is no easy way to do this. I was hoping to see some
kind of parameter to specify the starting position for bulk insert or
bcp.

Unfortunately, the method may not work for me because I can't have
header in the first field and the data I am importing is binary. All I
know is to skip a constant number of bytes at the beginning before
reading in the data.

Thanks for taking time.

[Bob]

Re: Is it possible to write a format file that skips a few bytes of header in data file?

Thanks for your time. From the replies, I understood that there is no
simple answer. So, I am trying to get the clients delivering the data
to strip off this header.

[Bob]

Re: Is it possible to write a format file that skips a few bytes of header in data file?

Thanks for your time. From the replies, I understood that there is no
simple answer. So, I am trying to get the clients delivering the data
to strip off this header.

[Craig Kelly]

Re: Is it possible to write a format file that skips a few bytes of header in data file?

"Bob" wrote:
[color=blue]
> It looks like there is no easy way to do this. I was hoping to see some
> kind of parameter to specify the starting position for bulk insert or
> bcp.
>
> Unfortunately, the method may not work for me because I can't have
> header in the first field and the data I am importing is binary. All I
> know is to skip a constant number of bytes at the beginning before
> reading in the data.
>
> Thanks for taking time.[/color]

There's no easy way with DTS or BCP (as far as I know), but there are
command line tools that can do this. If you have Cygwin installed or have a
*nix-like environment, the following will get you a new file assuming that
the header is 1000 bytes:

tail --bytes=+1001 input.bin > output.bin

This reads everything in input.bin starting at byte 1001 and writes it
output.bin. Then you can run your import on output.bin. There may be a way
to do this with the native Windows command line but I don't know what it is.

Craig

[Erland Sommarskog]

Re: Is it possible to write a format file that skips a few bytes of header in data file?

Craig Kelly (cnkelly.nospam @nospam.net) writes:[color=blue]
> There's no easy way with DTS or BCP (as far as I know), but there are
> command line tools that can do this. If you have Cygwin installed or
> have a *nix-like environment, the following will get you a new file
> assuming that the header is 1000 bytes:
>
> tail --bytes=+1001 input.bin > output.bin
>
> This reads everything in input.bin starting at byte 1001 and writes it
> output.bin. Then you can run your import on output.bin. There may be a
> way to do this with the native Windows command line but I don't know
> what it is.[/color]

TAIL is in the Windows Resource Kit for Win 2003. So far so good.

There is however an extra challenge in the fact that Bob's data file
is 1 GB.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp