Fixed Width Layout

[Little Penny]

Is there a macro that can tell me the layout (Number of columns and
column width) of fixed with text file?

I currently import these files into MS Access (Import text wizard) and
use the advance feature to tell me this information.

Thanks

 

[Dave Peterson]

I don't think that there's any generic way to tell how fixed width files are
laid out.

Any chance you can go back to the programmer who made the text file and ask
him/her for the spec.

I would bet that the only way that Access can know is that someone took the time
to set up the rules. Maybe you can talk to that person for this specific file
type????

 

[Joel]

Thank you Thank you Thank you

Change the filename as required. this is a very primitive function that
only checks 1st line of the code.

Sub advanceformat()

Const ForReading = 1, ForWriting = 2, ForAppending = 3
Const MyPath = "C:\temp\"
Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0

Dim StartField(120)

ReadFilename = "FixedWidth.txt"

Set fsread = CreateObject("Scripting.FileSystemObject")

'open files
ReadPathName = MyPath + ReadFilename
Set fread = fsread.GetFile(ReadPathName)
Set tsread = fread.OpenAsTextStream(ForReading, TristateUseDefault)

inputline = tsread.readline
Length = Len(inputline)

StartField(0) = 1
FieldCount = 1
Spaces = False
For ColumnCount = 1 To Length
a = Mid(inputline, ColumnCount, 1)
Select Case Spaces

Case True
If Mid(inputline, ColumnCount, 1) <> " " Then
StartField(FieldCount) = _
ColumnCount
FieldCount = FieldCount + 1
Spaces = False
End If
Case False
If Mid(inputline, ColumnCount, 1) = " " Then
Spaces = True
End If
End Select

Next ColumnCount
StartField(FieldCount) = Length
OutputString = "Number of columns = " & _
Length & Chr(13)

For Fields = 0 To (FieldCount - 2)
MyWidth = StartField(Fields + 1) - _
StartField(Fields)
OutputString = OutputString & "Field " & _
(Fields + 1) & " start at " & _
StartField(Fields) & ", width = " & _
MyWidth & Chr(13)
Next Fields
MsgBox (OutputString)
tsread.Close

End Sub

 

[Little Penny]

Thanks again Joel

There is one thing. The last column in the file is always a period (.)
for some reason this is the only column that the macro does not
recognize for example my test file has 24 columns the maco only shows
me 23. I'll try and figure out why.


But thanks again

 

[Joel]

I fixed a couple of problems.

Sub advanceformat()

Const ForReading = 1, ForWriting = 2, ForAppending = 3
Const MyPath = "C:\temp\"
Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0
Dim StartField(120)

ReadFilename = "FixedWidth.txt"

Set fsread = CreateObject("Scripting.FileSystemObject")

'open files
ReadPathName = MyPath + ReadFilename
Set fread = fsread.GetFile(ReadPathName)
Set tsread = fread.OpenAsTextStream(ForReading, TristateUseDefault)

inputline = tsread.readline
Length = Len(inputline)

StartField(0) = 1
FieldCount = 1
Spaces = False
For ColumnCount = 1 To Length

Select Case Spaces

Case True
If Mid(inputline, ColumnCount, 1) <> " " Then
StartField(FieldCount) = _
ColumnCount
FieldCount = FieldCount + 1
Spaces = False
End If
Case False
If Mid(inputline, ColumnCount, 1) = " " Then
Spaces = True
End If
End Select

Next ColumnCount
StartField(FieldCount) = Length + 1
OutputString = "Number of columns = " & _
Length & Chr(13)

For Fields = 0 To (FieldCount - 1)
MyWidth = StartField(Fields + 1) - _
StartField(Fields)
OutputString = OutputString & "Field " & _
(Fields + 1) & " start at " & _
StartField(Fields) & ", width = " & _
MyWidth & Chr(13)
Next Fields
MsgBox (OutputString)
tsread.Close

End Sub

 

[Little Penny]

Thanks again Joel

Is it possible to have the reults in the spreadsheet to look something
like this. If not you have done more than enough.


A B C
Start Width
Field 1 1 25
Field 2 70 13
Field 3 83 11
Field 4 94 19
Field 5 133 29
Field 6 142 18
Field 7 160 14



Thanks


Litle Penny

 

[Joel]

I left the msgbox and added a few lines of code to write the same info to the
active worksheet

Sub advanceformat()

Const ForReading = 1, ForWriting = 2, ForAppending = 3
Const MyPath = "C:\temp\"
Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0
Dim StartField(120)

ReadFilename = "FixedWidth.txt"

Set fsread = CreateObject("Scripting.FileSystemObject")

'open files
ReadPathName = MyPath + ReadFilename
Set fread = fsread.GetFile(ReadPathName)
Set tsread = fread.OpenAsTextStream(ForReading, TristateUseDefault)

inputline = tsread.readline
Length = Len(inputline)

StartField(0) = 1
FieldCount = 1
Spaces = False
For ColumnCount = 1 To Length

Select Case Spaces

Case True
If Mid(inputline, ColumnCount, 1) <> " " Then
StartField(FieldCount) = _
ColumnCount
FieldCount = FieldCount + 1
Spaces = False
End If
Case False
If Mid(inputline, ColumnCount, 1) = " " Then
Spaces = True
End If
End Select

Next ColumnCount
StartField(FieldCount) = Length + 1
OutputString = "Number of columns = " & _
Length & Chr(13)


For Fields = 0 To (FieldCount - 1)
MyWidth = StartField(Fields + 1) - _
StartField(Fields)

Cells(Fields + 1, "A") = "Field " & (Fields + 1)
Cells(Fields + 1, "B") = StartField(Fields)
Cells(Fields + 1, "C") = MyWidth
OutputString = OutputString & "Field " & _
(Fields + 1) & " start at " & _
StartField(Fields) & ", width = " & _
MyWidth & Chr(13)
Next Fields
MsgBox (OutputString)
tsread.Close

End Sub

 

[Little Penny]

Thanks Joel

Can I add thos to the code


ReadFilename = Application.GetSaveAsFilename( _
fileFilter:="Text Files, *.txt", _
InitialFileName:="FixedWidth.txt",Title:="SelectDataFile")

If possible I want to be ablde to navigate a choose the file.

I tried it but ofcourse it did not work.



The good the about all help help you have given me is every time the
code is mode I can campare the difference. This is a huge help in
learning to do this on my own.


Thanks

 

[Joel]

The fix was simple. The old code had the following:

ReadPathName = MyPath + ReadFilename

Notice the path was included in the ReadPathName. GetSaveAsFilename was
also including the path. You had the path in the name twice. I simply
removed MyPath from ReadPathName.

I nthe code below I change your InitialFileName to include MyPath. Now the
Pop Up window goes to the MyPath directory (folder). The Pop up will return
the full pathname of the file which you can simply open.

There is a problem with some VBA file selection methods that you have to
strip the filename from the path to use them. I originally set the code up
keeping the path and the filename seperate so these functions can be used
(you don't have this problem yet). In the past, I've had to write a simple
loop to strip the file name from the path when using some of these commands.
It is just extra code that is unecessary.

Your code is just fine!

Sub advanceformat()

Const ForReading = 1, ForWriting = 2, ForAppending = 3
Const MyPath = "C:\temp\"
Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0
Dim StartField(120)

ReadFilename = Application.GetSaveAsFilename( _
fileFilter:="Text Files, *.txt", _
InitialFileName:=MyPath & "FixedWidth.txt", _
Title:="SelectDataFile")
Set fsread = CreateObject("Scripting.FileSystemObject")

'open files
Set fread = fsread.GetFile(ReadFilename)
Set tsread = fread.OpenAsTextStream(ForReading, TristateUseDefault)

inputline = tsread.readline
Length = Len(inputline)

StartField(0) = 1
FieldCount = 1
Spaces = False
For ColumnCount = 1 To Length

Select Case Spaces

Case True
If Mid(inputline, ColumnCount, 1) <> " " Then
StartField(FieldCount) = _
ColumnCount
FieldCount = FieldCount + 1
Spaces = False
End If
Case False
If Mid(inputline, ColumnCount, 1) = " " Then
Spaces = True
End If
End Select

Next ColumnCount
StartField(FieldCount) = Length + 1
OutputString = "Number of columns = " & _
Length & Chr(13)


For Fields = 0 To (FieldCount - 1)
MyWidth = StartField(Fields + 1) - _
StartField(Fields)

Cells(Fields + 1, "A") = "Field " & (Fields + 1)
Cells(Fields + 1, "B") = StartField(Fields)
Cells(Fields + 1, "C") = MyWidth
OutputString = OutputString & "Field " & _
(Fields + 1) & " start at " & _
StartField(Fields) & ", width = " & _
MyWidth & Chr(13)
Next Fields
MsgBox (OutputString)
tsread.Close

End Sub

 

[Little Penny]

Thanks for the education. Everything works as advertised. I do have
one more question which I will post under a new subject. I has to do
with copying a module to another workbook.


Thanks for all your help