3

I receive files on occasion in a fixed width format. I need to import them into Excel but Excel doesn't perfectly pick up the columns. I can do it manually each time with the Text Import Wizard, but I'm wondering if there is a way to create a "text import template" or something similar - since these files are always the same format.

3 Answers 3

3

If you record a macro (Tools - Macro - Record New Macro) while you open the file, you will get the parameters you need to use the OpenText method. Here's an example

Workbooks.OpenText Filename:= _
    "C:\Documents and Settings\dick\My Documents\actsynclog.txt", Origin:=437, _
    StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 1), Array(8, _
    1), Array(31, 1), Array(41, 1), Array(57, 1), Array(77, 1), Array(80, 1), Array(82, 1), _
    Array(84, 1), Array(86, 1)), TrailingMinusNumbers:=True

The FieldInfo argument is the one you care about. It's an array of 2d arrays. The first element is the column and the second is the data type. This example splits the text file in the following columns: 0, 8, 31, 41, 57, 77, 80, 82, 84, and 86.

With that you, you can write a macro to open the text file. See the GetOpenFileName method of the Application object for how to select which file to open.

2

In Excel, it is possible to save text import settings (as a "template") and reuse them with any text file later. A blogpost by Excel MVP Jan Karel Pieterse describes this in detail: http://blogs.office.com/2010/06/10/importing-text-files-in-an-excel-sheet/

The "text import template" is automatically created when you are done with Text Import Wizard. Make sure that you save your workbook to keep the "template".

To reuse it with another (or the same) text file:

  1. Click any cell within your sheet that holds (used to hold) data of a previous import.
    • Excel 2007/2010: Click Data -> Refresh All dropdown -> Refresh
    • Excel 2003 and older: Click Data -> Refresh Data

If you use this "template" to always import a file of the same name and location, you can customize the "template" so that Excel does not prompt you for a file name each time you hit the Refresh button.

During the text import, click Properties... in the Import Data dialog and untick Prompt for file name on refresh in the External Data Range Properties dialog. You can also edit that later in the Connection Properties dialog.

I would post screenshots, but I "need at least 10 reputation to post images."

1

If you are fine with some Perl programming,
there is a Spreadsheet module which supports WriteExcel.

I have used this earlier to do the inverse of what you want -- convert an Excel file to CSV format,
I used ReadExcel for that.

Take a look at the examples in the WriteExcel link here and other references for XLS-to-CSV on that question, you might find something that works for you between the two.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .