News:

On Tuesday September 6th the forum will be down for maintenance from 9:30 PM to 11:59 PM PDT

Main Menu

[S]The CallHistory outload generates inconsistant results to the xml file...

Started by Robert.Thompson, February 24, 2012, 06:33:18 AM

Previous topic - Next topic

Robert.Thompson

Hello:

Here is an example of what I am saying:

1. Probably correct:
</CallHistory>
<CallHistory date="2/21/2012" time="16:08:54">
 <Terminal id="PHONE1" dir="Outbound">
   <Peer name="" number="#"/>
   <Event time="16:08:54">New Call</Event>
   <Event time="16:13:01">End Call</Event>
 </Terminal>
 <Terminal id="LINE1" dir="Outbound">
   <Peer name="" number=""/>
   <Event time="16:08:54">Call Connected</Event>
 </Terminal>

2. Incorrect:
</CallHistory>
<CallHistory date="2/21/2012" time="16:08:44">
 <Terminal id="PHONE1" dir="Outbound">
   <Peer name="" number="18666325775"/>
   <Event time="16:08:44">New Call</Event>
   <Event time="16:08:51">End Call</Event>
 </Terminal>
 <Terminal id="SP2" dir="Outbound">
   <Peer name="" number="18666325775"/>
  <Event time="nn:nn:nn">text here</Event>  this line is missing
 </Terminal>

3. Incorrect:
</CallHistory>
<CallHistory date="2/21/2012" time="14:14:06">
 <Terminal id="PHONE1" dir="Outbound">
   <Peer name="" number="**219055651038"/>
   <Event time="14:14:06">New Call</Event>
   <Event time="nn:nn:nn">text here</Event> this line is missing
 </Terminal>
 <Terminal id="SP2" dir="Outbound">
   <Peer name="" number="19055651038"/>
   <Event time="14:14:06">End Call (500 Server internal error)</Event>
 </Terminal>

Who and how do I contact the Obi people to request that they repair this bug?

Thanks,

Rob.
Rob. (Obi newbie.)

OBi 110 using Anveo - but presently testing AcroVoice
My blog: www.googlevoiceforcanadians.com

Robert.Thompson

Hello:

I have created a small pascal program to convert the callhistory.xml to a CSV file.

It works on my callhistory file but I do not have that many calls recorded.

It handles the two problems mentioned in my previous post.

How do I attach it to a post so that others might try it on a much bigger callhistory.xml?

Edit: Does anyone have a large callhistory.xml file that they would be willing to share with me for testing purposes?

Thanks,

Rob.
Rob. (Obi newbie.)

OBi 110 using Anveo - but presently testing AcroVoice
My blog: www.googlevoiceforcanadians.com

infin8loop

Hi Robert,

I wrestled with a vbscript and XSLT to convert the callhistory xml to csv.  I drove the XSLT off the "Event" tag and got 581 records for 200 calls in my history.  For successful calls ("Call Connected") there are consistently 3 events.  If a call isn't connected then it looks to me that the "Call Connected" event is simply omitted from the xml file (probably by design).  The 2 "incorrect" calls that you mentioned appear to be short enough that they may not have connected.

Excel is smarter than me.  When importing the callhistory xml directly into Excel I get 600 records, 3 for each call.  But this is only because a record for the "Terminal" tag appears for those that have no Event under them, the actual Event and event time are blank on those 19 records. 

Otherwise, the 581 csv records I get from my vbscript look identical to the Excel file created from the direct xml import.

I hope this makes some sense.

To post your code, click the "Additional Options" on the Post Reply form.  You'll see Attach:.
             
"This has not only been fun, it's been a major expense." - Gallagher

Robert.Thompson

Quote from: infin8loop on February 24, 2012, 04:04:02 PM
Hi Robert,

I wrestled with a vbscript and XSLT to convert the callhistory xml to csv.  I drove the XSLT off the "Event" tag and got 581 records for 200 calls in my history.  For successful calls ("Call Connected") there are consistently 3 events.  If a call isn't connected then it looks to me that the "Call Connected" event is simply omitted from the xml file (probably by design).  The 2 "incorrect" calls that you mentioned appear to be short enough that they may not have connected.

Excel is smarter than me.  When importing the callhistory xml directly into Excel I get 600 records, 3 for each call.  But this is only because a record for the "Terminal" tag appears for those that have no Event under them, the actual Event and event time are blank on those 19 records.  

Otherwise, the 581 csv records I get from my vbscript look identical to the Excel file created from the direct xml import.

I hope this makes some sense.

To post your code, click the "Additional Options" on the Post Reply form.  You'll see Attach:.
             

Hi infin8loop:

Thanks for your input.

My program was built with FreePascal using the Lazarus IDE on a Windows 7 64-bit PC.

How to use it:
1. Place the program, CallHistoryToCSV.exe, in the same directory that contains your callhistory.xml file.
2. Launch the CallHistoryToCSV program.
3. Open the callhistory.txt file with MS Excel or LibreOffice Calc
4. There will only be one row per call record.

What it will and won't do:
1. Does not alter the original callhistory.xml file.
2. Creates 2 new files:
callhistorytmp.txt
callhistory.txt  (this is the CSV file to use)
3. Won't work if callhistory.xml or callhistory.txt or callhistorytmp.txt are open in Excel, notepad etc.

The server won't allow me to attach it, too big, I guess.  :(

It is 2,093 kb.

Oh well, output sample attached.

Edit: I put the exe on RapidShare: https://rapidshare.com/files/165278480/CallHistoryToCSV.exe


Rob.
Rob. (Obi newbie.)

OBi 110 using Anveo - but presently testing AcroVoice
My blog: www.googlevoiceforcanadians.com

bbarker

This looks interesting.  Any chance of getting the source code?  Or how about a 32-bit EXE?

Robert.Thompson

Quote from: bbarker on March 03, 2012, 05:40:45 PM
This looks interesting.  Any chance of getting the source code?  Or how about a 32-bit EXE?

I am not a 'programmer' but here is the source code for the pascal unit that does the work:

unit Unit1;

{$mode objfpc}{$H+}

interface

uses
  Classes, SysUtils, FileUtil, Forms, Controls, Graphics, Dialogs, StdCtrls;

type

  { TForm1 }

  TForm1 = class(TForm)
    Button1: TButton;
    Edit1: TEdit;
    Edit2: TEdit;
    procedure Button1Click(Sender: TObject);
    procedure Edit2Change(Sender: TObject);
  private
    { private declarations }
  public
    { public declarations }
  end;

var
  Form1: TForm1;

implementation

{$R *.lfm}

var
  chFile, tmpfile ,csvFile : TextFile;
  text,newtext,outline, field9   : string;
   cnt, count, test : integer;


{ TForm1 }

procedure TForm1.Button1Click(Sender: TObject);
begin
  close;
end;

procedure TForm1.Edit2Change(Sender: TObject);
begin

end;


begin
  // open the callhistory.xml file for reading
  AssignFile(chFile, 'callhistory.xml');
  ReSet(chFile);
  // open or create the callhistorytmp.txt file for writing
  AssignFile(tmpFile, 'callhistorytmp.txt');
  ReWrite(tmpFile);

   // Initialize the variables that will be used
   newtext := '';
   outline := '';
   cnt := 0;

   // *************  Build the callhistorytmp.txt file ****************
      begin
          while not Eof(chFile) do
          begin
            ReadLn(chFile, text);
            cnt := cnt + 1;
            // skip the first 2 lines of the file
             if cnt < 3 then text := '';
            //replace any existing commas with spaces
             StringReplace(trim(text),',',' ', [rfReplaceAll, rfIgnoreCase]);
             newtext := text;
            // continue processing: remove literals & add commas as field separators
            newtext := StringReplace(trim(newtext),'<CallHistory date="','', [rfReplaceAll, rfIgnoreCase]);
            newtext := StringReplace(trim(newtext),'" time="' ,',', [rfReplaceAll, rfIgnoreCase]);
            newtext := StringReplace(trim(newtext),'">' ,',', [rfReplaceAll, rfIgnoreCase]);
            newtext := StringReplace(trim(newtext),'<Terminal id="' ,'', [rfReplaceAll, rfIgnoreCase]);
            newtext := StringReplace(trim(newtext),'" dir="' ,',', [rfReplaceAll, rfIgnoreCase]);
            newtext := StringReplace(trim(newtext),'<Peer name="' ,'', [rfReplaceAll, rfIgnoreCase]);
            newtext := StringReplace(trim(newtext),'" number="' ,',', [rfReplaceAll, rfIgnoreCase]);
            newtext := StringReplace(trim(newtext),'"/>' ,',', [rfReplaceAll, rfIgnoreCase]);
            newtext := StringReplace(trim(newtext),'<Event time="' ,'', [rfReplaceAll, rfIgnoreCase]);
            newtext := StringReplace(trim(newtext),'</Event>' ,',', [rfReplaceAll, rfIgnoreCase]);
            newtext := StringReplace(trim(newtext),'</Terminal>' ,'', [rfReplaceAll, rfIgnoreCase]);
            newtext := StringReplace(trim(newtext),'<?xml' ,'', [rfReplaceAll, rfIgnoreCase]);
            newtext := StringReplace(trim(newtext),'<CallHistoryFile>' ,'', [rfReplaceAll, rfIgnoreCase]);
            newtext := StringReplace(trim(newtext),'</CallHistoryFile>' ,'', [rfReplaceAll, rfIgnoreCase]);
            // Write the processed line to the callhistorytmp.txt file
            if newtext <> '' then writeln(tmpfile,newtext);
           end;
      CloseFile(chFile);
      CloseFile(tmpFile);
      end;


// *************  Build the callhistory.csv file ****************
Begin
  //Open the callhistory.csv file for writing
   AssignFile(csvFile, 'callhistory.txt');
   ReWrite(csvFile);
   //Open the callhistorytmp.txt file for reading
   AssignFile(tmpFile, 'callhistorytmp.txt');
   ReSet(tmpFile);
   text := '';
   outline := '';
   // Build column headings for callhistory.csv file (row # 1)
   outline := 'CallDate,'+'CallTime,'+'TerminalID,'+'Direction,'+'PeerName,'+
      'Number,'+'EventTime,'+'Event,'+'EventTime2,'+'Event2,'+'TerminalID2,'+
      'Direction2,'+'PeerName2,'+'Number2,'+'EventTime3,'+'Event3,'+'EventTime4,'+
      'Event4';
   // Write colum headings to callhistory.csv file
   writeln(csvfile,outline);
   // Initialize variables
   outline :='';
   count := 0;
   test := 0;
   field9:='';

  // Read through callhistorytmp.txt file
  while not Eof(tmpFile) do
  begin
    ReadLn(tmpFile, text);
    // build the record
    outline := outline + text;
    // Determine if processing the last line of a callhistorytmp record
    if text = '</CallHistory>' then
    begin
    // now, get rid of last line
    outline := StringReplace(trim(outline),'</CallHistory>' ,'', [rfReplaceAll, rfIgnoreCase]);
          begin
           for count := 1 to length(outline) do
              begin
                 //count the comma deliminaters to find the 8th comma
                 if copy(outline, count, 1) = ',' then test := test +1;
                 if test = 8 then
                 //test to see if some fields are missing
                    begin
                       // look at the data following the 8th comma
                       field9 := copy(outline,count+3,1);
                       //  ******** not sure about this test  *******
                       if (field9 <> ':') and  (field9 <> '') then insert(',,',outline,count);
                       test := 0;
                       break;
                    end;
              end;
          end;
       writeln(csvfile,outline);
       outline := '';
    end;
  end;
    closefile(csvfile);
    closefile(tmpfile);
end;
end.
                                       
Rob. (Obi newbie.)

OBi 110 using Anveo - but presently testing AcroVoice
My blog: www.googlevoiceforcanadians.com