Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Perfmon output: Converting from binary to CSV file Expand / Collapse
Author
Message
Posted Monday, May 17, 2004 2:57 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, March 3, 2010 6:02 PM
Points: 106, Visits: 32

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




Post #116304
Posted Monday, May 17, 2004 3:35 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, November 6, 2007 11:10 AM
Points: 52, Visits: 3

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.




Post #116311
Posted Monday, May 17, 2004 3:47 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, November 6, 2007 11:10 AM
Points: 52, Visits: 3

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




Post #116314
Posted Monday, May 17, 2004 4:04 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, March 3, 2010 6:02 PM
Points: 106, Visits: 32

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




Post #116317
Posted Tuesday, May 18, 2004 7:53 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, November 6, 2007 11:10 AM
Points: 52, Visits: 3

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.

 


 




Post #116412
Posted Tuesday, May 18, 2004 8:39 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, January 3, 2011 2:14 PM
Points: 291, Visits: 18

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




Post #116424
Posted Tuesday, May 18, 2004 11:41 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, March 3, 2010 6:02 PM
Points: 106, Visits: 32

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.

 




Post #116473
Posted Tuesday, May 18, 2004 12:29 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, November 6, 2007 11:10 AM
Points: 52, Visits: 3

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

BULK INSERT tempdb.dbo.TEMP_PERFMON_OUT

 FROM '<YOUR_FILE_NAME>.txt'




Post #116483
Posted Tuesday, May 18, 2004 12:42 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, March 3, 2010 6:02 PM
Points: 106, Visits: 32

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.




Post #116488
Posted Tuesday, May 18, 2004 1:00 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, November 6, 2007 11:10 AM
Points: 52, Visits: 3

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.




Post #116490
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse