Solved

Reading formatted text and export to Excel...

Posted on 2004-04-18
13
1,639 Views
Last Modified: 2010-04-05
Hi All... recently i was assign a task to help automate the conversion of a formatted text file to an excel file, i am still new to delphi, and i need help urgently, if it is a normal csv file, it's would be ok, but the source text file is too formatted i
have no idea how to go about it. I need to know if it is possible, if yes, how? or please point me to the right direction. Thanks...


[sample source text content]
------------------------------------------------------------------
| Table                    PRODUCT                                     |
| Short Description    EDI810 audit log                           |
| Number of Entries   2                                                |
------------------------------------------------------------------

-------------------------------------------------------------------
 DATUM      | SNDPRN     | NAME1   |                 VBELN   |
-------------------------------------------------------------------
|
MTEXT
|
--------------------------------------------------------------------
| MX00001     |
--------------------------------------------------------------------
02/24/2004 | 1000266369 | Comp Name |  XX42831537
--------------------------------------------------------------------
invoice processed successfully thru pre-processor.
|
---------------------------------------------------------------------
| MX00002   |
---------------------------------------------------------------------
03/24/2004 | 1000266369 | Comp Name |  XX73677837
---------------------------------------------------------------------
Line Item 0101 does not exist on PO # M7K806A.
|
---------------------------------------------------------------------

MX00001 and MX00002 are the line item number with date,
serial number, company name, product code and the status.

the out put i require has only 4 columns, the line item number,
date, product code and status all in excel file. The program
that we use to produce the above out put is a dos program and cannot do what was required.

Thanks again...
Regards
Ben
0
Comment
Question by:warchief07
  • 4
  • 3
  • 3
  • +1
13 Comments
 
LVL 17

Expert Comment

by:mokule
ID: 10857216
Maybe You should parse the text with regular expressions.

Freeware RegExpr for Delphi:
http://regexpstudio.com/TRegExpr/TRegExpr.html
0
 
LVL 17

Expert Comment

by:mokule
ID: 10857483

If You would like to follow my suggestion here is code for extracting Your data.

procedure Extract(const AText : string);
var
  item,date,code,status: string;
begin
  with TRegExpr.Create do try
     Expression := '(\|\ *(MX\d+)\ *\|\r\n\-+\r\n)([^\|]*)(\|)([^\|]*)(\|)([^\|]*)(\|)([^\-|]*)(\r\n\-+\r\n)([^\|]*)';
     if Exec (AText) then
      REPEAT
       item :=  Trim(Match[2]);
       date :=  Trim(Match[3]);
       code :=  Trim(Match[9]);
       status :=  Trim(Match[11]);

// export to excel here

      UNTIL not ExecNext;
    finally Free;
   end;
end;

0
 
LVL 11

Expert Comment

by:calinutz
ID: 10857687
To export your data to Excel you can use this

//To start using Excel
Drop an ExcelApplication component on your form. If the AutoConnect property is true, Excel will start automatically when your program starts; if it's false, just call
  ExcelApplication1.Connect;
when you want to start Excel. To use a running instance of Excel, if there is one, set the ConnectKind property of TExcelApplication to ckRunningOrNew, or to ckRunningInstance if you don't want to start a new instance if Excel isn't running.
 Once Excel has started, you can connect other components, such as TExcelWorkbook, using their ConnectTo method:
  ExcelWorksheet2.ConnectTo(ExcelApplication1.Worksheets.Item['Sheet2'] as _Worksheet);


//To close Excel
  { Uncomment the next line if you want Excel to quit without asking
    whether to save the worksheet }
  // Excel.DisplayAlerts[LCID] := False;
  Excel.Quit;

//To add a workBook
 var
    WBk: _Workbook;
  ...
    WBk := Excel.Workbooks.Add;

//To open a workBook
  var
    WBk, WS, SheetName: OleVariant;
  ...
    WBk := Excel.WorkBooks.Open('C:\Test.xls');
    WS := WBk.Worksheets.Item['SheetName'];
    WS.Activate;

//To close a workBook
  var
    SaveChanges: OleVariant;
  ...
  WBk.Close(SaveChanges := True);

//To enter data

Using a _Worksheet variable, WorkS:

  WorkS := Excel.ActiveSheet as _Worksheet;

  WorkS.Range['A1', 'A1'].Value := 'The meaning of life, the universe, and everything, is';
  WorkS.Range['B1', 'B1'].Value := 42;
You can enter data into many cells at once:

  WorkS.Range['C3', J42'].Formula := '=RAND()';
You can access cells with row and column numbers or variables, like this:

  var
    Row, Col: integer;
  ...
    WorkS.Cells.Item[1, 1].Value := 'The very first cell';
    WorkS.Cells.Item[Row, Col].Value := 'Some other cell';



I suppose you can parse through your text file and get the data...

Cheers
0
 
LVL 22

Expert Comment

by:Ferruccio Accalai
ID: 10857720
My version....

Uses ComObj;

procedure TForm1.Button1Click(Sender: TObject);
var
List: TStrings;
Excel: Variant;
row,column,i,x,y: Integer;
s: String;
const
  xlCenter = 4294963188;
begin
List := TstringList.Create;
  try
  List.LoadFromFile('c:\Test.csv');
  try
    excel:=CreateOleObject('Excel.Application');
  except
    Raise Exception.Create('Excel not available');
    exit;
  end;
  excel.Workbooks.Add;
  Excel.cells[1,1].value := 'Line Item Number';
  Excel.cells[1,1].ColumnWidth := 15;
  Excel.cells[1,2].ColumnWidth := 10;
  Excel.cells[1,3].ColumnWidth := 15;
  Excel.cells[1,4].ColumnWidth := 20;
  Excel.cells[1,2].value := 'Date';
  Excel.cells[1,3].value := 'Product Code';
  Excel.cells[1,4].value := 'Status';
  for i := 1 to 3 do
  Excel.Columns[i].HorizontalAlignment := xlcenter;
  row := 2;
  for i := 0 to List.Count-1 do
    begin

      if pos('MX',List[i]) > 0 then
        begin
          Y := 1;
          s := '';
          x := pos('MX',List[i]);
          while List[i][x] <> ' ' do begin
            s := s+list[i][x];
            inc(x);
          end;
        Excel.cells[row,1].value := Trim(s);
        x := 1;
        List[i+2] := List[i+2]+'|';
        for Column := 2 to 5 do begin
        s := '';
        while List[i+2][x] <> '|' do
          begin
            if (Y = 1) or (Y = 4) then
              s := s+List[i+2][x];
            inc(x);
          end;
        Inc(Y);
        If s <> '' then begin
            If column > 2 then
              excel.cells[row,column-2].value :=trim(s)
            else
              excel.cells[row,column].value :=trim(s);
            end;
            inc(x);
        end;
        excel.cells[row,4].value := list[i+4];
        Inc(row);
        end else continue;
    end;
    excel.visible := true;
    excel := Unassigned;
    finally
    List.free;
    end;
end;
0
 
LVL 17

Expert Comment

by:mokule
ID: 10857809

Ferruccio: I didn't read Your code too carefully. But have question.
Is it behaves good when found MX somewhere else in text?
0
 
LVL 22

Expert Comment

by:Ferruccio Accalai
ID: 10857839
I don't know about the whole output of his file....but based on the above posted by him it should work...

Of course there could be some error if something contains that MX other then the Line item number....
In this case it could be parsed as

[...]
for i := 0 to List.Count-1 do
    begin
      if Uppercase(Copy(list[i],1,4)) = '| MX' then
        begin
          Y := 1;
[...]
0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 11

Expert Comment

by:calinutz
ID: 10857986
Try this and tell me if it works for you:
Place a button and a stringGrid on your form and
place then this procedure in the code:

and then on your onbuttonClick event place the code below

procedure separateSTR(s:string;j:integer);
var
 i,x:integer;
 p:string;
begin
x:=STRLen(PCHAR(s));
i:=1;
p:='';
repeat
p:=Concat(p,s[i]);
i:=i+1;
until (i=x)or(i=Pos('|', S));
Form1.StringGrid1.Cells[2,j]:=p;
//was date
//now product code
i:=i+1;
p:='';
repeat
If s[i]<>'|' then p:=Concat(p,s[i]);
i:=i+1;
until (i=x)or(S[i]='|');
Form1.StringGrid1.Cells[3,j]:=p;
end;

procedure TForm1.Button1Click(Sender: TObject);
var
   f:TextFile;
   p,s:String;
   j:integer;
begin
   AssignFile(f,'C:\test.txt');
   Reset(f);
j:=2;
repeat
 repeat
  repeat
If not SeekEof(f) then    Readln(f,s);
  until (StrLen(PCHAR(s))>=2) or (SeekEof(f));
 until ((s[1]='|') and (s[3]='M') and (s[4]='X')) or (SeekEof(f));
If  not SeekEOF(f) then
begin
p:=s[3]+s[4]+s[5]+s[6]+s[7]+s[8]+s[9];
StringGrid1.Cells[1,j]:=p;
If not SeekEof(f) then Readln(f,s); // doted line
If not SeekEof(f) then Readln(f,s);//data line
separateSTR(s,j);
If not SeekEof(f) then Readln(f,s);
If not SeekEof(f) then Readln(f,s);
Form1.StringGrid1.Cells[4,j]:=Trim(s);
j:=j+1;
end;
until SeekEof(f);
CloseFile(f);


end;




I tested it with a test file composed from your question and it seem to work.
Cheers
0
 
LVL 11

Expert Comment

by:calinutz
ID: 10857989
Yes and it behaves well when encountering MX somewhere else in the text
0
 
LVL 11

Accepted Solution

by:
calinutz earned 500 total points
ID: 10858076
Two buttons and a StringGrid on a form and here is the whole code:


procedure separateSTR(s:string;j:integer);
var
 i,x:integer;
 p:string;
begin
x:=STRLen(PCHAR(s));
i:=1;
p:='';
repeat
p:=Concat(p,s[i]);
i:=i+1;
until (i=x)or(i=Pos('|', S));
Form1.StringGrid1.Cells[2,j]:=p;
//was date
//now product code
i:=i+1;
p:='';
repeat
If s[i]<>'|' then p:=Concat(p,s[i]);
i:=i+1;
until (i=x)or(S[i]='|');
Form1.StringGrid1.Cells[3,j]:=p;
end;

procedure TForm1.Button1Click(Sender: TObject);
var
   f:TextFile;
   p,s:String;
   j:integer;
begin
  StringGrid1.RowCount:=3;
  StringGrid1.Cells[1,1]:= 'Line Item Number';
  StringGrid1.cells[2,1]:= 'Date';
  StringGrid1.cells[3,1]:= 'Product Code';
  StringGrid1.cells[4,1]:= 'Status';

   AssignFile(f,'C:\test.txt');
   Reset(f);
j:=2;
repeat
 repeat
  repeat
If not SeekEof(f) then    Readln(f,s);
  until (StrLen(PCHAR(s))>=2) or (SeekEof(f));
 until ((s[1]='|') and (s[3]='M') and (s[4]='X')) or (SeekEof(f));
If  not SeekEOF(f) then
begin
p:=s[3]+s[4]+s[5]+s[6]+s[7]+s[8]+s[9];
StringGrid1.Cells[1,j]:=p;
If not SeekEof(f) then Readln(f,s); // doted line
If not SeekEof(f) then Readln(f,s);//data line
separateSTR(s,j);
If not SeekEof(f) then Readln(f,s);
If not SeekEof(f) then Readln(f,s);
Form1.StringGrid1.Cells[4,j]:=Trim(s);
j:=j+1;
StringGrid1.RowCount:=StringGrid1.RowCount+1;
end;
until SeekEof(f);
CloseFile(f);


end;

procedure TForm1.Button2Click(Sender: TObject);
var
Excel: Variant;
row,i:integer;
begin
 try
    excel:=CreateOleObject('Excel.Application');
  except
    Raise Exception.Create('Excel not available');
    exit;
  end;
  excel.Workbooks.Add;
  Excel.cells[1,1].ColumnWidth := 15;
  Excel.cells[1,2].ColumnWidth := 10;
  Excel.cells[1,3].ColumnWidth := 15;
  Excel.cells[1,4].ColumnWidth := 70;
for row:=1 to StringGrid1.RowCount do
  for i := 1 to 4 do
  begin
  Excel.Columns[i].HorizontalAlignment := xlcenter;
  Excel.cells[row,i].value := Trim(StringGrid1.Cells[I,ROW]);
  end;
  excel.visible := true;
  excel := Unassigned;
end;
0
 

Author Comment

by:warchief07
ID: 10864723
you guys are really great,n please give me some time to test out and i will let you know the outcome. Thanks

Regards...
0
 

Author Comment

by:warchief07
ID: 10873668
calinutz... i tried your method, however the product code and status produce weird characters... basically the code works already i will try to modify it to get the output i want... will let you know when it's done. Thanks
0
 

Author Comment

by:warchief07
ID: 10875253
ok, it's done now... thanks everybody for helping out my first delphi program :)

Regards...
0
 
LVL 22

Expert Comment

by:Ferruccio Accalai
ID: 10875762
OK, if it's done then you have to accept an answer and PAQ this question.....
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Creating an auto free TStringList The TStringList is a basic and frequently used object in Delphi. On many occasions, you may want to create a temporary list, process some items in the list and be done with the list. In such cases, you have to…
In my programming career I have only very rarely run into situations where operator overloading would be of any use in my work.  Normally those situations involved math with either overly large numbers (hundreds of thousands of digits or accuracy re…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now