quick fixed-width file import

  • Thread starter Thread starter rdrake
  • Start date Start date
R

rdrake

I import fixed-width files regularly. The files have anywhere betwee
20 and 50 columns and have varying widths. How can I make a list o
column start locations and lengths and have the data automaticall
imported rather than taking an hour to locate each column manually i
the Text Import Wizard
 
rdrake wrote...
I import fixed-width files regularly. The files have anywhere
between 20 and 50 columns and have varying widths. How can
I make a list of column start locations and lengths and have the
data automatically imported rather than taking an hour to locate
each column manually in the Text Import Wizard?
Click to expand...

Easier to open these text files without parsing into fields then us
Data > Text to Columns to parse. If each distinct file type (i.e.
field layout) has a different line length, you should use the macr
recorder while specifying the fixed width parsing for each file type
That would give you a separate macro for each file type. Then write
driver macro like


Sub foo()
Dim resp As Variant, wb As Workbook

resp = Application.GetOpenFilename( _
FileFilter:="Text Files (*.txt),*.txt", _
Title:="Select text file to open" _
)

If resp = False Then Exit Sub

Set wb = Application.Workbooks.Open( _
FileName:=resp, _
Format:=5 _
)

Select Case Len(wb.Worksheets(1).Range("A1"))
Case 20
Parse20
Case 35
Parse35
Case 50
Parse50
Case Else
MsgBox Title:="Unable to parse", _
Prompt:="No parser available for """ & wb.FullName & """"
End Select

End Sub


Then use this macro to open and parse your text file
 
r,

Do you have Excel 2002 or 2003? Do the files of the same layout always go
to the same place in your workbook? If so, use Data, Import External Data -
Import data. Once set up, for the next import, you need only right-click in
the existing table and choose refresh. It remembers the file location,
field lengths, etc.
 
Back
Top