Transposing data from vertical format to tabular

  • Thread starter Thread starter IKMD66
  • Start date Start date
I

IKMD66

Hi,

I need to convert a number of files (imported), that contain data in a
vertical format, to a tabular / datasheet format in order to utilise within a
database.

The files contain between 5 and 10K rows therefore it is not practical to do
this manually.


NOTE: If the data wraps below cut and paste this to Notepad or some other
text editor in order to view it correctly!

An example of the raw data is below:

EGIN_SEGMENT_SECTION
BEGIN_IDOC ORDERS05
BEGIN_SEGMENT E2EDK01005
SEGMENTTYPE E1EDK01
LEVEL 01
STATUS MANDATORY
LOOPMIN 0000000001
LOOPMAX 0000000001
BEGIN_FIELDS
NAME ACTION
TEXT Action code for the whole EDI message
TYPE CHARACTER
LENGTH 000003
FIELD_POS 0001
BYTE_FIRST 000064
BYTE_LAST 000066


I dont need the field labels from each row but their values - taking the
fields starting at NAME eg. I would have columns as follow:

NAME TEXT TYPE LENGTH FIELD_POS BYTE_FIRST BYTE_LAST

The data I want to be manipulated like the example below:

ACTION Action code for the whole EDI
message CHARACTER 000003 0001 000064 000066
KZABS Flag: order acknowledgment required CHARACTER 000001 0002 000067 000067

Can anyone give me some pointers?

Thanks in advance.

Kirk
 
I would create an empty table with the correct field names. Then
read in the text file (check out the "FileSystemObject" and "TextStream"
objects), search for the field names, discard the schema info, and place
the actual field data into the correct fields.

Alternatively you might try editing the file in MS Word which has some
powerful search and replace functions you could use to reformat the text
file.
 
Back
Top