Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Fixed Width Layout
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 64
Default Fixed Width Layout
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
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 35,218
Default Fixed Width Layout
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????

Little Penny wrote:

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
Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 9,101
Default Fixed Width Layout
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" wrote:

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

Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 64
Default Fixed Width Layout
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





On Wed, 12 Sep 2007 05:30:06 -0700, Joel
wrote:

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" wrote:

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

Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 9,101
Default Fixed Width Layout
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" wrote:

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





On Wed, 12 Sep 2007 05:30:06 -0700, Joel
wrote:

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" wrote:

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


Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 64
Default Fixed Width Layout

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





On Wed, 12 Sep 2007 23:40:03 -0700, Joel
wrote:

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" wrote:

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





On Wed, 12 Sep 2007 05:30:06 -0700, Joel
wrote:

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" wrote:

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


Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 9,101
Default Fixed Width Layout
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" wrote:


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





On Wed, 12 Sep 2007 23:40:03 -0700, Joel
wrote:

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" wrote:

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





On Wed, 12 Sep 2007 05:30:06 -0700, Joel
wrote:

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" wrote:

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



Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Hybrid Mode Hybrid Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Column fixed width juanpablo Excel Discussion (Misc queries) 1 January 16th 08 12:57 PM
Fixed width fonts Also Excel Discussion (Misc queries) 3 January 8th 08 04:42 PM
Fixed Width Brad Excel Programming 2 February 21st 07 07:04 PM
fixed width file? kwiklearner[_6_] Excel Programming 2 March 13th 06 08:59 PM
Fixed Width - Opening Certain Fixed Width Files Jan[_8_] Excel Programming 2 December 30th 03 08:31 PM


All times are GMT +1. The time now is 01:30 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement