Two text files

Hi guys: I have two text files containing information of simple name, address and ssn. How to i merge both files into one using vba or any other way and then import them into one table.

Thanks
mustish1Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

als315Commented:
In VBA you can import all files from some folder. Can you upload sample files?
0
mustish1Author Commented:
I create two list boxes and I want to import the two text files in each list box. When user press the submit button it will load the third list box with all the items from the first and the second? How to i do that

Thanks.

I have the company data in the form of text file it just contains information about employees name, address and SSN
0
unknown_routineCommented:
Easiest way:


1: open first  textfile in the access using the open file menu(choose all files  to be able to see the text file). Go through the wizard to create table1


2: open secondtextfile in the access using the open file menu. Go through the wizard to create table2


3: In Access  make the following query
SELECT * into table3
from  table1


4:In Access  make the following query
insert into table3
select * from  table2
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

mustish1Author Commented:
Thanks. Can you please tell me how to I do that using VBA. When user press the submit button, it automatically load the two text files name personnel1.txt and personnel2.txt from folder c:\personnel into each listbox and load the third listbox by means of merging the two into it. Files are always loaded from the same folder  c:\personnel
0
Jeffrey CoachmanMIS LiasonCommented:
What unknown_routine posted is the standard way of doing this.

Most Raw text files cannot simply be "Combined",...because of Header info and/or special characters at the start or end of the file.
0
unknown_routineCommented:
@mustish1 my solution above is not using VBA at all. Since inyour question you said
"vba or any other way "

If you are not clear about any part of the solution let me know.
0
mustish1Author Commented:
I create this form.
MailMerge.accdb
0
mustish1Author Commented:
When user press the load file button it load the text files into two list box and also merge the two files into the 3rd list box. Same time merge data also save into MergeData table.

Thanks.
0
Jeffrey CoachmanMIS LiasonCommented:
Did you read our post?
There was no need to create that form.

Import the two files normally, (saving the import specification)
Then use the SQL unknown_routine posted to combine the files.

Then you can run the import code and the SQL in one command
0
mustish1Author Commented:
Yes i can do in that way. But we are like 5 peoples in the office and not every one knows Access. Thats why i want to make a form and than copy it on the desktop of each computer so that every one can use it.

Thanks.
0
PatHartmanCommented:
In the click event of a button -

As long as the text files are a format that Access understands where all records are of the same type except that the first row may contain column headers, You can do it very simply using the transferText method.  Either of the following will work depending on whether the file is delimited (csv) or fixed format (some other extension, usually txt).  The True argument at the end of the statement specifies whether or not the first row contains column headings.    
Docmd.TransferText acImportDelim, "OptionalImportSpec","TableNameToImportTo", "YOurFileName", True 

OR

DoCmd.TransferText acImportFixed, "OptionalImportSpec" , "TableNameToImportTo", "YourFileName", True 

Open in new window

The TransferText method doesn't give you the option to append records but if you specify the name of a table that exists, it will append the records to that table.  So.

DoCmd.TransferText acImportDelim, , "DestinationTableName", "FromFileName", True
DoCmd.TransferText acImportDelim,, "DestinationTableName", "FromFileName2", True

Open in new window


This procedure assumes that there is no overlap in the data values.  If you have overlap, we need to do something more sophisticated.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mustish1Author Commented:
Is this code goes into the button

Private Sub Command6_Click()
Docmd.TransferText acImportDelim, "OptionalImportSpec","MergeData", "c:\personne", True
End Sub
0
mustish1Author Commented:
Can any one please tell me where i use the above code?
0
mustish1Author Commented:
1: open first  textfile in the access using the open file menu(choose all files  to be able to see the text file). Go through the wizard to create table1

ok i follow the steps and create two tables name table1 and table2 but when i create table2 table1 is not showing in the table area only table2 is showing.

Thanks.


2: open secondtextfile in the access using the open file menu. Go through the wizard to create table2


3: In Access  make the following query
SELECT * into table3
from  table1


4:In Access  make the following query
insert into table3
select * from  table2
tables.jpg
0
mustish1Author Commented:
ok. I just use the right click and import from both files but why it makes error table too



Data1.txt
Harris    122    33
John      343    22


Data2.txt
Mary     987    92
John      456    76
error.jpg
0
PatHartmanCommented:
If you look at the import errors, you will see that it is telling you that some of the columns have invalid data.  I can't tell by looking at the post what format your file is.  Is it fixed width?  That is what it looks like.  and Record 2 in the first file may not have enough spaces to pad out the name field.

Sometimes importing the same file into Excel will give you some insight into the problem.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.