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
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.
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
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
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.
FWIW, I would suggest using Perfmon to convert the saved binary file to a comma seperated file.
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
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.
Have you tried the bulk insert without the "with"?
FROM '<YOUR_FILE_NAME>.txt'
yes.
bulk insert t1 from 'c:\filename.blg' -- gives me the following error.
Server: Msg 4864, Level 16, State 1, Line 1Bulk insert data conversion error (type mismatch) for row 1, column 1 (data).Server: Msg 4866, Level 17, State 66, Line 1Bulk 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 1OLE 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.
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.
