The boss wants another database. this one should be easy. I want to import information from four folders on different servers. All of the info is in text files. The fields are ID, Log, System, User, Day, Date and Time. How can I get this info into my tables the delete the files after import? I would like this to happen on database open so all of the info is current since the info from the forms ids continual. Thanks
-
-
Sounds like they've given you quite the project. Luckily it is a repeative motion project that can be modulated. Unforatunately, there's going to be several steps needed to achieve what you're looking for. Below I have included the first function that I could think of that will read a text file into a single string and return it. This then could be sent to the remaining functions for parsing, splitting, or moving as necessary. These of course will be forth coming from some I'm sure, but it would help to know what state the text files are in. Are they comma delmited or use some other method of determining one value from the next? This information will be helpful in splitting the values.
But as I promised here is a compact function that should read the text file in from the path you send it.
[Code=vb]
Public Function readDocIn(ByVal inDoc As String)
'Reads a source file into a string for later use
'Define
Dim fso As FileSystemObjec t
Dim docText As TextStream
'Initialize
Set fso = New FileSystemObjec t
Set docText = fso.OpenTextFil e(inDoc, ForReading, False, TristateUseDefa ult)
'Read in & clean up
readDocIn = docText.ReadAll
docText.Close
Set docText = Nothing
End Function
[/code]
I hope this helps and as more information becomes available I'll try and keep getting functions of the module together.
- Minion -
Space delimited but one problem I may have is that the extention is .log rather than .txt. The logon script that I have puts the info this way.Comment
-
Would it be easier if I had a button for him to push? (heaven knows I push his)Comment
-
*grins*
The extention does not really matter. I use a version of this function to read in XML files and alter them. As long as it can be read in Notepad it will work. As for the button you can add this to a button if you like and I have a version that will let you browse for a file too. However, the same components will need to be put into play regardless of how the file is read in. At least as I see it but I'm still learning.
If the file is going to be space delimited with no spaces appearing anywhere else it should be a simple matter to generate a parsing script that will simply break the string when it hits a space. If there are spaces in the values this won't work of course and we'll need to figure another method.
Ultimately I'm trying to make the functions as generic as possible so that they are not specialized by file and can be used for the whole process.
Bear with me if you would I'm still learning much of this as I go (and get asked to do things on my various projects).
Hope to be of further help.
- Minion -Comment
-
Assuming your Text Files are in a Comma Delimited Format, the first step in the process is to Import the Data into Tables in the Current Database. The following code will Import the four Text Files into 4 Tables consecutively named tblImport_1 thru tblImport_4. The code also assumes that no Field Names exist in the Text Files, if this is not the case, replace the False Parameter to True. When you have completed step 1, let us know:Originally posted by rcollins
[CODE=vb]
DoCmd.TransferT ext acImportDelim, , "tblImport_ 1", "<Absolute_Path _to_File_1>", False
DoCmd.TransferT ext acImportDelim, , "tblImport_ 2", "<Absolute_Path _to_File_2>", False
DoCmd.TransferT ext acImportDelim, , "tblImport_ 3", "<Absolute_Path _to_File_3>", False
DoCmd.TransferT ext acImportDelim, , "tblImport_ 4", "<Absolute_Path _to_File_4>", False[/CODE]Comment
-
Hello Minion, it is probably a good idea to mention the required Reference to Microsoft Scripting Runtime. I've seen a lot of OPs go crazy assuming that they just cannot get the code to work. See you around.Originally posted by MinionComment
-
Map out your entire process and make sure your app takes follows the process.Originally posted by rcollins
I would not do this on the opening of the form. What if you import the data and delete the import files and then open the app again later. Your tables are now empty and no source to pull data from. You can code around this but lets try to keep this simple. It would be better to attach code to a command button ("Refresh Data" or whatever you want to call it.)
You can use the "advanced" option of the import wizard to help build a import specification. Just remember what you name it. This import spec is saved internally to the mdb.
1. Are all of the file layouts identical? If so only one import spec required.
2. Is this a delimited (acImportDelim) or a fixed width (acImportFixed) text file being imported?
3. Are there multiple users whose drive mappings may be different to the file servers? (FileServer01 may mapped to F: on one machine and L: on another)
If so (and this is best habit anyhow use the UNC to the server - "\\servername\f oldername\subfo ldername\filena me.txt"
4. Are all 4 files being imported to the same table or 4 different tables?
5. Are you clearing data from the import tables before you import?
Docmd.Runsql "Delete MyImportTable.* from MyImportTable;" 'to clear import table
'Repeat for each file being imported
DoCmd.TransferT ext acImportDelim, "MyImportSp ec", "MyImportTable" , "CompleteUNCtoF ile1"Comment
-
Lets see If I can answer all of these.Originally posted by jaxjagfan
1. Yes all of the layouts are identicle
2. Space Delimitated
3. Mapping wont matter because I will use UNC to the servers
4. All to the same table (probably the only table this database will have
5. It is the log files I want to get rid of not clear tables. That way these will be new every time the info is imported. The log files are automatically generated by a logon script.
For right now I have files saved into C:\MyFiles do that I can get it to work then I will change it to the UNC.
When I try the code from ADezii if I change the extention to .txt it will import, but not as .log. Also you said that this would work for comma delim but remember mine is space delim (there are no spaces in the fields to worry about) so mine all goes into one field.
I already have been trying this:
It wont let me import .logCode:Private Sub Command0_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*.log") Do While Len(strfile) > 0 DoCmd.TransferText acImportDelim, "Log Import Specification", "tblLog", "c:\MyFiles\" & strfile, True 'delete the file (consider moving it to an Archive folder instead.) Kill "c:\MyFiles\" & strfile strfile = Dir Loop End SubComment
-
In your import specification change the delimiter to SPACE Vice COMMA if you haven't done so already. Is the name of the Log file always the same or are you creating a log file with a date as part of the name? Consider consistent name since the date is a column in the file and doesn't need to be part of the name.Originally posted by rcollins
Code:Dim strFile as string Dim strPath as string Dim i as integer strFile = "MyLog.log" Do While i <=4 Select Case i Case 1 strpath = "UNCPath1" Case 2 strpath = "UNCPath2" Case 3 strpath = "UNCPath3" Case 4 strpath = "UNCPath4" End Select DoCmd.TransferText acImportDelim, "Log Import Specification", "tblLog", strpath & strFile, True Kill strpath & strFile Loop
Comment
Comment