How to automate importing a text file into Excel

October 5, 2005 5:42 PM

ExcelFilter: I know how to import a text file into Excel using the wizard for fixed column widths. Can I automate the process?

I am working on an application that generates very large text files (frequently 100 to 150 meg). The file contents are in fixed width columns. Opening the file in a plain text reader like notepad is obviously problematic. Opening the file in Excel is much better for a multitude of reasons. Unfortunately, every time I open one of these files in Excel I have to walk through the import wizard and spend about five minutes specifying the actual columns (the wizard only gets it about half right initially). The column widths in the generated text files never, ever change. Is there some way to automate the Excel import with some kind of template or saved settings?

posted by Lokheed to Computers & Internet (5 answers total)

I've just used the macro recorder to record the import stage - it seemed to work. I'd try doing that, and then edit the macro code to use an InputBox to select the file (or some sort of 'browse-to-file' thing if you're tricky- I know I've made that work once or twice).
posted by pompomtom at 6:00 PM on October 5, 2005

Hmmm, that wasn't that clear was it?

You can use the macro recorder to record the input stage and it will save the column widths. When the macro is run you will not have to see the wizard. It also saves the path and filename - so if you didn't want to edit the VBA code, you could rename your first file "Importing.txt" or somesuch, record the import, and then each time you need to import another file, just rename your new file to the predetermined name (in the same directory).
posted by pompomtom at 6:10 PM on October 5, 2005

Best answer: If you have programming experience you can write a perl script that will take the text file as input and output an .xls file. All the heavy lifting is already done for you with modules like Spreadsheet::SimpleExcel or Spreadsheet::WriteExcel::Simple.

I'm not sure what you mean about "Opening the file in a plain text reader like notepad is obviously problematic." Notepad is not a good text editor. Good text editors will let you view and edit arbitrarily large files without loading the entire thing into memory. You should have no trouble at all viewing and editing this file with a good editor, if you want to eliminate the Excel step entirely.
posted by Rhomboid at 6:39 PM on October 5, 2005

Visual Basic or vb.net would also be a viable option.
posted by Manjusri at 7:37 PM on October 5, 2005

If you have access to, well, Access, you can define an import layout, save it, and re-use it in future imports. Exporting from Access to Excel should be trivial.
posted by SPrintF at 8:10 PM on October 5, 2005