Import fixed-width Txt Files to Access (Office 2000)

Hi everybody:

I've been asked to write VBA code to import .txt dumps from the company's mainframe into Access 97 and 2000. These files are not comma or tab delimited, so I need to use fixed-width. Does anyone have some sample code? Or is there another technique I should use?

I tried using the TransferText function, but for fixed-width files, I don't see a way to create the specification from code or read them except by using the wizard.

Re: Import fixed-width Txt Files to Access (Office 2000)

The TransferText Method should do the trick (at least in 2000) - it has a SpecificationName parameter that lets you select a specific import specification that is stored in your database. The 2002 Help file says:
<font color=blue>SpecificationName
Optional Variant
A string expression that's the name of an import or export specification you've created and saved in the current database. For a fixed-width text file, you must either specify an argument or use a schema.ini file, which must be stored in the same folder as the imported, linked, or exported text file. To create a schema file, you can use the text import/export wizard to create the file. For delimited text files and Microsoft Word mail merge data files, you can leave this argument blank to select the default import/export specifications.</font color=blue>

Re: Import fixed-width Txt Files to Access (Office 2000)

The trick to creating the spec in later versions of Access is to create the empty target table that you want to import the data into and then tell Access you want to export that table to a fixed width text file. Then you can click on the Advanced button in the wizard, create and save your spec and then use that spec when you import the actual fixed width file.

Re: Import fixed-width Txt Files to Access (Office 2000)

Thank you! That will get me started.

I'll also be revising some old apps (and writing some new ones) that use I/O code in VBA. Does anybody have sample code using Open for Input As . . . and/or FileOpen, FilePut/FileGet, etc? I need to understand the whole process for fixed-width files.

Re: Import fixed-width Txt Files to Access (Office 2000)

Now I'm confused. You don't actually need file I/O code to import/export fixed width files. TransferText will do it nicely in most cases, and file I/O isn't particularly a function of fixed width. If you want to use it, there are samples of code for file I/O in the on-line help for each version of Access/VBA. Just go to the VB Editor in Access, click on Help and type Open Statement in the Answer Wizard textbox. That topic will provide an example and syntax for the process.

Re: Import fixed-width Txt Files to Access (Office 2000)

I think she is asking a completely different question, not extending the import text question.

Do you have access to the Microsoft Scripting Runtime (scrrun.dll)? If you have Visual basic, you should have it. I believe it also comes with Internet Explorer. If you have MSDN search the help for this feature. If not, you can go to Msdn.Microsoft.com and do the search there. It will have examples on how to use this tool.

The Scripting runtime gives you access to the FileSystem object, which you can use to scan directories and open files. It has a TextStream object, which lets you read and write text files incredibly easily. There are methods to read entire files, or chunks of files, or a line at a time. I was always getting confused as to which open method to use, and how to read/get/input the text. The Scripting Runtime simplifies all that.

I believe you can use it in Access 2000 and 97 if you have it on your (and your user's) pc.

Re: Import fixed-width Txt Files to Access (Office 2000)

Originally there were 2 questions, one about importing fixed-width files, and one about reading text files in general. I was addressing the second one. Of course, you're right about not NEEDING the FileSystem object to open and read a file. Her last post seemed to contain some of the same confusion I've had in the past about which commands to use to open and read the file. There are many different combinations of methods. So, I was offering up the FileSystem object as a much simpler way of doing file IO (once you get the hang of it).