Fix Text File 1

[air1access]

I have a text file that is space delimited - the problem is the spaces are uneven/different widths or what not... This creates problems with my import spec.. How can I use VB to make the spaces between the values on each line even & line the columns up on the text file..?

What I have posted below is not what I'm working with - but I'm messing with the code structure to get it to work, and I'm striking out..!! I know that I need to maybe use the "mid" function, or maybe the "split" function...??? Look for the spaces in each line and move the values accordingly to get everything to line up in nice & even columns...

Any suggestions/idea/examples..?
Thanks in advance..!!!
air1access

Do Until EOF(1)
Line Input #1, strLine
If Left(strLine, 2) = "E " _
Or Left(strLine, 2) = "S " Then
strType = strLine
Print #2, strType
End If
Loop

Close #1
Close #2

 

[jges]

Rather than a space delimited file, it sounds like you are really after a fixed width file; where each field is a certain number of characters wide. Reference

http://en.wikipedia.org/wiki/Flat_file_database

or

http://www.softinterface.com/Convert-XLS\Features\Fixed-Width-Text-File-Definition.htm

for more info.

If you are writing such a file out to disk, your code might look something like:

columnWidth = 60
strData = "Hello World"
lengthData = Len(strData)
if lengthData < columnWidth then
  [COLOR=green]'pad data string to required width[/color]
  strPadding = Space(columnWidth - lengthData)
  strData = strData & strPadding
else
  [COLOR=green]'truncate data to required width[/color]
  strData = Left(strData, columnWidth)
end if

Where columnWidth is the fixed width of your given column and strPadding is a string of space characters to append to your data to get to the required width.

 

[randy700]

What version of Access?
I use 2007 and have no problem with space delimited files.
Just select Text File from the Import section
of the External Data tab.


Randy

 

[Andrzejek]

the problem is the spaces are uneven/different widths

Spaces are just spaces, they cannot be "uneven" or "different widths". How they appear to you is just the matter of what font you are using to view the text file.

Please provide the sample of your text file here so we can see the problem you decribe.

Have fun.

---- Andy

 

[TheAceMan1]

To All . . .

[blue] ... [purple]the problem is the spaces are uneven/different widths or what not...[/purple] This creates problems with my import spec..[/blue]

I believe [blue]air1access[/blue] is referring to the number of spaces between data elements as in:

[tt][blue]aaa bbb ccc
ddd eee fff[/blue][/tt]

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[TheAceMan1]

air1access . . .

Before you import you could correct the file with something like the following:

[blue]   Dim Pak As String
   
   [green]'1. Open the file[/green]
   [green]'2. Read the file into the string Pak[/green]
   
   [green]'3. Remove all extra spaces with the following[/green]
   Do Until InStr(Pak, "  ") = 0
      Pak = Replace(Pak, "  ", " ")
   Loop
   
   [green]'4. Overwrite the file with Pak[/green]
   [green]'5. Close the file[/green][/blue]

Using the example from my prior post:

[tt][blue]aaa bbb ccc
ddd eee fff[/blue][/tt]

Corrected would be:
[tt][blue]aaa bbb ccc
ddd eee fff[/blue][/tt]

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[air1access]

The AceMan1,

That is exactly what I need to do..!! Dang you are on it..!!
But I cannot not incorporate your example into what I'm working with... It just keeps looping on the 1st line and re-writes that line over & over... It "evens out the spaces", but just keeps looping that 1st line...
This is what I'm working with...

strInFile = "D:\UPSDATA\TaskCompliance1.txt"
strOutFile = "D:\UPSDATA\TaskCompliance2.txt"

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

Do While Not EOF(1)
Line Input #1, strLine
Do Until InStr(strLine, " ") = 0
strType = Replace(strLine, " ", " ")
Loop
Print #2, strType
Loop

Close #1
Close #2

 

[PHV]

...
Do While Not EOF(1)
    Line Input #1, strLine
    Do Until InStr(strLine, "  ") = 0
        str[!]Line[/!] = Replace(strLine, "  ", " ")
    Loop
    Print #2, str[!]Line[/!]
Loop
...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[air1access]

PHV,

Thanks for the fast response..!!
It is now looping thru each line, but it is not "evening out the spaces"....

Any other suggestions/examples..?

Thanks..!!!
air1access

 

[PHV]

Please, define "evening out the spaces"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[air1access]

Well - I guess what I'm saying is - I need the spaces to be bigger... I tried using Space(5) at strLine = Replace(strLine, " ", " ") -- like Replace(strLine, " ", Space(5))...
But it just runs forever & ever... Takes very long...

Sounds like I'm not explaining what I'm requesting very good..!!
Man - its right there... I'm so close..!!

Any other fixes/suggestions/examples..?

Thanks so much..!!!

 

[PHV]

Any other fixes/suggestions/examples..?
On WHAT ?
Please, explain what do you want to do with some input data samples and corresponding output data.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Andrzejek]

I would repeat my suggestion: "Please provide the sample of your text file here so we can see the problem you decribe."

Or give an example of what you have in your text file, and what you would like it to be.

Please, use [ignore][tt] and [/tt] or

 and

[/ignore] to post a sample of text file.

Have fun.

---- Andy

 

[air1access]

Ok... My bad...

This is what the text file looks like now:
aa bbbb ccc dd eeee
aaa bb cccccc d eeeeee

I want it to look like this:
aa bbbb ccc dd eeee
aaa bb cccccc d eeeeee

Hope that helps..!!
Sorry..!!!

 

[PHV]

I'd replace this:
Print #2, strLine
with this:
Print #2, Join(Split(strLine), vbTab)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[air1access]

That did it..!!
Sorry for the misunderstanding..!!

Thanks again..!! You ROCK..!!!