Collating text files

[Senthil]

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

 

[John W. Vinson]

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]

 

[Senthil]

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.

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]

 

[John W. Vinson]

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]