Import and export a text document

[Odysseus]

I have a text input file that I upload to a bespoke programme to produce depreciation values. I have a need to re-create this text file with amendments.

So my thoughts are in import the text file into excel, create template with look ups and links then make my necessary changes and then export / save the file back into a text file in order to upload it.

However when I open the text file I get an error message saying the range is outside the maximum range and I will lose some data, this isn’t a major problem as its just filled with Zeroes and is not needed. However the text documents MUST match specific criteria which is below.

However I can’t find a way to re-create the offsets and make each row 2934 bytes of data.

I’ve attached and en example of and original document.

Can this be done and where would I start?

Each subsequent record contains 2934 bytes of data and is terminated by CR/LF characters.

The 14 Periods Covered 6, 12, 18, 24, 30, 36, 42, 48, 54, 60, 66, 72, 78 and 84 Months.
NOTE : Not all of the periods may be used.


OFFSET LENGTH DESCRIPTION

1-20 20 Item Code
21-25 5 Item Identification Number
26-29 4 Not used
30-31 2 Not used
32-35 4 Dep point/Kms 1
36-175 10*14 Periods 1st Dep point/Kms Values

176-179 4 Dep point/Kms 2
180-319 10*14 Periods 2nd Dep point/Kms Values

320-323 4 Dep point/Kms 3
324-463 10*14 Periods 3rd Dep point/Kms Values

464-467 4 Dep point/Kms 4
468-607 10*14 Periods 4th Dep point/Kms Values

608-611 4 Dep point/Kms 5
612-751 10*14 Periods 5th Dep point/Kms Values

752-755 4 Dep point/Kms 6
756-895 10*14 Periods 6th Dep point/Kms Values

896-899 4 Dep point/Kms 7
900-1039 10*14 Periods 7th Dep point/Kms Values

1040-1043 4 Dep point/Kms 8
1044-1183 10*14 Periods 8th Dep point/Kms Values

1184-1187 4 Dep point/Kms 9
1188-1327 10*14 Periods 9th Dep point/Kms Values

1328-1331 4 Dep point/Kms 10
1332-1471 10*14 Periods 10th Dep point/Kms Values

1472-1475 4 Dep point/Kms 11
1476-1615 10*14 Periods 11th Dep point/Kms Values

1616-1619 4 Dep point/Kms 12
1620-1759 10*14 Periods 12th Dep point/Kms Values

1760-1763 4 Dep point/Kms 13
1764-1903 10*14 Periods 13th Dep point/Kms Values

1904-1907 4 Dep point/Kms 14
1908-2047 10*14 Periods 14th Dep point/Kms Values

2048-2051 4 Dep point/Kms 15
2052-2191 10*14 Periods 15th Dep point/Kms Values

2192-2195 4 Dep point/Kms 16
2196-2335 10*14 Periods 16th Dep point/Kms Values

2336-2339 4 Dep point/Kms 17
2340-2479 10*14 Periods 17th Dep point/Kms Values

2480-2483 4 Dep point/Kms 18
2484-2623 10*14 Periods 18th Dep point/Kms Values

2624-2627 4 Dep point/Kms 19
2628-2767 10*14 Periods 19th Dep point/Kms Values

2768-2771 4 Dep point/Kms 20
2772-2911 10*14 Periods 20th Dep point/Kms Values

2912-2926 15 New rice (##########.####)
2927-2934 8 Date of last new price change

The final record is a footer record, typically
TOTAL RECORDS= 2871