S
SirPoonga
I am trying to convert data that was given to me in a text file to a
database. The text file was generated from a main frame system. Each
line is a record where fields started and ended at specific character.
I have splitted the data as needed.
As of now I treated all fields as text.
I have a field that should be converted to a numeric field. A number,
as text, looks like '00000012'. That's for positive numbers. For
negative, a -12 would look like '0000001r'. You might think, why use
r? I figured out how it determines negative. For the 1s digit, p=0,
q=1, r=2, ..., y=9. If you look at the ascii hex value you probably
can figure out why you start with p. P in hex is 50, y is 59. No
other letter has a hex of X0 where X is anything.
SO, I need to convert these values to numbers so I can do calculations
on them. I am wondering if there is a way to replace, say an r, with
the number 2, and then make the value negative?
Or is it possible to convert during import? Actually, it is. I had to
make my own import function to go through each line of hte text file
and pull out the field according to start and end character. So I need
to convert during import. One might think at this moment "why not just
use fixed width text import wizard". Because this file contains many
different record types that have different record layout. I created a
table that stores record layout info, so as each line of the file comes
in I check what record type it is. Then I query the record layout
table for fields, starts, and ends. Then I output the data into
appropiate tables for each record type.
i suppose I just answered my own question. However, is there a way to
do this after import?
database. The text file was generated from a main frame system. Each
line is a record where fields started and ended at specific character.
I have splitted the data as needed.
As of now I treated all fields as text.
I have a field that should be converted to a numeric field. A number,
as text, looks like '00000012'. That's for positive numbers. For
negative, a -12 would look like '0000001r'. You might think, why use
r? I figured out how it determines negative. For the 1s digit, p=0,
q=1, r=2, ..., y=9. If you look at the ascii hex value you probably
can figure out why you start with p. P in hex is 50, y is 59. No
other letter has a hex of X0 where X is anything.
SO, I need to convert these values to numbers so I can do calculations
on them. I am wondering if there is a way to replace, say an r, with
the number 2, and then make the value negative?
Or is it possible to convert during import? Actually, it is. I had to
make my own import function to go through each line of hte text file
and pull out the field according to start and end character. So I need
to convert during import. One might think at this moment "why not just
use fixed width text import wizard". Because this file contains many
different record types that have different record layout. I created a
table that stores record layout info, so as each line of the file comes
in I check what record type it is. Then I query the record layout
table for fields, starts, and ends. Then I output the data into
appropiate tables for each record type.
i suppose I just answered my own question. However, is there a way to
do this after import?