473,231 Members | 1,503 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,231 software developers and data experts.

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

Jul 23 '05 #1
7 2696
Bob (ba******@i-o.com) writes:
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.


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,fieldc,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,fieldc,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, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #2
On Tue, 22 Mar 2005 22:28:18 +0000 (UTC), Erland Sommarskog wrote:
Bob (ba******@i-o.com) writes:
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.


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,fieldc,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,fieldc,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.


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 :)
Jul 23 '05 #3
Bob
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.

Jul 23 '05 #4
Bob
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.

Jul 23 '05 #5
Bob
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.

Jul 23 '05 #6
"Bob" wrote:
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.


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
Jul 23 '05 #7
Craig Kelly (cn************@nospam.net) writes:
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.


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, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #8

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

6
by: bart_nessux | last post by:
Hello, I have some Macbinary files on a PC. I want to recursively read these files and remove the first 128 bytes of the files if they contain the macbinary header info. I know how to read...
16
by: Michael | last post by:
I have a data application in a2k that I need to create two fixed width text files and then combine them to a single file The first file is header information and the second is transaction data. ...
4
by: Neil10365 | last post by:
I wonder if someone can help me with a small conundrum I am having. This is what I want to achieve: Scenario -------- Each week, I import an excel spreadsheet called Week1.xls into an access...
14
by: Vertilka | last post by:
I need to read binary data file written by C++ program, using my C# application. How do i marshal the bytes i read with my C# code to .NET types. The data is numbers (integers float doubles...
5
by: philip | last post by:
Here is some lines of code than I wrote. You can copy/paste theis code as code of form1 in a new project. My problem is this one : I try to write in a file a serie of bytes. BUT some bytes...
2
by: AB | last post by:
Hi to all, I have a problem about a importation of a file *.csv with SQL Server, through a bulk insert, called in a store procedure that a c# sw calls. This is the description of the error:...
6
by: Jasper | last post by:
Hi, Maybe this is off-topic, but perhaps you can help. I'm looking for ideas on how to parse a data file. I dont know XML but I know it parses data in text format. I have a structured data...
2
by: phpnoob | last post by:
I have a php script that processes a form and then posts the user input to a data file on the server in a comma delimited format. For simplicity call the file "data.csv." The script is working...
5
by: dm3281 | last post by:
Hello, I have a text report from a mainframe that I need to parse. The report has about a 2580 byte header that contains binary information (garbage for the most part); although there are a...
0
by: VivesProcSPL | last post by:
Obviously, one of the original purposes of SQL is to make data query processing easy. The language uses many English-like terms and syntax in an effort to make it easy to learn, particularly for...
3
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 3 Jan 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). For other local times, please check World Time Buddy In...
0
by: jianzs | last post by:
Introduction Cloud-native applications are conventionally identified as those designed and nurtured on cloud infrastructure. Such applications, rooted in cloud technologies, skillfully benefit from...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.