Manually Define Fixed Widths in Data File

Question

I have a fixed width data file from a third party that contains 1,000 records. It came with a separate document that displays all available columns, char start char end and char length for each column. It has thousands of columns.

My data file doesn't have data in every row so defining the fixed widths in Excel isn't feasible as I might erroneously skip a column because I can't see that it has data.

Is there a text editor that lets you manually type/define or import widths?

Answer 1

What does this "separate document" look like? Let's say I have a text file with a column of width values to be read that looks something like this:

20

25

30

10

5

23

25

10

23

I can then read the values from this text file into excel, and adjust the column widths of my spreadsheet using the following vba code:

Sub colWidth()
    Dim widthArray() As String
    Dim myFile, textline As String
    Dim x, y As Integer


    'example text file containing column widths
    myFile = "C:\qqq\qqq\qqq\widths.txt"

    'loop through the file and store each column width in an array
    Open myFile For Input As #1
    x = 1
    Do Until EOF(1)
        Line Input #1, textline
        ReDim Preserve widthArray(1 To x)
        widthArray(x) = textline
        x = x + 1
    Loop
    Close #1

    'using the array of column widths to adjust columns
    For y = 1 To UBound(widthArray)
        Columns(y).ColumnWidth = Int(widthArray(y))
    Next y
End Sub