Results 1 to 6 of 6

Thread: *.txt to excel

  1. #1
    Join Date
    Feb 2009
    Posts
    3

    Unanswered: *.txt to excel

    I would like to import a tremendous amount of data from about 300 text files to an exel spreadsheet. I also do not need all of the data in each text file.

    Would someone be able to guide me in doing this, I need to get this done ASAP.
    thanks in advance.

    G

  2. #2
    Join Date
    Feb 2009
    Posts
    4

    Solution

    Steps brought from the Excel Help:

    1- Click the cell where you want to put the data from the text file.
    To ensure that the external data does not replace existing data, make sure that the worksheet has no data below or to the right of the cell you click.

    2- On the Data menu, point to Import External Data, and then click Import Data.
    3- In the Files of type box, click Text Files.
    4- In the Look in list, locate and double-click the text file you want to import as an external data range .
    5- To specify how you want to divide the text into columns, follow the instructions in the Text Import Wizard, and then click Finish.
    6- In the Import Data dialog box, click Properties to set formatting and layout options for the imported data.
    7- In the Import Data dialog box, do one of the following:
    - To return the data to the location you selected, click Existing worksheet, and then click OK.
    - To return the data to a new worksheet, click New worksheet, and then click OK. Microsoft Excel adds a new worksheet to your workbook and automatically puts the external data range in the upper-left corner of the new worksheet.

    I hope this is the requested help...

    Thanks,

  3. #3
    Join Date
    Feb 2009
    Posts
    3

    *.txt to excel

    ok. I guess my question was too broad and not clear enough. This is what the txt file looks like (see below), also note that there was some identifying info on top that i deleted.

    For example for the line: Outsid HI 2:47a49 17:49a48 18:39a44 20:10a38 4:40a33 4:49a60 7:19a43

    I need it to look like this :
    Outsid HI 49 48 44 38 33 60 43


    LONG LOG SUMMARY REPORT FOR PERIOD 1/7/2008 TO 1/1/2008
    Event 01/07/08 01/06/08 01/05/08 01/04/08 01/03/08 01/02/08 01/01/08
    Inside HI 0:00a72 20:53a74 17:30a73 13:28a71 7:12a72 6:29a73 13:46a74
    Inside LO 7:57a66 5:03a68 7:27a69 10:33a64 6:06a64 19:30a60 0:00a66
    Outsid HI 2:47a49 17:49a48 18:39a44 20:10a38 4:40a33 4:49a60 7:19a43
    Outsid LO 7:11a36 0:19a36 6:52a31 0:00a18 10:55a12 20:40a16 22:55a30
    H/W HI 6:49a133 9:32a135 9:28a134 9:42a136 10:31a133 8:49a136 14:19a134
    H/W LOW 8:34a112 7:02a111 7:49a110 19:37a110 16:56a110 15:41a113 9:57a111
    COIL HI 5:57a203 13:29a203 16:23a204 6:30a203 5:45a204 12:55a202 11:16a204
    COIL LOW 0:25a149 5:35a151 2:15a165 15:12a137 7:51a152 0:26a152 23:22a146
    AQUA HI 5:52a224 16:02a225 16:25a226 0:48a224 5:33a225 16:33a224 11:16a225
    AQUA LOW 0:26a161 5:35a164 2:14a180 15:13a146 7:51a165 0:25a165 23:21a158
    Heat BYP 00:00:00 00:00:00 00:00:00 00:00:00 00:00:00 00:00:00 00:00:00
    H/W BYP 00:00:00 00:00:00 00:00:00 00:00:00 00:00:00 00:00:00 00:00:00
    Heat TMR 03:12:09 09:24:44 11:48:03 11:07:47 14:40:16 11:20:17 07:54:41
    Comp Heat 03:17:40 08:32:03 10:11:53 11:18:47 14:43:18 10:32:13 08:03:38
    <Font color=red>Comp H/W 12:54:35 22:30:20 23:38:58 23:59:59 23:57:27 23:59:59 23:24:49</font>
    Boiler RT 03:22:32 08:37:28 10:12:10 11:18:51 14:43:22 10:37:01 08:03:39
    Water use 0.0 Ga 0.0 Ga 0.0 Ga 0.0 Ga 0.0 Ga 0.0 Ga 0.0 Ga
    Boil fail 00:00:00 00:00:00 00:00:00 00:00:00 00:00:00 00:24:24 00:00:00
    Fuel use 135 uts 344 uts 408 uts 452 uts 588 uts 424 uts 322 uts
    <REPGOESHERE>
    </Pre></tt>
    <br>
    <hr ALIGN=LEFT NOSHADE WIDTH="600">
    </html>


    thanks again.

  4. #4
    Join Date
    Feb 2009
    Posts
    4

    Solution

    At step 2 of the wizard under "delimiters" use "space" and "other" using character "a" that will make it each one in a cell... (as shown in the jpg attached)
    and if you want some of those columns not to be imported on the 3rd step of the wizard you can highlight a specific column and choose "Do not import column (skip)" then click Finish..

    I hope this will help...

    Thanks,
    Attached Thumbnails Attached Thumbnails import step 2.JPG  

  5. #5
    Join Date
    Feb 2009
    Posts
    3

    *.txt to excel

    thank you so much

  6. #6
    Join Date
    Feb 2009
    Posts
    4

    Wink

    You are welcome anytime...

Posting Permissions

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