A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Collating text files



 
 
Thread Tools Display Modes
  #1  
Old December 25th, 2007, 06:02 PM posted to microsoft.public.access.queries
senthil
external usenet poster
 
Posts: 24
Default Collating text files

Hi,

There are 3 text files - text1.txt, text2.txt, text3.txt. Each of these
files have a records of 10 digits each.

Text1.txt Text2.txt Text3.txt
1234567890 0123456789 1234506789
9638527410 9876543210 4567891230
2583691470 7538694210 9516238470

I want to have a consolidated (txt file or table) with these numbers and
their filenames as below.

Field1 Field2
1234567890 Text1
9638527410 Text1
2583691470 Text1
0123456789 Text2
9876543210 Text2
7538694210 Text2
9516238470 Text3
4567891230 Text3
1234506789 Text3

Any idea...pls help
  #2  
Old December 25th, 2007, 08:44 PM posted to microsoft.public.access.queries
John W. Vinson
external usenet poster
 
Posts: 18,261
Default Collating text files

On Tue, 25 Dec 2007 09:02:01 -0800, Senthil
wrote:

Hi,

There are 3 text files - text1.txt, text2.txt, text3.txt. Each of these
files have a records of 10 digits each.

Text1.txt Text2.txt Text3.txt
1234567890 0123456789 1234506789
9638527410 9876543210 4567891230
2583691470 7538694210 9516238470

I want to have a consolidated (txt file or table) with these numbers and
their filenames as below.

Field1 Field2
1234567890 Text1
9638527410 Text1
2583691470 Text1
0123456789 Text2
9876543210 Text2
7538694210 Text2
9516238470 Text3
4567891230 Text3
1234506789 Text3

Any idea...pls help


You need a couple of steps here. First, use File... Get External Data... Link
to link to the text files; select "Text" from Files Of Type in the link
dialog. Do this for each file; you'll end up with three "tables" named Text1,
Text2 and Text3.

Then create a UNION query in the SQL window (you can't use the grid for this).
Edit it to use your actual linked-table and field names:

SELECT [Text1].[Field1] AS Field1, "Text1" AS Field2 FROM [Text1]
UNION ALL
SELECT [Text2].[Field1], "Text2" FROM [Text2]
UNION ALL
SELECT [Text3].[Field1], "Text3" FROM [Text3];

Save this query as uniAllText.

Finally, create a permanent table to store your data, and base an Append query
on uniAllText, appending the data into this new table.

John W. Vinson [MVP]
  #3  
Old December 27th, 2007, 06:01 PM posted to microsoft.public.access.queries
senthil
external usenet poster
 
Posts: 24
Default Collating text files

Thanks john.

But i have a problem here. The text files are not 3...they are around 30
files and each time i do a link and change the file name is tedious. Any
other way out.

"John W. Vinson" wrote:

On Tue, 25 Dec 2007 09:02:01 -0800, Senthil
wrote:

Hi,

There are 3 text files - text1.txt, text2.txt, text3.txt. Each of these
files have a records of 10 digits each.

Text1.txt Text2.txt Text3.txt
1234567890 0123456789 1234506789
9638527410 9876543210 4567891230
2583691470 7538694210 9516238470

I want to have a consolidated (txt file or table) with these numbers and
their filenames as below.

Field1 Field2
1234567890 Text1
9638527410 Text1
2583691470 Text1
0123456789 Text2
9876543210 Text2
7538694210 Text2
9516238470 Text3
4567891230 Text3
1234506789 Text3

Any idea...pls help


You need a couple of steps here. First, use File... Get External Data... Link
to link to the text files; select "Text" from Files Of Type in the link
dialog. Do this for each file; you'll end up with three "tables" named Text1,
Text2 and Text3.

Then create a UNION query in the SQL window (you can't use the grid for this).
Edit it to use your actual linked-table and field names:

SELECT [Text1].[Field1] AS Field1, "Text1" AS Field2 FROM [Text1]
UNION ALL
SELECT [Text2].[Field1], "Text2" FROM [Text2]
UNION ALL
SELECT [Text3].[Field1], "Text3" FROM [Text3];

Save this query as uniAllText.

Finally, create a permanent table to store your data, and base an Append query
on uniAllText, appending the data into this new table.

John W. Vinson [MVP]

  #4  
Old December 27th, 2007, 08:21 PM posted to microsoft.public.access.queries
John W. Vinson
external usenet poster
 
Posts: 18,261
Default Collating text files

On Thu, 27 Dec 2007 09:01:00 -0800, Senthil
wrote:

Thanks john.

But i have a problem here. The text files are not 3...they are around 30
files and each time i do a link and change the file name is tedious. Any
other way out.


You'll need to use the Dir() function to list the filenames and pull them into
variables. How can you tell which files in the folder need to be imported and
which don't?

John W. Vinson [MVP]
 




Thread Tools
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

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 12:26 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.