Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Reading formatted text and export to Excel...

Posted on 2004-04-18
13
Medium Priority
?
1,688 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 23

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 23

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
 
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 1500 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 23

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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Objective: - This article will help user in how to convert their numeric value become words. How to use 1. You can copy this code in your Unit as function 2. than you can perform your function by type this code The Code   (CODE) The Im…
Introduction I have seen many questions in this Delphi topic area where queries in threads are needed or suggested. I know bumped into a similar need. This article will address some of the concepts when dealing with a multithreaded delphi database…
Video by: ITPro.TV
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…
Suggested Courses

730 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