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.)

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

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

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

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

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

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