Transfer table to fixed length text records

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a table the consists of two fields that together make 300 bytes. When
I export this delimited file to text there appear to be 300 characters.
However when this text file is imported to a well established package, it is
validated for having records that are 300 bytes long. Needless to say my text
file fails this check. I have reduced my table size to allow for a line feed
or carriage return but it still fails the check. The strange thing is that
even though I have reduced the record length the output text files are still
the same overall size. Are there some hidden characters in the text file to
take into consideration here?
 
Look at it and see. Open the text file in a preferably stupid text
editor, like notepad. You don't want anything trying to "fix" the
results. See what you get.

A couple things pop to mind.
150 + 150 is going to be perhaps 307 bytes, two fields, two pairs of
quotes, a comma or tab, carriage return, and linefeed.

Are you sure this "well established" package is expecting a delimited
file, not fixed length, and are you sure that you are using the exact
same delimiters, for field, record and text, that this package expects?
They can be very picky sometimes.
 
Hi Phil,

I'm sorry I must have had a brainstorm! It's a fixed length length file not
delimited. I've already tried your suggestion and in notepad the cursor sits
on the 300th character and when you move the cursor one position to the
right, the line feed kicks in and moves you to the beginning of the next
line. What I don't understand is that the text file size stays the same even
though I have reduced the record size to say 298 or 299 to allow for line
feed or carriage return characters. I've got over 900 records so there should
be some variation in overall file size, I would have thought. There must be
some hidden filler or control characters somewhere.
 
1) Are you looking at the size of the file as reported in Windows Explorer
(which is approximate) or the exact size in bytes (e.g. in its properties
sheet)?

2) Check the encodings (code pages) involved. The one your computer uses for
the export may not be what the other computer expects, and this could cause
it to detect the wrong number of characters per line.
 
ok. Fixed length means NO delimiters of any kind. Your data is simply
one massive string of characters. NO RECORD DELIMITERS. In your
example, the import would take the first 150 characters to field1, the
next 150 character to field2, the next 150 characters to field1 of the
next record...
In a fixed length file, there is nothing at all to define fields or
records except character position, and nothign except data.

If you are opening a fixed length file in notepad, and seeing a line
break every three hundred character, it is NOT a fxed length file. SO
where your import is expecting to see data in 300 character blocks, and
a file length which is an exact multiple of 300, you are getting records
that are 301 or 302, and not the proper length. Plus the first
character of the second record is going to be a control character, which
is probably not what the program is expecting.

I would say something is going wrong with the export.
What you might try is to create a new table to import the data into.
Field1, (150), Field2, (150) Field3, (1) or (2) or (3) characters.
I don't know whether both programs are Access or not, but once you have
an export file to play with, try to load it into the new table in
Access. The idea is for Field3 to take the extra control characters,
and there may be one or two, so you will have to try it each way. If
that works, then you can go from there. What program are you EXPORTING
from? What method?
 
Yes, this needs to be settled too. Access's "fixed-width" export
facility creates files in which each record is a fixed length (e.g. 300
characters) and is terminated by the two characters CR LF. So a file
containing three 300-character records is 906 bytes long (assuming one
byte per character). There's no way of specifying a different record
terminator.

So if the other application expects a file such as Phil describes, in
which the first record starts at offset 0, the next at offset 300 (or
600 if we're using two bytes per character), and so on with no record
separators - the standard Access export routine can't create it.
 
Back
Top