Import excel to dbgird

[flori]

Hi!
I would like to import excel (xls or csv) to dbgird. I use ZConnection, ZQuery, ZTransaction, dbgird, DataSource.
I tried import excel to memo1. When I tried excel to dbgrid does not work.
var
  column: string;
  doc: TextFile;
  list: TStringList;
  i: Integer;
begin
  i := 0;
  // Lista
  list := TStringList.Create;
  // Dialogus inditas
  OpenDialog1.Execute;
    AssignFile(doc, OpenDialog1.FileName);
  try
    Reset(doc);
    while not eof(doc) do
    begin
      Memo1.Clear;
      readln(doc,column);
      if i=0 then
         begin
           i := i+1;
           Continue;
         end;
      list.Delimiter:=',';
      list.StrictDelimiter:=True;
         list.DelimitedText:=column;
      ShowMessage(list.Text);
      Memo1.Append(list.Text);
      Label1.Caption:=inttostr(list.Count);
      list.Clear;
      i := i+1;
    end;
  finally
    list.Free;
  end;
  CloseFile(doc);
  Memo1.SelStart:=1;     

[rvk]

I would like to import excel (xls or csv) to dbgird. I use ZConnection, ZQuery, ZTransaction, dbgird, DataSource.
I tried import excel to memo1. When I tried excel to dbgrid does not work.

I'm not sure I see your problem.

You code works fine. It reads from the second line onward and shows all the columns it read. It empties out Memo1 and shows the line.

Code: [Select]

D4
2018.04.20
dd5
41
10
658
511
241
0
I.
$7.23
$0.02
$2.90
$6.07
$0.00
$0.00
$0.00
$0.00

I'm also not sure why you mention excel and dbgird etc.
You will never be able to read to a TDBGrid because a TDBGrid shows a table from a database. So you would first need to import those lines into a database.

You could however show the lines in a TStringGrid.

Also note that Excel-format isn't really a text format so you can't just read it like a text-file. The attached example reads a csv which is fine to read as text-file.

[flori]

Yes. I would like to  all rows import to database and after show dbgird.
 D u know how to do it?

[rvk]

D u know how to do it?

I do.

But you said it doesn't "work" for you.

What exactly did you try already yourself.
(and what database do you use? and do you already have a defined table?)

I could create an elaborate example but you would probably not learn much from it.

[flori]

I use Firebird 2.5,  Database Zeos. Im enclosing program and gdb database and excel file which should be imported into the database

[rvk]

I use Firebird 2.5,  Database Zeos. Im enclosing program and gdb database and excel file which should be imported into the database

First of all, like I said before... You can't read real Excel files like text files.
They look like the image below as text.

You first need to convert that to CSV. You could do that in Excel itself (Save as) or use fpspreadsheet.
(There is even a complete spreadsheet-program example included)

[Michael Collier]

Hi, I downloaded the attached demo project to get the phone_book.xlsx file

I tried opening it via ADO but my jet driver cant handle *.xlsx but can handle *.xls so I converted it and opened it in a data browser written in Delphi using TDBGrid, see screen shot.

Maybe this could be an option if you get stuck..not sure, haven't done anything with excel in a long time, all the best, Mike..

[wp]

You do not explain why you are using the database approach to read an Excel file. It is much easier with fpspreadsheet - look at the attached demo.

Please note: In order to run this demo you must install the fpspreadsheet package. The easiest way to do this is by means of the Online Package Manager which comes with Laz 1.8+.

If you really need the xlsx file in a database look at the "db_import_export" demo in the "examples" folder of the fpspreadsheet installation.

[Michael Collier]

You do not explain why you are using the database approach to read an Excel file.

I mentioned it because the OP wanted to read the data into a DBGrid

I would like to import excel (xls or csv) to dbgird. I use ZConnection, ZQuery, ZTransaction, dbgird, DataSource.
I tried import excel to memo1. When I tried excel to dbgrid does not work.

[valdir.marcos]

I would like to import excel (xls or csv) to dbgird.
I use ZConnection, ZQuery, ZTransaction, dbgird, DataSource.

Try FPSpreadsheet to open the Microsoft Excel sheets (xls, xlsx, csv) and then you can easily send its data to any database, including Firebird.
http://wiki.freepascal.org/FPSpreadsheet

Specifically for CSV files, you can also try:
http://wiki.freepascal.org/CSV

[wp]

I mentioned it because the OP wanted to read the data into a DBGrid

Are you sure that the data must be displayed in a DBGrid or just any other grid? For the second case I posted the example with the FPSpreadsheetGrid

[flori]

Thank you!

Yes, I use excel, because the  customers (soil laboratory) it send the results in an excel. After the users have to import from  excel (example: csv, etc) to dbgrid and save it.

[Michael Collier]

Are you sure that the data must be displayed in a DBGrid

The OP specifically asks for DBGrid

I'm also not sure why you mention excel and dbgird etc.
You will never be able to read to a TDBGrid because a TDBGrid shows a table from a database. So you would first need to import those lines into a database.

So I tried to point out that excel (in so far as *.xls and not *.xlsx is concerned)  *can* be read using the OP existing components (ZConnection, ZQuery, ZTransaction, dbgird, DataSource) via ODBC/ADO/OLEDB (as per screenshot). The connection string is just a single line to connect to the excel file and I am assuming tha z-components can handle ODBC/ADO/OLEDB

[rvk]

After the users have to import from  excel (example: csv, etc) to dbgrid and save it.

Save it to where?
What are they (you) planning to do with the information?

Do you need to gather all the results (multiple excel files over a period of time) to one database?

Are the fields/columns of the result-file the same each time?
Do all the results need to be gathered to one table?

The connection string is just a single line to connect to the excel file and I am assuming tha z-components can handle ODBC/ADO/OLEDB

Does that work without Excel being installed on the computer? If not... then the question to OP becomes... is Excel installed on all the computers doing the import?

[Michael Collier]

Does that work without Excel being installed on the computer?

Excel isn't installed on my machine but I can open *.xls. I may be able to open *.xlsx too but would need to update the drivers, quick search gives this
http://help.loftware.com/pages/viewpage.action?pageId=27099554