Automated Text File Import "Achez" <achez.macdonald[ at ]gmail.com> 17.07.2006 15:15:58 Hello, All: I'm designing a very simple database that will import data (all text files, all different names, each text file using the same column headings, one common directory) basically for reference purposes. I found a posting (titled "import multiple text files into Access (different file names)" from February 2005...see link at the end) using macros and a hidden form. That looks like it would work to import all files from a directory regardless of title. What would one do if they wanted to tell the database which file to import? In the case that I'm referring to above, a text box on a hidden form holds the information needed by the macro (*.txt) to import the right file. Would it then make sense to adjust the macro to allow for user input (e.g. the file name to import), and use a control button to start the macro? The text file that will be imported is a generated file from a hardware programming utility. The title will reflect the order number associated with the particular piece of hardware. Usually, it will be a 5 or 6 digit number, although sometimes it will have a letter or an underscore (I'm not sure if this makes any difference in designing the VB code or macro). Each file size will be different, and each file will contain one or more lines of information, but each line will have data that reflects common column headings. The values are separated by commas. The end user is assumed to have very little if any database experience, and would most likely have trouble using Access's import wizard. Any help is greatly appreciated. -Achez http://groups.google.ca/group/microsoft.public.access.externaldata/browse_thread/thread/9c67cbbc437c6be4/aa879d1dff2b2b1a?lnk=st&q=macro+to+import+text+file+into+access&rnum=3&hl=en#aa879d1dff2b2b1a

RE: Automated Text File Import DavidAtCaspian 18.07.2006 14:25:02 Achez: Your users certainly would have trouble using the wizard because it doesn't work. Microsoft broke it with their latest service pack, and it is virtually impossible to get the hotfix (at least if you're in the UK) Although they broke it, and it's not exactly an esoteric requirement, if you want the fix you have to phone the 'support' people. This costs 17p per minute in the UK, and so far I have never been able to get through. In the olden days you could have used the common dialog control, with prefilled directory path and filters to only show *.txt files, but Micrrosoft no longer allow you to use this because they couldn't get it to work with different versions. (Yes I know you only have the one version, but you still can't have it). You can always use the API directly if you want to while away an hour or so. What you can do is add excel as a reference, and use Excel.Application.GetOpenFilename To allow users to select the file. (I'll post a bit of code as a starter if you like) If you also yourself, use the advanced text import wizard to define an import spec and then in your code use docmd.TransferText using your presaved spec name. All should go quite smoothly (on the assumption that the text import does work they way it is supposed to) Dav id "Achez" wrote: [Quoted Text] > Hello, All: > > I'm designing a very simple database that will import data (all text > files, all different names, each text file using the same column > headings, one common directory) basically for reference purposes. I > found a posting (titled "import multiple text files into Access > (different file names)" from February 2005...see link at the end) using > macros and a hidden form. That looks like it would work to import all > files from a directory regardless of title. What would one do if they > wanted to tell the database which file to import? In the case that I'm > referring to above, a text box on a hidden form holds the information > needed by the macro (*.txt) to import the right file. Would it then > make sense to adjust the macro to allow for user input (e.g. the file > name to import), and use a control button to start the macro? > > The text file that will be imported is a generated file from a hardware > programming utility. The title will reflect the order number > associated with the particular piece of hardware. Usually, it will be > a 5 or 6 digit number, although sometimes it will have a letter or an > underscore (I'm not sure if this makes any difference in designing the > VB code or macro). Each file size will be different, and each file > will contain one or more lines of information, but each line will have > data that reflects common column headings. The values are separated by > commas. The end user is assumed to have very little if any database > experience, and would most likely have trouble using Access's import > wizard. > > Any help is greatly appreciated. > > -Achez > > http://groups.google.ca/group/microsoft.public.access.externaldata/browse_thread/thread/9c67cbbc437c6be4/aa879d1dff2b2b1a?lnk=st&q=macro+to+import+text+file+into+access&rnum=3&hl=en#aa879d1dff2b2b1a > >

Re: Automated Text File Import John Nurick <j.mapSoN.nurick[ at ]dial.pipex.com> 18.07.2006 20:09:08 Hi Achez, The following, which was originally posted here by Joe Fallon, gives a basic procedure for importing all the files in a folder. ----Begin quote Private Sub btnImportAllFiles_Click() 'procedure to import all files in a directory and delete them. 'assumes they are all the correct format for an ASCII delimited import. Dim strfile As String ChDir ("c:\MyFiles") strfile = Dir("FileName*.*") Do While Len(strfile) > 0 DoCmd.TransferText acImportDelim, "ImportSpecName", _ "AccessTableName", "c:\MyFiles\" & strfile, True 'delete the file (consider moving it to an Archive folder instead.) Kill "c:\MyFiles\" & strfile strfile = Dir Loop End Sub This sample code goes in the Click eventof a button named "btnImportAllFiles" on a form. You need to manually imprt one of the files and use the wizard to set all the properties in the spec. Do *not* hit Finish! Click Advanced, Save As and give the spec a name. ----End quote On 17 Jul 2006 08:15:58 -0700, "Achez" <achez.macdonald[ at ]gmail.com> wrote: [Quoted Text] >Hello, All: > >I'm designing a very simple database that will import data (all text >files, all different names, each text file using the same column >headings, one common directory) basically for reference purposes. I >found a posting (titled "import multiple text files into Access >(different file names)" from February 2005...see link at the end) using >macros and a hidden form. That looks like it would work to import all >files from a directory regardless of title. What would one do if they >wanted to tell the database which file to import? In the case that I'm >referring to above, a text box on a hidden form holds the information >needed by the macro (*.txt) to import the right file. Would it then >make sense to adjust the macro to allow for user input (e.g. the file >name to import), and use a control button to start the macro? > >The text file that will be imported is a generated file from a hardware >programming utility. The title will reflect the order number >associated with the particular piece of hardware. Usually, it will be >a 5 or 6 digit number, although sometimes it will have a letter or an >underscore (I'm not sure if this makes any difference in designing the >VB code or macro). Each file size will be different, and each file >will contain one or more lines of information, but each line will have >data that reflects common column headings. The values are separated by >commas. The end user is assumed to have very little if any database >experience, and would most likely have trouble using Access's import >wizard. > >Any help is greatly appreciated. > >-Achez > >http://groups.google.ca/group/microsoft.public.access.externaldata/browse_thread/thread/9c67cbbc437c6be4/aa879d1dff2b2b1a?lnk=st&q=macro+to+import+text+file+into+access&rnum=3&hl=en#aa879d1dff2b2b1a -- John Nurick [Microsoft Access MVP] Please respond in the newgroup and not by email.

Re: Automated Text File Import "Achez" <achez.macdonald[ at ]gmail.com> 20.07.2006 14:15:47 John and David...thank you both. For anyone else reading this as reference, both methods work perfectly! Kindest regards, Achez John Nurick wrote: [Quoted Text] > Hi Achez, > > The following, which was originally posted here by Joe Fallon, gives a > basic procedure for importing all the files in a folder. > > ----Begin quote > Private Sub btnImportAllFiles_Click() > 'procedure to import all files in a directory and delete them. > 'assumes they are all the correct format for an ASCII delimited import. > Dim strfile As String > > > ChDir ("c:\MyFiles") > strfile = Dir("FileName*.*") > Do While Len(strfile) > 0 > DoCmd.TransferText acImportDelim, "ImportSpecName", _ > "AccessTableName", "c:\MyFiles\" & strfile, True > 'delete the file (consider moving it to an Archive folder instead.) > Kill "c:\MyFiles\" & strfile > strfile = Dir > Loop > End Sub > > > This sample code goes in the Click eventof a button named > "btnImportAllFiles" on a form. You need to manually imprt one of the > files and use the wizard to set all the properties in the spec. > Do *not* hit Finish! Click Advanced, Save As and give the spec a name. > ----End quote > > On 17 Jul 2006 08:15:58 -0700, "Achez" <achez.macdonald[ at ]gmail.com> > wrote: > > > -- > John Nurick [Microsoft Access MVP] > > Please respond in the newgroup and not by email.