+ Reply to Thread
Results 1 to 4 of 4

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

  1. #1
    Aster
    Guest

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

    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


  2. #2
    Ron Coderre
    Guest

    RE: Upload multiple text files into 1 excel worksheet + put the filena

    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


    "Aster" wrote:

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


  3. #3
    Ron Coderre
    Guest

    RE: Upload multiple text files into 1 excel worksheet + put the fi

    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


    "Ron Coderre" wrote:

    > 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
    >
    >
    > "Aster" wrote:
    >
    > > 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
    > >
    > >


  4. #4
    Aster
    Guest

    Re: Upload multiple text files into 1 excel worksheet + put the fi

    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?


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1