Questions

Convert log output for .csv in Excel

+
0 Votes
Locked

Convert log output for .csv in Excel

daveevans28
I need to take a log output (Active Killdisk) and import it into Excel. Has anyone done this?Do I have to go thru the log and manually put in the separators?
  • +
    0 Votes
    neilb@uk

    You can use anything as separators, including spaces.

    +
    0 Votes
    daveevans28

    its saved by the app as .log but it opens with notepad. i did change the separators to colons, as the "headings" are separated that way, but then i wound up with the elapsed time of wipe in 2 different columns. The log info is diplayed vertically, a separate entry for each wiiped drive, but I would like it displayed on the sheet on horizontal columns of course.

    +
    0 Votes
    Tony Hopkinson

    Change the headings to be separated by commas, is minor one.
    The big issue is entry per drive vertically, thing was never designed to be imported as csv, I'd chcek the app to see if it has an other log file format, by the time you've edited it to make it fit in a worksheet you could have cut and pasted it in cell by cell.

    +
    0 Votes
    daveevans28

    Thanks Tony. I was afraid of that, and pasting in the info line by line may be the only solution. We have this deployed in our other facilities, I can see how they did it. Corp took the easy money way out on this one. Wew tried a different version that was much easier to use.

    +
    0 Votes
    BillMlod

    I have a different type of log file but what I do is rename the file to a .csv (or copy it to be safe) and then open the .csv in Excel, it should then ask for the seperator to use. Might save some cutting & pasting.

    +
    0 Votes
    Tony Hopkinson

    Do you know what information you want to analyse.

    Using VBA or even Find and cherry picking from the txt file into a csv might make it do able.

    ie if you wanted drive size
    and each drive entry was

    Drive XXXX
    ...
    ...
    Size 500 GB

    Rip through the text file.
    Other possibilities would be the ODBC text driver or Access.

    +
    0 Votes
    seanferd

    just use a text editor for which you can create a find & replace scheme to comma-separate the values.

    If you need this "horizontally", you need to do further macro creation to automate this. Is it worth it? Is this something you do all the time? Then invest in creating the macro. Notepad++ might be a good choice. For Mac, I'd look at TextWrangler. For Unix(-like) OS, emacs, vim, etc., may do what you need.

    +
    0 Votes
    daveevans28

    I'm playing around with excel right now - if I 'copy' and 'paste special' with 'transpose' I can get it to paste horizonatlly into cells. However we wipe 2 dozen or so drives a day.

    I like the idea of macros, which I havent worked with yet, and will look into the other methods suggested by Tony. I alsoo need to make it easy for my co-workers or my successor if I decide to leave :)

    If saved as a .txt when opened it does ask for the delimiter anyway, which is a colon in this case (which screws up the time entry).

    I need to keep as much of the info as possible for auditing purposes.

    Thanks everyone - onward I go. PS I work as a bored to tears computer tech for an e-recycling company in the states.

    +
    0 Votes
    seanferd

    is that all you have to do in the future is open the file, then run the macro. Once the text is re-arranged properly, then you can paste it into Excel in one shot.

    Notepad++ is one that will record the macro for you. (I.e., you don't have to write in some macro language.)

    edit: And if you are doing that many drive which you need to keep track of, you may want to look into using a database (even Access :-& ) which might be a lot better than spreadsheets in the long run.

    +
    0 Votes
    daveevans28

    Funny I just finished reading about Macros. That seems pretty straight forward and will work for me for now. I will look for notepad ++. I use Access often at a part-time job but never set it up (sucessfully), so I will have to learn more about it. I'm not a fan of the search function in Access, but anything that works is fine with me.

  • +
    0 Votes
    neilb@uk

    You can use anything as separators, including spaces.

    +
    0 Votes
    daveevans28

    its saved by the app as .log but it opens with notepad. i did change the separators to colons, as the "headings" are separated that way, but then i wound up with the elapsed time of wipe in 2 different columns. The log info is diplayed vertically, a separate entry for each wiiped drive, but I would like it displayed on the sheet on horizontal columns of course.

    +
    0 Votes
    Tony Hopkinson

    Change the headings to be separated by commas, is minor one.
    The big issue is entry per drive vertically, thing was never designed to be imported as csv, I'd chcek the app to see if it has an other log file format, by the time you've edited it to make it fit in a worksheet you could have cut and pasted it in cell by cell.

    +
    0 Votes
    daveevans28

    Thanks Tony. I was afraid of that, and pasting in the info line by line may be the only solution. We have this deployed in our other facilities, I can see how they did it. Corp took the easy money way out on this one. Wew tried a different version that was much easier to use.

    +
    0 Votes
    BillMlod

    I have a different type of log file but what I do is rename the file to a .csv (or copy it to be safe) and then open the .csv in Excel, it should then ask for the seperator to use. Might save some cutting & pasting.

    +
    0 Votes
    Tony Hopkinson

    Do you know what information you want to analyse.

    Using VBA or even Find and cherry picking from the txt file into a csv might make it do able.

    ie if you wanted drive size
    and each drive entry was

    Drive XXXX
    ...
    ...
    Size 500 GB

    Rip through the text file.
    Other possibilities would be the ODBC text driver or Access.

    +
    0 Votes
    seanferd

    just use a text editor for which you can create a find & replace scheme to comma-separate the values.

    If you need this "horizontally", you need to do further macro creation to automate this. Is it worth it? Is this something you do all the time? Then invest in creating the macro. Notepad++ might be a good choice. For Mac, I'd look at TextWrangler. For Unix(-like) OS, emacs, vim, etc., may do what you need.

    +
    0 Votes
    daveevans28

    I'm playing around with excel right now - if I 'copy' and 'paste special' with 'transpose' I can get it to paste horizonatlly into cells. However we wipe 2 dozen or so drives a day.

    I like the idea of macros, which I havent worked with yet, and will look into the other methods suggested by Tony. I alsoo need to make it easy for my co-workers or my successor if I decide to leave :)

    If saved as a .txt when opened it does ask for the delimiter anyway, which is a colon in this case (which screws up the time entry).

    I need to keep as much of the info as possible for auditing purposes.

    Thanks everyone - onward I go. PS I work as a bored to tears computer tech for an e-recycling company in the states.

    +
    0 Votes
    seanferd

    is that all you have to do in the future is open the file, then run the macro. Once the text is re-arranged properly, then you can paste it into Excel in one shot.

    Notepad++ is one that will record the macro for you. (I.e., you don't have to write in some macro language.)

    edit: And if you are doing that many drive which you need to keep track of, you may want to look into using a database (even Access :-& ) which might be a lot better than spreadsheets in the long run.

    +
    0 Votes
    daveevans28

    Funny I just finished reading about Macros. That seems pretty straight forward and will work for me for now. I will look for notepad ++. I use Access often at a part-time job but never set it up (sucessfully), so I will have to learn more about it. I'm not a fan of the search function in Access, but anything that works is fine with me.