Advertisement
If you have a new account but are having problems posting or verifying your account, please email us on hello@boards.ie for help. Thanks :)
Hello all! Please ensure that you are posting a new thread or question in the appropriate forum. The Feedback forum is overwhelmed with questions that are having to be moved elsewhere. If you need help to verify your account contact hello@boards.ie

Trying to convert from notepad to excel...very confused

  • 29-06-2020 9:49pm
    #1
    maniac2003
    Registered Users Posts: 352 ✭✭


    Hopefully someone here can help. I'm very confused at this stage.

    Trying to get the notepad attached into some form of presentable format for trending in excel, cannot figure out a way after many hours of trying. Am very frustrated at this stage any ideas welcomed.
    Tagged:


Comments

  • #2
    Vestiapx
    Registered Users, Registered Users 2 Posts: 1,009 ✭✭✭Vestiapx


    maniac2003 wrote: »
    Hopefully someone here can help. I'm very confused at this stage.

    Trying to get the notepad attached into some form of presentable format for trending in excel, cannot figure out a way after many hours of trying. Am very frustrated at this stage any ideas welcomed.

    Can't open on my phone but if you bump the thread in the morning I'll do it for you if I can.


  • #3
    Dravokivich
    Moderators, Category Moderators, Music Moderators, Politics Moderators, Society & Culture Moderators Posts: 22,360 CMod ✭✭✭✭Dravokivich


    There doesn't seem to be any consistent formatting in there. Usually when using the import text feature, it's trying to identify a delimiter, whether it's, " " or "," or ";" or anything else to figure out how the columns are to be validated. Your text file appears to be done by line breaks? And with odd times showing "
    ".

    I think the data source might need to be looked at. Do you have other export options, or are you stuck with this file?


  • #4
    ED E
    Registered Users, Registered Users 2 Posts: 36,169 ✭✭✭✭ED E


    Raw 
    [17/06/20 - 21:40:05:313] 
 3  
[17/06/20 - 21:40:05:314] 
Guide D[mm]: 
[17/06/20 - 21:40:05:318] 
 1.5137  
[17/06/20 - 21:40:05:320] 
position D[mm]: 
[17/06/20 - 21:40:05:323] 
 7.00  
[17/06/20 - 21:40:05:324] 
F[N]: 
[17/06/20 - 21:40:05:324] 
 0.0000  
[17/06/20 - 21:40:05:325] 
P[bar]: 
[17/06/20 - 21:40:05:325] 
 1.00  
[17/06/20 - 21:40:05:326] 
T[C*]: 
[17/06/20 - 21:40:05:326] 
 599 
[17/06/20 - 21:40:09:704] ------------ 
[17/06/20 - 21:40:09:709] 
R: 
[17/06/20 - 21:40:09:711] 
 8  
[17/06/20 - 21:40:09:712] 
S: 
[17/06/20 - 21:40:09:712] 
 4  
[17/06/20 - 21:40:09:712] 
Guide D[mm]: 
[17/06/20 - 21:40:09:713] 
 0.5000  
[17/06/20 - 21:40:09:714] 
position D[mm]: 
[17/06/20 - 21:40:09:716] 
 5.0265  
[17/06/20 - 21:40:09:717] 
F[N]: 
[17/06/20 - 21:40:09:717] 
 0.0000  
[17/06/20 - 21:40:09:718] 
P[bar]: 
[17/06/20 - 21:40:09:718] 
 0.9890  
[17/06/20 - 21:40:09:719] 
T[C*]: 
[17/06/20 - 21:40:09:719] 
 599 
[17/06/20 - 21:40:11:501] ------------ 
[17/06/20 - 21:40:11:502] 
R: 
[17/06/20 - 21:40:11:503] 
 8

    This is a pretty garbage way of getting data, no way you can get json or something else?

    You can macro it in NPP but its too awkward for me to bother.


  • #5
    ED E
    Registered Users, Registered Users 2 Posts: 36,169 ✭✭✭✭ED E


    Dravokivich wrote: »
    There doesn't seem to be any consistent formatting in there.

    Its repeating sets of 29 rows, so you can automate cleanup but eughhhh.


  • #6
    leinster93
    Registered Users, Registered Users 2 Posts: 311 ✭✭leinster93


    Use Find Replace and replace the line break with a comma. Change the .txt file extension to csv and open in excel...

    I just see, there is no delimiter?


  • Advertisement
  • #7
    ED E
    Registered Users, Registered Users 2 Posts: 36,169 ✭✭✭✭ED E


    leinster93 wrote: »
    Use Find Replace and replace the line break with a comma. Change the .txt file extension to csv and open in excel...

    Thats not quite it. You'd have to isolate to one timestamp with values.


  • #8
    1percent
    Registered Users Posts: 123 ✭✭1percent


    My two cent would be paste it into word
    Replace all " " (space) with "" (nothing)
    Copy all, past into excel in 3 adjacent cols as it looks like you have 3 fields for measure - time stamp - reading.
    Select cells A1:2 - insert, shift cells down
    Select cells B1 - insert, shift cells down
    Select row 1 - clear content
    Select row 2 - delete rows
    Delete the last two rows of data too where the data in colour A and B were pushed down.
    This will give you measure time stamp and reading, stick in your headings format you time stamp to date format and insert a pivot table
    Rows = time stamp
    Col = measure
    Values = reading


  • #9
    JoyPad
    Registered Users, Registered Users 2 Posts: 1,704 ✭✭✭JoyPad


    Is this a log of some sorts? And does the first set of records have some missing fields?

    Assuming that the dashes are the record separator, and ignoring the millis in the timestamps, I assume the rows would look like this:

    Time|R|S|Guide D (mm)|position D (mm)|F (N)|P (bar)|T (C*)
    17/06/20 - 21:40:09|8|4|0.5000|5.0265|0.0000|0.9890|599
    17/06/20 - 21:40:11|8|5|0.5000|2.5287|0.0000|0.9995|600
    ...


    See file attached. I dropped the incomplete records at the top and end, but still have 80 good ones. Rename to CSV (boards didn't allow me to attach it with that extension).


Advertisement