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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|