Read a text file and output a formated version of it

[docaia]

I am stuck with a text file in the following format:

E21776A0003 1 1 3244 35238.71N 74347.43E 585034.2 -13512.6 132.3 32130848 E21776A0003 1 1 3243 35238.69N 74346.82E 585015.4 -13513.2 132.6 32130858 E21776A0003 1 1 3242 35238.66N 74346.22E 584996.7 -13514.1 132.4 32130907 E21776A0003 1 1 3241 35238.63N 74345.61E 584977.9 -13515.1 132.7 32130917 E21776A0003 1 1 3240 35238.60N 74345.00E 584959.1 -13515.9 132.8 32130926 E21776A0003 1 1 3239 35238.57N 74344.39E 584940.2 -13516.8 132.8 32130936 E21776A0003 1 1 3238 35238.55N 74343.78E 584921.4 -13517.5 132.6 32130945 E21776A0003 1 1 3237 35238.52N 74343.17E 584902.7 -13518.3 133.2 32130954 E21776A0003 1 1 3236 35238.49N 74342.56E 584883.8 -13519.3 132.8 32131004 E21776A0003 1 1 3235 35238.45N 74341.95E 584865.1 -13520.4 133.1 32131013 E21776A0003 1 1 3234 35238.42N 74341.35E 584846.5 -13521.5 132.8 32131022 E21776A0003 1 1 3233 35238.38N 74340.74E 584827.6 -13522.4 132.4 32131032 E21776A0003 1 1 3232 35238.34N 74340.12E 584808.7 -13523.8 132.3 32131041 E21776A0003 1 1 3231 35238.30N 74339.51E 584789.8 -13525.0 132.8 32131050 E21776A0003 1 1 3230 35238.28N 74338.90E 584770.8 -13525.8 132.9 32131100 E21776A0003 1 1 3229 35238.25N 74338.28E 584751.9 -13526.6 132.5 32131109 E21776A0003 1 1 3228 35238.22N 74337.67E 584733.0 -13527.5 133.0 32131118 E21776A0003 1 1 3227 35238.19N 74337.06E 584714.4 -13528.2 132.5 32131126 E21776A0003 1 1 3226 35238.16N 74336.46E 584695.7 -13529.1 132.4 32131135 E21776A0003 1 1 3225 35238.14N 74335.85E 584677.0 -13529.8 132.7 32131144 E21776A0003 1 1 3224 35238.12N 74335.25E 584658.3 -13530.4 132.6 32131153 E21776A0003 1 1 3223 35238.10N 74334.64E 584639.7 -13531.0 132.6 32131202 E21776A0003 1 1 3222 35238.08N 74334.04E 584620.9 -13531.5 132.5 32131210 E21776A0003 1 1 3221 35238.07N 74333.42E 584602.0 -13531.8 132.6 32131219 E21776A0003 1 1 3220 35238.06N 74332.81E 584583.0 -13532.3 132.7 32131228 E21776A0003 1 1 3219 35238.05N 74332.20E 584564.2 -13532.6 132.7 32131237 E21776A0003 1 1 3218 35238.04N 74331.59E 584545.5 -13532.8 133.0 32131245 E21776A0003 1 1 3217 35238.03N 74330.99E 584526.8 -13533.0 132.4 32131254 E21776A0003 1 1 3216 35238.03N 74330.38E 584508.1 -13533.1 132.3 32131303 E21776A0003 1 1 3215 35238.02N 74329.77E 584489.4 -13533.3 133.0 32131311 E21776A0003 1 1 3214 35238.02N 74329.17E 584470.7 -13533.5 133.1 32131320 E21776A0003 1 1 3213 35238.02N 74328.55E 584451.7 -13533.4 132.8 32131329 E21776A0003 1 1 3212 35238.02N 74327.94E 584432.9 -13533.4 132.9 32131338 E21776A0003 1 1 3211 35238.03N 74327.34E 584414.3 -13533.1 132.2 32131346 E21776A0003 1 1 3210 35238.04N 74326.73E 584395.5 -13532.6 132.7 32131355 E21776A0003 1 1 3209 35238.04N 74326.12E 584376.7 -13532.5 132.8 32131404 E21776A0003 1 1 3208 35238.05N 74325.52E 584358.1 -13532.2 132.9 32131412 E21776A0003 1 1 3207 35238.06N 74324.91E 584339.4 -13532.1 132.5 32131421 E21776A0003 1 1 3206 35238.06N 74324.30E 584320.5 -13532.0 133.1 32131430 E21776A0003 1 1 3205 35238.07N 74323.69E 584301.8 -13531.7 132.9 32131438 E21776A0003 1 1 3204 35238.08N 74323.08E 584283.0 -13531.3 132.8 32131447 E21776A0003 1 1 3203 35238.10N 74322.46E 584264.0 -13530.8 132.8 32131455 E21776A0003 1 1 3202 35238.12N 74321.86E 584245.2 -13530.1 132.5 32131504 E21776A0003 1 1 3201 35238.13N 74321.24E 584226.1 -13529.6 132.8 32131512 E21776A0003 1 1 3200 35238.15N 74320.64E 584207.6 -13529.1 132.3 32131520 E21776A0003 1 1 3199 35238.17N 74320.03E 584188.9 -13528.6 132.4 32131529 E21776A0003 1 1 3198 35238.18N 74319.43E 584170.3 -13528.2 132.8 32131537 E21776A0003 1 1 3197 35238.19N 74318.82E 584151.6 -13527.9 133.2 32131545 E21776A0003 1 1 3196 35238.21N 74318.22E 584133.0 -13527.3 133.6 32131553 E21776A0003 1 1 3195 35238.22N 74317.61E 584114.1 -13526.8 132.6 32131601 E21776A0003 1 1 3194 35238.24N 74317.00E 584095.4 -13526.3 132.5 32131609 E21776A0003 1 1 3193 35238.26N 74316.39E 584076.6 -13525.7 132.8 32131618 E21776A0003 1 1 3192 35238.27N 74315.78E 584057.9 -13525.3 132.7 32131626 E21776A0003 1 1 3191 35238.28N 74315.18E 584039.4 -13525.0 133.0 32131634 E21776A0003 1 1 3190 35238.30N 74314.58E 584020.8 -13524.4 132.6 32131642 E21776A0003 1 1 3189 35238.31N 74313.97E 584001.9 -13523.9 132.8 32131650 E21776A0003 1 1 3188 35238.32N 74313.36E 583983.2 -13523.7 132.9 32131658 E21776A0003 1 1 3187 35238.33N 74312.75E 583964.4 -13523.3 132.5 32131706 E21776A0003 1 1 3186 35238.34N 74312.14E 583945.6 -13523.0 132.7 32131715 E21776A0003 1 1 3185 35238.34N 74311.54E 583927.1 -13522.9 132.5 32131723 E21776A0003 1 1 3184 35238.35N 74310.93E 583908.3 -13522.6 132.9 32131731 E21776A0003 1 1 3183 35238.36N 74310.33E 583889.5 -13522.4 133.0 32131739 E21776A0003 1 1 3182 35238.36N 74309.72E 583870.8 -13522.3 132.8 32131747 E21776A0003 1 1 3181 35238.37N 74309.11E 583852.0 -13521.9 132.9 32131756 E21776A0003 1 1 3180 35238.39N 74308.50E 583833.2 -13521.5 132.7 32131804 E21776A0003 1 1 3179 35238.40N 74307.89E 583814.5 -13521.2 132.6 32131812 E21776A0003 1 1 3178 35238.41N 74307.29E 583795.8 -13520.6 132.9 32131820 E21776A0003 1 1 3177 35238.42N 74306.69E 583777.3 -13520.5 132.8 32131828 E21776A0003 1 1 3176 35238.42N 74306.08E 583758.5 -13520.5 133.3 32131837 E21776A0003 1 1 3175 35238.41N 74305.47E 583739.7 -13520.6 132.9 32131845 E21776A0003 1 1 3174 35238.41N 74304.86E 583721.0 -13520.7 132.7 32131853 E21776A0003 1 1 3173 35238.41N 74304.25E 583702.1 -13520.7 132.7 32131901 E21776A0003 1 1 3172 35238.40N 74303.65E 583683.5 -13520.8 132.6 32131910 E21776A0003 1 1 3171 35238.41N 74303.04E 583664.7 -13520.5 132.4 32131918 E21776A0003 1 1 3170 35238.41N 74302.43E 583645.9 -13520.4 132.8 32131926 E21776A0003 1 1 3169 35238.41N 74301.82E 583627.2 -13520.4 132.5 32131934 E21776A0003 1 1 3168 35238.41N 74301.22E 583608.6 -13520.6 132.7 32131943 E21776A0003 1 1 3167 35238.40N 74300.60E 583589.7 -13520.9 132.7

Each 80 characters are measurments at a specific point.
I would like each line of the file to stop at 80 characters only and automatically switch to a new line, to do this I believe the following is needed:
1- read the text file in Excel
2- get a function that reads the text file and outputs every 80 characters in a separate line
3- output the file with a new name.

I tried reading it directly in excel using the text to columns features but I ended up having irregular columns for some reason, your help is much appreciated in advance!

Thanks,

 

[VBA Geek]

I see a pattern at each 74 characters, anyway for each 80 chars try this


change the first 3 strings in the Importfile procedure to suit your situation

if my feeling of a pattern each 74 chars is right then change this line from 80 to 74

Const CharactersToLimit As Long = 80

*********************************************************************************************

Sub ImportFile()
Const textFilePath As String = "C:\Desktop\"
Const textFileName As String = "myFile.txt"
Const newTextFileName As String = "myNewFile.txt"


Dim iFile As Long: iFile = FreeFile


Open textFilePath & newTextFileName For Output Access Write As #iFile
    Print #iFile, Join(EveryChars(ReadFile(textFilePath & textFileName)), vbNewLine)
Close #iFile


MsgBox "Done"
End Sub


Private Function ReadFile(sPath As String) As String
Dim iFile As Long
iFile = FreeFile
Open sPath For Input Access Read As #iFile
    ReadFile = Input$(LOF(iFile), iFile)
Close #iFile
End Function


Private Function EveryChars(sContent As String) As Variant
Dim k As Long, aLines() As String
Const CharactersToLimit As Long = 80
ReDim aLines(1 To (Len(sContent) \ CharactersToLimit) - (Len(sContent) Mod CharactersToLimit <> 0))
For k = LBound(aLines) To UBound(aLines)
    aLines(k) = Mid$(sContent, (k - 1) * CharactersToLimit + 1, CharactersToLimit)
Next
EveryChars = aLines
End Function