1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Transffering and splicing multple *.txt to Excel

Discussion in 'Business Applications' started by farmakologen, Apr 17, 2008.

Thread Status:
Not open for further replies.
Advertisement
  1. farmakologen

    farmakologen Thread Starter

    Joined:
    Apr 16, 2008
    Messages:
    10
    Hi,
    I'm a novice in VBA programing and cann't seem to find a solution to my problem anywhere. I've found similar codes to what I'm looking for, but I don't have the skills to change the code to fit my needs.

    My problem?
    I have loads of data (hundreds of files), in *.txt files, that I want to organize in Excel.
    The files are named by date and an item nummer e.g. 080130;13 (the 13th sample from 2008/01/30).
    I would like to import the data in excel and cut out specific data from the txt and discard the rest. In addition the next imported file should be placed next to the first. I think you get the idea.

    I'll attach samples of the txt and a worksheet with the layout I would like to have.
    The txt does not represent the whole file since it is over 1 MB.

    I found a code writen by Rollin_Again on the thread: http://forums.techguy.org/business-applications/697763-excel-importing-multiple-files.html
    It was great but I couldn't make it work for my situation.

    Anyway, can somebody help me?
     

    Attached Files:

  2. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,912
    I'll have a look when I get to work in a bit. I assume these text files are being produced or exported from another application? Will they always have the same layout with the same labels and types of data? Will the text files all be stored in a specific location from which they are processed and then deleted or moved?

    Regards,
    Rollin
     
  3. farmakologen

    farmakologen Thread Starter

    Joined:
    Apr 16, 2008
    Messages:
    10
    Great!
    Yeah! The text-files are exported from the machine application. They are keept as originals in the application data folder and copies that are to be analized are copied to a "text-file" directory.

    Thanks for having a look at it!
    Regards,
    Farmakologen
     
  4. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,912
    Can you ZIP and upload a couple more of the sample text files?

    Regards,
    Rollin
     
  5. farmakologen

    farmakologen Thread Starter

    Joined:
    Apr 16, 2008
    Messages:
    10
    Here are three more samples. They contain more info than the original file that I sent to you, since I discovered that I could zip them (novice move).

    Anyway, here they are.

    Do you think you could include (or teach me) how to modify the macro later to be able to choose what part of the data file that I want?

    Thanks!!
     

    Attached Files:

  6. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,912
    What information are you wanting to keep from the sample text files? In your sample workbook you are only keeping the Name / Time / and XX Distribution. The sample text files also include the following sections.

    ***** amount[pixel] vs. distance *****
    ***** q/m distribution *****
    ***** q/d data *****
    ***** q/d-data normalized*****
    etc.
    etc.
    etc.

    Regards,
    Rollin
     
  7. farmakologen

    farmakologen Thread Starter

    Joined:
    Apr 16, 2008
    Messages:
    10
    Yeah. You see, thats a part of the problem.
    Initially I just need the XX distribution with name and time to differentiate. But eventually I will need too look and compare the other tables as well. So it would be great if I could change the code the splice out the required parts for different trails. I mean, I can't come crying to you every time there's a little fuss. :)
     
  8. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    Hi, I'm Hans and took a look at you post.
    I also saw that some of the xtra sample txt files also contain a series of entries with about 7 or more groups of 00000 00000 00000

    I think it can be done, it'll take some brainwork.:eek:
    All the lines containing titles etc, are these identical?
    Is there a problem if a different section is shown in a different sheet and that you have one excel file for lets say each sample (file)?
    :confused:
     
  9. farmakologen

    farmakologen Thread Starter

    Joined:
    Apr 16, 2008
    Messages:
    10
    Hi Hans,

    Yes, the titles are identical to every other file.
    It isn't a problem to have the other sections in other sheets, that would acctually be o bonus. Having everything at the same time. But having excel file for each sample is against the the purpose of my question.
    The whole idea is to have the same info/section from different "sample" files in the same excel so that I can compare them.

    Regards,
    Farmakologen
     
  10. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    Hi just for you to loot at,
    All i did was 'drop' one of the txt files on Excel and got the attached Excel sheet.
    If you can point out which column is ok and what you want to have kept together I will look at some VBA code to heldp you with it.
     

    Attached Files:

  11. farmakologen

    farmakologen Thread Starter

    Joined:
    Apr 16, 2008
    Messages:
    10
    Hi,

    I'm not really sure what you mean, but if you are wondering how I would like the information from one file to be organized I've attached a sample file. Each table (i.e. amount[pixel] vs. distance, q/m distribution, q/d data, q/d-data normalized etc.) has been placed in a seperate worksheet. The name and "times" are labled over the tables. This file is similar to the original sample excel sheet that I attached, with the difference that the other table are there as well.

    But there's no use in doing a VBA code that just imports a text file and orders them like the file in the attachment. That I can do. The problem is to have a loop code that imports multiple files from a directory and orders them after each other with name, "time" and tables.

    Sorry if I'm being unnecesseraly overexplicit, but I might as well be, right? :)

    Regards
    /Farmakologen
     

    Attached Files:

  12. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    Beter over explicit than having the other one think of something he finds handy.
    I'll look at your sample and hope to at least give a tip.
     
  13. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    Just to check. Where does the next file data come:
    In the columns to the right? Does this mean that the sheet gets wider as you have moer files (mode columns)?
    I have a similar sheet which does something similra, reads a number of files from a directory and then starts doeing the job. I am seeing if I can rewrite the vba code to work for your files.
    It's really quite simple but you have to get it right the first time.
    If you want to append the data to the rows, does the header have to be included? I imagine so or else you don't have any idea where it comes from?
    Name 0800130:14 080130:15
    ???? ?????
    ???? ?????
    XX [YY/XX] XXXX XXXX XX[YY/XX] XXXXX XXXXX

    or below?
     
  14. farmakologen

    farmakologen Thread Starter

    Joined:
    Apr 16, 2008
    Messages:
    10
    Yes the next file data should be placed in the column right of the first data.
    And yes, the header/name should be included. Since there are four files for each date. So both name (e.g. 080130;14) and "time" (
    XXXXXXXX Time
    0.0, YYYYYYYYY Time 3.0
    ) need to be there to be able to differ between the files.
    All info is included in the file name, so if it is possible to import the text file name to the data, that would be enough.
     
  15. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    Hi there, I think I put something together for you.
    I'm sorry it'snot dicumented but it comes down to the following:
    I have attached a zip file containing a Samples.xls, a folder containg the vba code which is also allready in the excel sheet and a small file called input.lst which contains the names of the 3 txt files you sent.
    The code is programmed in such a manner that the data txt files should be in the same directory as the xcel sheet and you need to creata a lst file usign MSDOS containing only the nales of the txt files:
    The dos command would be (at the command prompt)
    dir *.txt /B > input.lst
    In the excel tab InputRead there are 3 buttons which trigger the necessary macro's, screen updating is False so you will not have a flikkering screen.
    Once the process ends the InputRead sheet will be active.
    The code is really quite simple. I'll try and put together a small explanatory file but see if this is the idea.
    I advise you answer yes to the promt to clear the input read list or else ik will keep appending the same files. Code would have to be written for this.
    The last sheets in about 33 columns wide so keep in mind the limit of columns in excel
    Good luck and let me know
     

    Attached Files:

  16. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/704549

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice