Results 1 to 8 of 8
  1. #1
    Lounger
    Join Date
    Oct 2003
    Location
    Toronto, Ontario, Canada
    Posts
    30
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Automatically importing a column-delimited text fi (2002)

    Hi, I have done a number of searches on this but so far, no joy. I have a column-delimited text file that is generated by a mainframe application (an interface file). I would like to read this into Excel using some form of file (schema.ini?) to define the columns, datatypes, assign column names, etc. as a means of testing the files contents. I will be doing this over and over again with the same format. Is there some way I can specify the format and just say "Read xxx.txt using schema.ini" and have it automatically load into Excel? The reverse (writing to a column-delimited file) would also be handy. I have seem some tantalizing references to schema.ini, but not enough to clue me in. Any help would be appreciated.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Automatically importing a column-delimited text fi (2002)

    A schema.ini file is used when you use ADO to open a text file. Excel has its own methods for opening text files.

    Since the format of the text file will be the same each time, I would recommend that you record a macro of opening one such file. You can then study the code generated by the macro recorder and tweak it (for example prompt the user to select a file instead of having a fixed file name in the code).
    Similarly, you can record a macro of saving a worksheet as a text file.

  3. #3
    Lounger
    Join Date
    Oct 2003
    Location
    Toronto, Ontario, Canada
    Posts
    30
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Automatically importing a column-delimited text fi (2002)

    Thanks Hans, that worked. Is the write to file basically the same?

    Stu

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Automatically importing a column-delimited text fi (2002)

    You can record a macro of saving a worksheet to a fixed width text file, then tweak it.

  5. #5
    Lounger
    Join Date
    Oct 2003
    Location
    Toronto, Ontario, Canada
    Posts
    30
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Automatically importing a column-delimited text fi (2002)

    Trying to record the macro to save as fixed-width text file, can't see any options to do this, is this a different save-as extension or an add-in required?

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Automatically importing a column-delimited text fi (2002)

    You must select 'Formatted text (space delimited)' as file type in the Save As dialog. Before you do so, you must set the column widths in the worksheet to the widths you want the text file to use. You can do this manually, or in code.

    Example:

    Range("A1").ColumWidth = 14
    Range("B1").ColumWidth = 10
    Range("C1").ColumWidth = 6
    Range("D1").ColumWidth = 15

    ActiveWorkbook.SaveAs _
    Filename:="C:ExcelExport.txt", _
    FileFormat:=xlTextPrinter

  7. #7
    Lounger
    Join Date
    Oct 2003
    Location
    Toronto, Ontario, Canada
    Posts
    30
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Automatically importing a column-delimited text fi (2002)

    Thanks Hans, I had to play a little with the column settings, works well now. Results attached, many thanks.

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Automatically importing a column-delimited text fi (2002)

    Thanks for sharing your code.

    One remark: in most situations, it is not necessary to select ranges in a macro, and code will run more efficiently if you don't. For example, the two lines
    <code>
    Columns("A:A").Select
    Selection.ColumnWidth = 0
    </code>
    can be combined into one:
    <code>
    Columns("A:A").ColumnWidth = 0
    </code>
    and the lines
    <code>
    Columns("H:H").Select
    Selection.ColumnWidth = 20 ' Text of numeric value
    Selection.NumberFormat = "0.00" ' No commas, 2 decimal places
    </code>
    can be changed to
    <code>
    With Columns("H:H")
    .ColumnWidth = 20 ' Text of numeric value
    .NumberFormat = "0.00" ' No commas, 2 decimal places
    End With</code>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •