Read a text file and output a formated version of it

docaia

New Member
Joined
Feb 28, 2008
Messages
38
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,
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
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

Code:
Const CharactersToLimit As Long = 80


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

Code:
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
 
Upvote 0

Forum statistics

Threads
1,223,098
Messages
6,170,099
Members
452,301
Latest member
QualityAssurance

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top