Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old March 10th, 2005, 04:54 AM
Authorized User
 
Join Date: Mar 2005
Location: , , Finland.
Posts: 10
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to Suomi
Default Importing Multiple files in Multiple tables Versi

Hi all,
I am a newbie to ACCESS. Just know.. it is powerful tool for databases.

Here is task statement:
* I have predifened 2 tables(Table A and TableB). With predefined relationship among them.
* I have 5 .text files to be imported to the above tables. ( file1,3,4 to A and 2,5 to B)
* These files are needed to be appended one after another.

Is it possible to do this whole programically?
(There is one problem with the .text files. The contents looks in columns but they are not as the numbr of spaces/Tabs between two values in the same row are not always constants.)

Reply With Quote
  #2 (permalink)  
Old March 10th, 2005, 09:41 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

You are going to have to do manual ETL work on these text files BEFORE you import them.

Open each file in Excel, and align the columns and clean the data as needed. Then copy and paste all the worksheets to one worksheet.

Then insert that worksheet - Insert>Table>Import Table.

Use the Import table wizard to find and import your Excel spreadsheet. Import it into a new or existing table, and let Access add the primary key.

HTH

P.S. Did you get that Beginning Access book yet? Try Alison Balter's Mastering Access 2002 Desktop Development, and Wrox Beginning Access 2003 VBA.:D

mmcdonal
Reply With Quote
  #3 (permalink)  
Old March 21st, 2005, 05:12 AM
Authorized User
 
Join Date: Mar 2005
Location: , , Finland.
Posts: 10
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to Suomi
Default

Hi mmcdonal,
Thanks alot for your reply.
1. Actually I want to avoid the manual work. As it is tedious to do it again and again. I will have always the files with the same names and almost i will have the same pattern for them to populate a certain database. Do you have any idea to do it programwise. Let say using VB?
2. I have one book Mastering ACCESS 2002 by Celeste Robinson and ALan Simpson. So far it is ok :(
:)
Again thanks alot for ur message.
BR,
Suomi

Reply With Quote
  #4 (permalink)  
Old March 21st, 2005, 09:19 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

Do you have more specific information on the text files, and then on the database table you want to store them all in? Do you want to keep a history, so retain older data when you pull in the new data? What do you want to do with the old text files after you import them?

I would suggest the following steps:
1. Use a button to manually pull this data in.
2. On the button attach code to do the following:
   a. Look for and confirm the existence of each file.
   b. make a back up copy of each file in an archive folder.
   c. If the files will always have the same name, then add the date to the beginning of the archive copy name.
   d. Open each file and read it one line at a time.
   c. Pull in each line according to the parsing you do in the script.
   d. When the script is done with the file, overwrite it to empty it, or delete it.
   e. Move on to the next file, and format them with parsing, and pull them into the proper table.
   f. delete them as well.
   g. Count each record added and show a message box after each table is done showing the count of the records added to the table.

Is this what you want to do? If so, post the text file structure and the database structure, and we can help out.

Thanks,


mmcdonal
Reply With Quote
  #5 (permalink)  
Old June 1st, 2005, 03:32 AM
Authorized User
 
Join Date: Mar 2005
Location: , , Finland.
Posts: 10
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to Suomi
Default

Hi mmcdonal, after long delay.
Actually I have been busy my thesis :)
I am thankful to your in detail answeres.

After going into the problem I found out one solution of using 'TransferText' in a macro/VBA.
The bad thing that is worrying me is the 'Specification Name' that I would need in both case i.e. in case of Macro and as well as in VBA.
For my purpose I'll need to parse data with multiple seperator. If there are consective seperators then they are grouped as one (the same as we have it in importing a txt file into Excel).
Is it possible?

Another option is to follow the proceedure that you wrote to me in your last email. I agree with that proceedure but the only worry is that I have feelings that it will be slower than the TransferText way.

BR,
Suomi

Sub TestTransferText()
DoCmd.TransferText acImportDelim, , _
"tblEmails", "C:\Temp\emails.txt", True
End Sub

Reply With Quote
  #6 (permalink)  
Old June 1st, 2005, 06:49 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

Well, here is how I do this same thing with data that is pulled every two weeks on my job.

1. Someone does the manual ETL on the spreadsheets to make sure all the columns are the same each pull, and the fields are properly named and contain the right data.
2. They save the files to a directory, and then name them with the date and content, like this:
    2005_05_23_TEMP.xls, for example.
3. I have a series of 26 buttons, that are invisible until they see their file is in the folder, and then they become visible.
4. The user can click the button, then, and here is the code that executes:

'==========
DoCmd.DeleteObject acTable, "tblPosition"
DoCmd.CopyObject , "tblPosition", acTable, "tblP"
DoCmd.TransferSpreadsheet acImport, , "tblPosition", "I:\WPM\Data\NFC\2005_05_23PERM.xls", True
DoCmd.TransferSpreadsheet acImport, , "tblPosition", "I:\WPM\Data\NFC\2005_05_23TEMP.xls", True
MsgBox "Import Finished."
'==========

In your case, I guess you wouldn't delete the old data. Mine is a viewer for the current batch of data. I have a backup table in this case called "tblP" that is the proper structure for the data being retrieved that I use to rebuild the receiving table.

This allows the user to view data for each of the previous pay periods at will, and not have extraneous data from other pay periods.

You may want to stack your data as new data comes in, so just don't delete the table.

Really, taking the time to do the manual hand work on the Excel spreadsheet will make this amore serviceable process.

HTH


mmcdonal
Reply With Quote
  #7 (permalink)  
Old June 1st, 2005, 08:47 AM
Authorized User
 
Join Date: Mar 2005
Location: , , Finland.
Posts: 10
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to Suomi
Default

Hi mmcdonal,
I appreciate your prompt reply. Thanks for it.
Actually I would like to avoid the Excel step at my best. As it will slow down the whole process and moreover it is manual.
Actually if I import a table in Excel manualy then I get option of having more than one seperators to be merged as one when they occure together. But I don't have this in ACCESS. Can you help me with calling my text contents into ACCESS using VBA eighhter all together or line by line.
My text file looks like:
1 22 33 44 55 77 88
2 22 33 44 55 773 884
3 22 5433 44 55 77 88
4 2352 33 44 55 77e 88
5 22 ww33 t44 5g5 77 88

NOTE:[Somewhere I have two spaces in the same row or sometimes I have combination of tabs and spaces]
BR,
SUOMI

Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Importing Multiple Files Into Excel Using VB bsac Excel VBA 4 January 27th, 2014 11:37 AM
Importing multiple text files to sql server db tammyk SQL Server DTS 1 April 25th, 2008 12:07 AM
data from multiple tables keyvanjan Classic ASP Basics 1 January 24th, 2006 06:32 PM
Multiple Recordsets from Multiple Tables TSEROOGY Classic ASP Databases 2 December 28th, 2004 12:45 PM
Multiple detail tables sudeesh_nd Crystal Reports 0 August 13th, 2004 12:21 AM



All times are GMT -4. The time now is 08:19 PM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.