• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1692
  • Last Modified:

Reading formatted text and export to Excel...

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
warchief07
Asked:
warchief07
  • 4
  • 3
  • 3
  • +1
1 Solution
 
mokuleCommented:
Maybe You should parse the text with regular expressions.

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

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
 
calinutzCommented:
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 Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
Ferruccio AccalaiSenior developer, analyst and customer assistance Commented:
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
 
mokuleCommented:

Ferruccio: I didn't read Your code too carefully. But have question.
Is it behaves good when found MX somewhere else in text?
0
 
Ferruccio AccalaiSenior developer, analyst and customer assistance Commented:
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
 
calinutzCommented:
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
 
calinutzCommented:
Yes and it behaves well when encountering MX somewhere else in the text
0
 
calinutzCommented:
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
 
warchief07Author Commented:
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
 
warchief07Author Commented:
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
 
warchief07Author Commented:
ok, it's done now... thanks everybody for helping out my first delphi program :)

Regards...
0
 
Ferruccio AccalaiSenior developer, analyst and customer assistance Commented:
OK, if it's done then you have to accept an answer and PAQ this question.....
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 4
  • 3
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now