I receive reports formatted as a .txt (flat) file.
Problem: I have to develop a process to manage the .txt files so the data can be separated into its own fields. The layout of the output is as follows:
Date Time User Status Customer Name ID#
06/04/07 15:51 MRF C PTRSNEEO,TBYET L 01234567
Closed By:MRF acvd per xx adv xx of prices on her xxxx XYZASSX
TBLTSXS 75ZZ 90/09ds &5.35 XMEPRAZLE CCC 20XX
90/90ds &2.15 UTZXLPIES EX CCC 100XX 90/09ds &2.15
TYERLXP 0.125XX (TPSDTRM) 90/90ds &2.15 MNTYIMPS
90/90ds &2.15 XYZCNITSASLS TTT 20XX n/s gave # to
call back mrf
1) The layout of the flat .txt file does not allow it to be parsed vertically.
I.e. Vertically: Date and Time overlap the Secondary User information.
The User initials are not aligned. Customer Name is directly over the documentation text and of a different string length.
When I try to "cut" the data by the main sections into columns (Date / Time / User / Status / Customer Name / ID#) to import into Access the text in the documentation fields is "cut" into those sections as well.
I want to be able to reconstruct the text sections that are split apart into a single "cell" that can be exported to Excel or used within Access.
2) Also, some records have 1 line of text, others 2...up to 8. Each individual line of text ends up in its own cell. How can I "code" Access to read to the end of the last line of text (last cell) of the record and then concatenate the text in those cells into one continuous string of text in a single cell?
At the end of each record in the report there is a space separating the records.
I realize this is long and complicated, but am lost and the volume of this type of work is increasing. Requests to IS to change the output of the system reports is low priority so I am stuck. Thank you for any help you may provide.
Problem: I have to develop a process to manage the .txt files so the data can be separated into its own fields. The layout of the output is as follows:
Date Time User Status Customer Name ID#
06/04/07 15:51 MRF C PTRSNEEO,TBYET L 01234567
Closed By:MRF acvd per xx adv xx of prices on her xxxx XYZASSX
TBLTSXS 75ZZ 90/09ds &5.35 XMEPRAZLE CCC 20XX
90/90ds &2.15 UTZXLPIES EX CCC 100XX 90/09ds &2.15
TYERLXP 0.125XX (TPSDTRM) 90/90ds &2.15 MNTYIMPS
90/90ds &2.15 XYZCNITSASLS TTT 20XX n/s gave # to
call back mrf
1) The layout of the flat .txt file does not allow it to be parsed vertically.
I.e. Vertically: Date and Time overlap the Secondary User information.
The User initials are not aligned. Customer Name is directly over the documentation text and of a different string length.
When I try to "cut" the data by the main sections into columns (Date / Time / User / Status / Customer Name / ID#) to import into Access the text in the documentation fields is "cut" into those sections as well.
I want to be able to reconstruct the text sections that are split apart into a single "cell" that can be exported to Excel or used within Access.
2) Also, some records have 1 line of text, others 2...up to 8. Each individual line of text ends up in its own cell. How can I "code" Access to read to the end of the last line of text (last cell) of the record and then concatenate the text in those cells into one continuous string of text in a single cell?
At the end of each record in the report there is a space separating the records.
I realize this is long and complicated, but am lost and the volume of this type of work is increasing. Requests to IS to change the output of the system reports is low priority so I am stuck. Thank you for any help you may provide.
