Connecting Tech Pros Worldwide Forums | Help | Site Map
bytes > topic > access > answers

New database project

rcollins's Avatar
Familiar Sight
  
Join Date: Aug 2006
Location: Grand Junction, CO
Posts: 233
#1: Dec 31 '07
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

Minion's Avatar
Expert
  
Join Date: Dec 2007
Posts: 98
#2: Dec 31 '07

re: New database project

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.

Expand|Select|Wrap|Line Numbers
  1. Public Function readDocIn(ByVal inDoc As String)
  2. 'Reads a source file into a string for later use
  3.  
  4. 'Define
  5.     Dim fso As FileSystemObject
  6.     Dim docText As TextStream
  7.  
  8. 'Initialize
  9.     Set fso = New FileSystemObject
  10.     Set docText = fso.OpenTextFile(inDoc, ForReading, False, TristateUseDefault)
  11.  
  12. 'Read in & clean up
  13.     readDocIn = docText.ReadAll
  14.     docText.Close
  15.     Set docText = Nothing
  16.  
  17. End Function
  18.  
I hope this helps and as more information becomes available I'll try and keep getting functions of the module together.

- Minion
rcollins's Avatar
Familiar Sight
  
Join Date: Aug 2006
Location: Grand Junction, CO
Posts: 233
#3: Dec 31 '07

re: New database project

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.
rcollins's Avatar
Familiar Sight
  
Join Date: Aug 2006
Location: Grand Junction, CO
Posts: 233
#4: Dec 31 '07

re: New database project

Would it be easier if I had a button for him to push? (heaven knows I push his)
Minion's Avatar
Expert
  
Join Date: Dec 2007
Posts: 98
#5: Dec 31 '07

re: New database project

*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 -
ADezii's Avatar
Expert
  
Join Date: Apr 2006
Location: Philadelphia
Posts: 5,219
#6: Dec 31 '07

re: New database project

Quote:

Originally Posted by rcollins

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

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:
Expand|Select|Wrap|Line Numbers
  1. DoCmd.TransferText acImportDelim, , "tblImport_1", "<Absolute_Path_to_File_1>", False
  2. DoCmd.TransferText acImportDelim, , "tblImport_2", "<Absolute_Path_to_File_2>", False
  3. DoCmd.TransferText acImportDelim, , "tblImport_3", "<Absolute_Path_to_File_3>", False
  4. DoCmd.TransferText acImportDelim, , "tblImport_4", "<Absolute_Path_to_File_4>", False
ADezii's Avatar
Expert
  
Join Date: Apr 2006
Location: Philadelphia
Posts: 5,219
#7: Dec 31 '07

re: New database project

Quote:

Originally Posted by Minion

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.

Expand|Select|Wrap|Line Numbers
  1. Public Function readDocIn(ByVal inDoc As String)
  2. 'Reads a source file into a string for later use
  3.  
  4. 'Define
  5.     Dim fso As FileSystemObject
  6.     Dim docText As TextStream
  7.  
  8. 'Initialize
  9.     Set fso = New FileSystemObject
  10.     Set docText = fso.OpenTextFile(inDoc, ForReading, False, TristateUseDefault)
  11.  
  12. 'Read in & clean up
  13.     readDocIn = docText.ReadAll
  14.     docText.Close
  15.     Set docText = Nothing
  16.  
  17. End Function
  18.  
I hope this helps and as more information becomes available I'll try and keep getting functions of the module together.

- Minion

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.
jaxjagfan's Avatar
Expert
  
Join Date: Dec 2007
Location: Jax, FL
Posts: 253
#8: Dec 31 '07

re: New database project

Quote:

Originally Posted by rcollins

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

Map out your entire process and make sure your app takes follows the process.
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\foldername\subfoldername\filename.tx t"
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.TransferText acImportDelim, "MyImportSpec", "MyImportTable", "CompleteUNCtoFile1"
rcollins's Avatar
Familiar Sight
  
Join Date: Aug 2006
Location: Grand Junction, CO
Posts: 233
#9: Dec 31 '07

re: New database project

Quote:

Originally Posted by jaxjagfan

Map out your entire process and make sure your app takes follows the process.
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\foldername\subfoldername\filename.tx t"
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.TransferText acImportDelim, "MyImportSpec", "MyImportTable", "CompleteUNCtoFile1"

Lets see If I can answer all of these.
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:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Command0_Click()
  2. 'procedure to import all files in a directory and delete them. 'assumes they are all the correct format for an ASCII delimited import.
  3.  
  4. Dim strfile As String
  5.  
  6. ChDir ("c:\MyFiles")
  7. strfile = Dir("FileName*.log")
  8. Do While Len(strfile) > 0
  9. DoCmd.TransferText acImportDelim, "Log Import Specification", "tblLog", "c:\MyFiles\" & strfile, True
  10.  
  11. 'delete the file (consider moving it to an Archive folder instead.)
  12. Kill "c:\MyFiles\" & strfile
  13. strfile = Dir
  14. Loop
  15. End Sub
It wont let me import .log
jaxjagfan's Avatar
Expert
  
Join Date: Dec 2007
Location: Jax, FL
Posts: 253
#10: Dec 31 '07

re: New database project

Quote:

Originally Posted by rcollins

Lets see If I can answer all of these.
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:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Command0_Click()
  2. 'procedure to import all files in a directory and delete them. 'assumes they are all the correct format for an ASCII delimited import.
  3.  
  4. Dim strfile As String
  5.  
  6. ChDir ("c:\MyFiles")
  7. strfile = Dir("FileName*.log")
  8. Do While Len(strfile) > 0
  9. DoCmd.TransferText acImportDelim, "Log Import Specification", "tblLog", "c:\MyFiles\" & strfile, True
  10.  
  11. 'delete the file (consider moving it to an Archive folder instead.)
  12. Kill "c:\MyFiles\" & strfile
  13. strfile = Dir
  14. Loop
  15. End Sub
  16.  
It wont let me import .log

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.
Expand|Select|Wrap|Line Numbers
  1. Dim strFile as string
  2. Dim strPath as string
  3. Dim i as integer
  4. strFile = "MyLog.log"
  5. Do While i <=4
  6. Select Case i
  7. Case 1
  8. strpath = "UNCPath1"
  9. Case 2
  10. strpath = "UNCPath2"
  11. Case 3
  12. strpath = "UNCPath3"
  13. Case 4
  14. strpath = "UNCPath4"
  15. End Select
  16. DoCmd.TransferText acImportDelim, "Log Import Specification", "tblLog", strpath & strFile, True
  17. Kill strpath & strFile
  18. Loop
Reply

« previous question | next question »

Similar Microsoft Access / VBA bytes

/bytes/development

/bytes/it

Forums
Visit our community forums for general discussions and latest on Bytes

Our staff and community areas have moved to bytes.com/forums/

/bytes/about

We are a network of experts and professionals in IT and software development that help one another with answers to tough questions and share insights. Get the best answers to your questions from over 226,510 network members.

dev questions:
algorithms | asp | asp.net | c/c++ | coldfusion | c# | flash | html/css | java | javascript |
mobile dev | .net | perl | php | python | ruby | software dev | vb.net | visual basic | xml
it questions:
access | apache | careers | db2 | iis | macosx | mysql | networking | oracle | postgresql | sql server | unix | windows

About Bytes | Copyright 1995-2009 BYTES. All rights Reserved