dman19691
asked on
Convert ASCII file with several rows of data that are inconsistent to a column format in Excel
I have an ASCII file that was generated in Cobol. Trying to convert the rows of data into column format in Excel 2013. The problem is that there are different number of rows for each record. Some records may be 15 lines while another may have 20.
ASKER
5Teveo,
I will have to check in see if the data is confidential. But I was able to put every record that started with an 01 and changed it to a unique identifier of XX. So that the start of a new record what be identified with XX. I don't know if that makes sense?
I will have to check in see if the data is confidential. But I was able to put every record that started with an 01 and changed it to a unique identifier of XX. So that the start of a new record what be identified with XX. I don't know if that makes sense?
Just make a pattern similar to what you need so enable a better sample script... that's my thought
ASKER
Here is a sample of the data. The first two positions in the lines of data represent a record. For example 01, 02, 03 up to 15 represent one record. I will attach a sample of the data.
sample.txt
sample.txt
Upon quick review... it appears a data definition may be needed...
I can guess row types 01-?? thru next 01 are all associated with 1 record. I can also see multiple record rows concatenation for a text type description but it appears some other array type work is occurring within record.
Do you have a data definition describing how data is defined in variable length record in cobol? that's may be what you need to get better answer/script?
A vb script to join records will not help you unless script knows how to handle each type.
make sense?
I can guess row types 01-?? thru next 01 are all associated with 1 record. I can also see multiple record rows concatenation for a text type description but it appears some other array type work is occurring within record.
Do you have a data definition describing how data is defined in variable length record in cobol? that's may be what you need to get better answer/script?
A vb script to join records will not help you unless script knows how to handle each type.
make sense?
ASKER
If you go to this link it has the layout.
http://www.rrc.state.tx.us/media/1250/drillingpermitmasterpluslatitudeslongitudes_oga049m_july1.pdf
http://www.rrc.state.tx.us/media/1250/drillingpermitmasterpluslatitudeslongitudes_oga049m_july1.pdf
since you have very different layouts for these various record types, please do a manual parsing of the data and post that workbook.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks 5teveo and aikimark. I figured this would not be easy since the data is in Cobol and has several lines, but you pointed me in the right direction. Thanks again.
The better formats for this data would be:
* mark-up (XML, JSON, YAML)
* relational database with separate tables for each kind of record (01-15)
* NoSQL database (most likely JSON documents)
If you want to save this in Excel, you should have separate tabs for each kind of record (01-15)
Note: When parsing the data, the primary key of the 01 (or 02) record must be added to the 03-15 records as a foreign key, allowing you to join the tables for your queries.
* mark-up (XML, JSON, YAML)
* relational database with separate tables for each kind of record (01-15)
* NoSQL database (most likely JSON documents)
If you want to save this in Excel, you should have separate tabs for each kind of record (01-15)
Note: When parsing the data, the primary key of the 01 (or 02) record must be added to the 03-15 records as a foreign key, allowing you to join the tables for your queries.
Are there any patterns? Record row tags / headings?
You will need to append multi-row records into 1 row for excel which will require a macro - in my opinion.