Fix a Text File - Make it Fixed Width... 2

[air1access]

I have a text file that I need to import into an Access table. But first it must be formated...

The text file looks like this:
A-A-59173 USA 04-017
A-A-59173 USA 04-521
A-A-59474 USA 08-097
A-A-59474 USA 08-097A
A-A-59474 USA 08-097B
A-A-59503 TYPE I GRADE B CLASS 1 USA 19-014
ABGN/44/83324 GB 09-016
ABP 4-1123 UK 16-006D
ABP 4-2128 GB 16-021B
ABP1-4053 GB 05-064
ABP4-1123 GB 16-006E
ABP4-2124 GB 16-042A
ABR 1-4055 GB 06-011
ABR 9-0140 GB 11-026

I need for it to be like this:
A-A-59173 USA 04-017
A-A-59173 USA 04-521
A-A-59503 TYPE I GRADE B CLASS 1 USA 19-014
ABP 4-1123 UK 16-006D
ABP 4-2128 GB 16-021B

Basically - everything from and after "USA" or "UK" or "GB" needs to be moved over a few spaces to "make a column"... And everything before "USA" or "UK" or "GB" is a seperate column... Sure hope this makes sense..!!

The below example is what I'm working with...
Well - kinda... Its like its almost there...!!

Open strInFile For Input As #1
Open strOutFile For Output As #2

Do While Not EOF(1)
Line Input #1, strLine

strType = Left(strLine, InStr(strLine, "USA") - 2)
strType = Mid(strLine, InStr(strLine, "USA "))

Print #2, strType
Line Input #1, strType
Loop

Any examples or suggestions...?
I thank you in advance...!!

air1access

 

[SkipVought]

Hi,

I would simply REPLACE
[tt]
[SPACE]USA
[/tt]
or
[tt]
[SPACE]GB
[/tt]
with
[tt]
,USA
[/tt]
or
[tt]
,GB
[/tt]
Then import using the [COMMA] as the field separator.



Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[HarleyQuinn]

If you need to have a bunch of spaces in the file contents then you could use code like this:

Public Sub FormatFile(strInFile As String, strOutFile As String)
Dim re As Object

Dim m As Variant
Dim strOutput As String

Set re = CreateObject("VBScript.RegExp")

With re
    .Global = True
    .MultiLine = True
    .IgnoreCase = True
    .Pattern = "(^.*?)( USA | GB | UK .*?$)"
    strOutput = .Replace(CreateObject("Scripting.FileSystemObject").OpenTextFile(strInFile, 1, False).ReadAll, "$1" & Space(39) & "$2")
End With

Debug.Print strOutput

CreateObject("Scripting.FileSystemObject").OpenTextFile(strOutFile, 2, True).Write strOutput

Set re = Nothing
End Sub

Just pass it the name of the file and the name of the file you want to generate.

If you have any queries please just post back.

Regards

Andy
---------------------------------
Zebracorn: 50% Zebra, 50% Unicorn = 100% Real.

http://lessthandot.com

 

[HarleyQuinn]

Seems I've been away too long and have slowed down on my posting speed considerably!

Skip's suggestion is a bit simplier than mine...

Andy
---------------------------------
Zebracorn: 50% Zebra, 50% Unicorn = 100% Real.

http://lessthandot.com

 

[air1access]

HarleyQuinn & Skip...

Thank you both very much...!! It got it to work..!!!

air1access