Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Perfmon output: Converting from binary to CSV file


Perfmon output: Converting from binary to CSV file

Author
Message
bj007
bj007
SSC-Enthusiastic
SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)

Group: General Forum Members
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





smstankus
smstankus
Valued Member
Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)

Group: General Forum Members
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.





smstankus
smstankus
Valued Member
Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)

Group: General Forum Members
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





bj007
bj007
SSC-Enthusiastic
SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)

Group: General Forum Members
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





smstankus
smstankus
Valued Member
Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)

Group: General Forum Members
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.






mbaker
mbaker
SSC Veteran
SSC Veteran (291 reputation)SSC Veteran (291 reputation)SSC Veteran (291 reputation)SSC Veteran (291 reputation)SSC Veteran (291 reputation)SSC Veteran (291 reputation)SSC Veteran (291 reputation)SSC Veteran (291 reputation)

Group: General Forum Members
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





bj007
bj007
SSC-Enthusiastic
SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)

Group: General Forum Members
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.





smstankus
smstankus
Valued Member
Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)

Group: General Forum Members
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'





bj007
bj007
SSC-Enthusiastic
SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)

Group: General Forum Members
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.





smstankus
smstankus
Valued Member
Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)

Group: General Forum Members
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.





Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search