How do I lock columns on import?

[erikd]

The subject line may not be too descriptive, but it's the best I could
think of. I have a mainframe report that is column delimited. It's a
list of emplyee information and besides some demographic information,
it's primarily their work and home addresses.

I export this report from the mainframe and then import it into Excel
to do some simplistic manipulation. The problem is the import. I can't
come up with a method to make it repeatable. When I import it into
Excel, I go through the steps of defining the columns and then what
type of data is in each column. The line of data is about 350
characters long and Excel places the suggested column breaks at
different columns depending on which records I place into the report.
Sometimes it will place a suggested break in column 83, which is in
the middle of a USA address, but probably makes some sense for one of
the non-USA addresses further down in the report.

I know which columns are supposed to be what type (number, text, don't
import) and where the breaks should be, but I haven't run across a
method in Excel to specifiy that information beforehand or in a macro.
Any suggestions? One person suggested recording the keystrokes, but
that won't work since the suggested column breaks are not always the
same. I know I need the columns to break at columns 13, 18, 19, 24,
27, etc and I know that the first 8 colums are text and the 9th is a
number and the last column shouldn't be imported, but how do I program
that information into Excel so that I don't have to repeat essentially
the same steps every time?

Erik

 

[Earl Kiosterud]

Erik,

I suspect you're using File - Open to import the text file. With Excel
2002, you can instead use Data - Import External Data - Import Data.
Navigate to the folder containing your file to be imported. The import
wizard will start, where you specify fixed columns, etc. The file will be
imported into your existing sheet, rather than into a new workbook. Now you
can re-read it by right-clicking it and selecting Refresh.

 

[erikd]

Erik,

I suspect you're using File - Open to import the text file. With Excel
2002, you can instead use Data - Import External Data - Import Data.
Navigate to the folder containing your file to be imported. The import
wizard will start, where you specify fixed columns, etc. The file will be
imported into your existing sheet, rather than into a new workbook. Now you
can re-read it by right-clicking it and selecting Refresh.

Thanks for the help. Yes, you're correct, I am using File - Open. I
didn't think it would make a difference, but I'm using Excel 2000. The
version I have doesn't have that exact wording, but I was able to use
the procedure that you posted. It kept the definition of the columns I
imported and I'll be able to use it to reduce the time and effort in
the future.

It's not as clean as I was hoping though. It means I'll need to keep a
copy of the spreadsheet around. I was hoping for a solution more along
the lines of a macro or a template. Unfortunately although I can tweak
others macros, I'm not that good at creating them. But refreshing the
import will definitely be more effective than what I was doing.

 

[Earl Kiosterud]

Erik,

I'm not sure what you mean "keep it around." SOmething has to keep the
information about how the data is to be imported -- either the query I
described, or a macro that records the File - Open method. If you need
produce a separate workbook with the data each time, you could use either
method. In the first, a macro could do the refresh, importing the data into
the workbook containing the macro, then make a copy of the imported sheet to
a new workbook and save it with the desired name. A macro could also do the
second case (File - Open). Either would automate the import process, with
the field specs. You'd step through the process with the macro recorder
running.