software

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 helpTry 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-Proquot;Asterquot; wrote:

gt; Hi,
gt; I have multiple text files (*.txt) in 1 folder that I need to combine
gt; in 1excel spreadsheet.
gt; For each row in the text file, I would also need the filename of each
gt; file to appear in my excel, ie. in the first column. The next column
gt; will be the content.
gt;
gt; Example of source files:
gt; - File A01.TXT: Content:
gt; Row 1....
gt; Row 2...
gt; - File A02.TXT: Content:
gt; Row 1....
gt; Row 2...
gt;
gt; Combined Result in Excel:
gt; Row Column A Column B
gt; 1 A01.txt Row 1...
gt; 2 A01.txt Row 2...
gt; 3 A02.txt Row 1...
gt; 4 A02.txt Row 2...
gt;
gt; Thanks for any of u who can help
gt;
gt;

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:
---gt;Col headings (Example: Dept, PartNum, Desc, Price, whatever)
---gt;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)Datagt;Import External Datagt;New Database Query
gt;Databases: Get Text

Browse to one of the files and select it.
---gt;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
Datagt;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-Proquot;Ron Coderrequot; wrote:

gt; Try something like this:
gt;
gt; I'm pretty sure this is a job that MS Query can easily handle.
gt;
gt; First, make sure that each column in the text file has a unique column heading
gt; Follow the same delimiter pattern that the file already has. If the file has
gt; commas, separate the col headings with commas, etc.
gt;
gt; Next,
gt;
gt; Does that help?
gt;
gt; ***********
gt; Regards,
gt; Ron
gt;
gt; XL2002, WinXP-Pro
gt;
gt;
gt; quot;Asterquot; wrote:
gt;
gt; gt; Hi,
gt; gt; I have multiple text files (*.txt) in 1 folder that I need to combine
gt; gt; in 1excel spreadsheet.
gt; gt; For each row in the text file, I would also need the filename of each
gt; gt; file to appear in my excel, ie. in the first column. The next column
gt; gt; will be the content.
gt; gt;
gt; gt; Example of source files:
gt; gt; - File A01.TXT: Content:
gt; gt; Row 1....
gt; gt; Row 2...
gt; gt; - File A02.TXT: Content:
gt; gt; Row 1....
gt; gt; Row 2...
gt; gt;
gt; gt; Combined Result in Excel:
gt; gt; Row Column A Column B
gt; gt; 1 A01.txt Row 1...
gt; gt; 2 A01.txt Row 2...
gt; gt; 3 A02.txt Row 1...
gt; gt; 4 A02.txt Row 2...
gt; gt;
gt; gt; Thanks for any of u who can help
gt; gt;
gt; gt;

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?