Post reply

Perfmon output: Converting from binary to CSV file

  • May 17, 2004 at 2:57 pm

    #87158

    Hi All,

    While collecting perfmon statistics, I saved the file as a binary file my mistake.  How do I read the data now from it ?

    Is there a way, to convert this file to a CSV format ?  Any help grealy appreciated. 

    bill

  • May 17, 2004 at 3:35 pm

    #506713

    You could try to copy and paste your results into MS Excel, then convert it to decimal. 

    The formula is =BIN2DEC (binary result).  You may need to go to TOOLS, AddIns and select the Analysis ToolPak.  This will not work without this Add In.  When you save the Excel file, save it as a .csv.

    I don't know if this will work, but it's worh a shot.

  • May 17, 2004 at 3:47 pm

    #506716

    Another thought...have you tried to Bulk Insert your output ( presuming it was saved electronically) into a temp table, then do a select, converting the binary to char or varchar?  This would probably be faster than my initial suggestion.

    Scott

  • May 17, 2004 at 4:04 pm

    #506719

    Hi,

    Thanks.  Can you give me some more detail ? 

    I tried the first option, i added the add-in.  But, after pasting it to xcel, it is not working.  Or, I dont know how to proceed ?

    Can u explain a little more in detail on your 2nd suggestion ?  thanks

  • May 18, 2004 at 7:53 am

    #506787

    Sure.  With my first suggestion, you would need to modify each cell individually.  With my second thought, you could create a temp table, import the file's data into the temp table, then you should be able to query the table making the conversion during the select statement.

    EX.

    -- CREATE TABLE TO HOLD PERFMON DATA

    CREATE TABLE tempdb.dbo.TEMP_PERFMON_OUT

    ( DATA     BINARY (8000) )

     

    -- IMPORT THE DATA

    BULK INSERT tempdb.dbo.TEMP_PERFMON_OUT

     FROM '<YOUR_FILE_NAME>.txt'

    WITH ( FIELDTERMINATOR = ',' ) -- THIS ROW STATES YOUR FILE IS COMMA DELIMITED. 

                                   -- MAKE THIS HOW YOUR DATA IS PARSED. (COMMA, TAB, ETC. )

    -- GET YOUR DATA, CONVERTING THE BINARY TO VARCHAR

    SELECT CAST ( DATA AS VARCHAR ) FROM tempdb.dbo.TEMP_PERFMON_OUT

     

    Hope this helps.  It's only a rough template and a rough idea.  I haven't had time to tinker with test data on my end.

     

     

  • May 18, 2004 at 8:39 am

    #506797

    FWIW, I would suggest using Perfmon to convert the saved binary file to a comma seperated file.

    1. Open the binary file (.blg) in Perfmon.
    2. Add all counters of all of the performance objects to the display.
    3. "Right-click the System Monitor details pane and click Save Data As.", selecting .csv.

    This will ensure that the proper scaling is applied to all of the data.  For more info, see Perfmon help file, "saving performance data to a log file."

    Cheers,

    Mike

  • May 18, 2004 at 11:41 am

    #506834

    sstankus1, I cannot do a bulk insert, as i dont know what is the field terminator & row terminator.  it gives error.  It is a binary file.  So, what should i set?

    mbaker,  your idea seems interesting.  But, i dont know how to do it.  i started perfmon.  When i right-click on SystemMonitor/Open nothing happens.  Then, I went to the right pane. Here I clicked the 4th button, 'View Log File Data', and selected my log file to open.  After that nothing happens.  Is there something i am missing..

    Your help is greatly appreciated.  thanks.

     

  • May 18, 2004 at 12:29 pm

    #506842

    Have you tried the bulk insert without the "with"? 

    BULK INSERT tempdb.dbo.TEMP_PERFMON_OUT

     FROM '<YOUR_FILE_NAME>.txt'

  • May 18, 2004 at 12:42 pm

    #506847

    yes.

    bulk insert t1 from 'c:\filename.blg'  -- gives me the following error.

    Server: Msg 4864, Level 16, State 1, Line 1

    Bulk insert data conversion error (type mismatch) for row 1, column 1 (data).

    Server: Msg 4866, Level 17, State 66, Line 1

    Bulk Insert fails. Column is too long in the data file for row 2, column 1. Make sure the field terminator and row terminator are specified correctly.

    Server: Msg 7399, Level 16, State 1, Line 1

    OLE DB provider 'STREAM' reported an error. The provider did not give any information about the error.

    OLE DB error trace [OLE/DB Provider 'STREAM' IRowset::GetNextRows returned 0x80004005:  The provider did not give any information about the error.].

    The statement has been terminated.

  • May 18, 2004 at 1:00 pm

    #506849

    Hmmm...gotta think about this one for a while. 

    1.  Can you open the file in notepad?  If you can, I presume it will be in binary.  Can you read binary?  (Just a little humor to lighten the mood... and yes, I know if you could we wouldn't be having this conversation )  What I'm after is a way to determine what the field terminator or row terminator would be.  If you can open the file in notepad, then copy and paste it into MS Word, you can set word to show the  characters.  This may help us determine the fieldterminator or rowterminator.

  • May 18, 2004 at 1:06 pm

    #506850

    Hi bj007,

    "Is there something i am missing.."

    After you click on the fourth button, "View Log File Data" or CTRL-L, you should all all of the instances of all of the counter, of all of the objects. To do this.

      * Click on the eight icon from the left (the plus sign, "Add", or CRTL-I)

         * Click on the "All counters" radio button

         * Click on the "All instances" radio button

         * Click on the "Add" button

      * Repeat this for each Performance object in the drop down list

    "Right-click the System Monitor details pane and click Save Data As.", selecting .csv.

    Cheers,

    Mike

  • May 18, 2004 at 2:03 pm

    #506855

    I can see the file, but it is hard to find the pattern, between row seperator and column seperator.

    By the way, I was able to load the log file back into System Monitor.  I can save this as a html file (the graphy),  and also as a tsv file.  But, the tsv file captures only only value per counter.

    It does not store all the values of every counter for the entire duration I captured, into the tsv file.  I am still exploring the possibilities.

    Thanks for your help.

  • May 18, 2004 at 2:46 pm

    #506860

    I would suggest checking out Relog from Microsoft. Relog takes an existing Perfmon log and can output it to binary, csv, tsv or a SQL table.

  • October 7, 2004 at 12:43 pm

    #525830

    Just to clarify mbaker's statement, you need to copy you blg file to a Server 2003 machine. There is now a new option called 'save data as' that allows you to convert to csv.

  • April 25, 2012 at 4:42 am

    #1478843

    mbaker (5/18/2004)

    FWIW, I would suggest using Perfmon to convert the saved binary file to a comma seperated file.<OL><LI>Open the binary file (.blg) in Perfmon.</LI><LI>Add all counters of all of theperformance objects to the display.</LI><LI>"Right-click the System Monitor details pane and click <B>Save Data As</B>.", selecting .csv.</LI></OL>

    This will ensure that the proper scaling is applied to all of the data. For more info, see Perfmon help file, "saving performance data to a log file."

    Cheers,

    Mike</NOLOC>

    Thank you Mike, you just save me a huge amount of time :).

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply