Upload multiple text files into 1 excel worksheet + put the filename as the first column in the work

[Aster]

Hi,
I have multiple text files (*.txt) in 1 folder that I need to combine
in 1excel spreadsheet.
For each row in the text file, I would also need the filename of each
file to appear in my excel, ie. in the first column. The next column
will be the content.

Example of source files:
- File A01.TXT: Content:
Row 1....
Row 2...
- File A02.TXT: Content:
Row 1....
Row 2...

Combined Result in Excel:
Row Column A Column B
1 A01.txt Row 1...
2 A01.txt Row 2...
3 A02.txt Row 1...
4 A02.txt Row 2...

Thanks for any of u who can help

 

[Guest]

Try something like this:

I'm pretty sure this is a job that MS Query can easily handle.

First, make sure that each column in the text file has a unique column heading
Follow the same delimiter pattern that the file already has. If the file has
commas, separate the col headings with commas, etc.

Next,

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro

 

[Guest]

Obviously, half a post wouldn't help much, would it?

I'll start over....

This example uses 2 comma-delimited text files as data sources.

Assumptions:
The data in each file is structured like a table:
--->Col headings (Example: Dept, PartNum, Desc, Price, whatever)
--->Columns are in the same order.

Also, you'll need a Text File data source nameto continue. I've had a Get
Text dsn for so long that I can't remember if it's standard with windows or
not. Creating one is easy if you need it though.

Starting with an empty worksheet:
1)Select the cell where you want the consolidated data to start

2)Data>Import External Data>New Database Query

Databases: Get Text

Browse to one of the files and select it.
--->Accept defaults until the next step.

At The last screen double-click the asterisk at the top of the table field
list to see the fields in query window.

Click the [SQL] button

Replace the displayed SQL code with an adapted version of this:

SELECT 'File_1' , *
FROM `C:\ExcelQueries`\A01.txt
UNION ALL
SELECT 'File_2' , *
FROM `C:\ExcelQueries`\A02.txt

(Note: the apostrophes in the SQL code ( ` )are located on the same key as
the tilde (~) )

Return the data to Excel.

You'll get what you requested: One file appended below the other with a
source reference on each row.

After that....to get the latest data just click in the data range then
Data>Refresh Data.
(You can edit the query at any time to add/remove data sources and/or fields.)

Is that something you can work with?

***********
Regards,
Ron

XL2002, WinXP-Pro

 

[Aster]

Hi Ron, thanks alot for your help, but what if I have 100s of text
files in that folder, is there any other way i can do without writing
each of the file name in the query?