Import Text File into Excel - Build Columns based on position

[chris9]

My question is almost identical to one posted earlier.
Here is the message posted in December the only change is that i wanting to import my text file in ACCESS not excel.
Can someone tell me an easy way to do this.
Thank you


StephDN (MIS) Dec 31, 2002
Hi!

I'm trying to import a rather complex text file into an Excel spreadsheet (simple enough). I can use the import wizard and set up the columns where I need them (not based on tabs, commas, etc.), but I need it be automated (no wizard).

I need to select text in the text file based on row positioning and import the text into the columns. For example: I need the column A to contain the first 7 characters of the the text file, Column B to contain the next 10 characters, Column C to contain next 2 characters, and so on. I can't use the regular delimited method because each line in the text file is different (and not based on a character). I just need to be able to select the text from the text file based on character positioning, and import that into each consecutive column - and then do this for each line in the text file.

I've searched and searched for examples, but can only find examples that seperate the text file by commas, spaces, or colons. PLEASE HELP!!

Thanks in advance!

 

[johnnymac43]

This is easy in Access. Use the Import Text Wizard to create an import specification. The wizard will probably default to Fixed Width. Leave it there and click on Advanced.

The wizard will present a list of fields based on where it finds spaces in the first line. Change these to your requirements and save the specification. You can then automate the process with a macro or in code.

HTH

John

 

[chris9]

Thanks for the help but could you get me started on creating a macro. I'm new to access and they seem quite a bit different from excel.

 

[johnnymac43]

You really should try a few simple things like this for yourself before you ask for help - that is definitely the best way to learn, and the world won't stop if you get it wrong once or twice! However:

Macros - New
Transfer Text - Import Fixed Width - <Specification Name> - <Table Name> - <File Name> (include path if the text file is not in the same folder as the database) - Has Field Names [Yes/No]
Save - <Macro Name> - OK

HTH

John