Import excel to dbgird

[flori]

I will get the information from laboratory (csv format) which contain pH: 7, P2O5: 45, etc.  The fields/columns of the result-file the same each time .
Im enclosing the sample csv format, i get this information from laboratory.  And this laboratory SCLAB.csv need to import all information to dbgrid and save...
The users are every day get a results and should be import to.... dbgrid... all data can be saved the database. 

[rvk]

Stop calling it CSV format. CSV is a comma separated text file. You attached a .xlsx file which has a completely different format.
(Calling it a CSV format while it really is xslx only confused the issue)

Note: If you really DID get a SCLAB_01.CSV then you should say so directly because everything below isn't needed.
(But you included a .xlsx file so I assume the lab really send you a .xlsx file !!!!)

You need to read the xlsx file (which is much harder than a CSV file) and append it to all the other results.

You can do that in 3 different ways.
1) use the ODBC connection Michael mentioned. But it needs some figuring out how to set it up correctly.

2) use the fpspreadsheet to read the file and append it to the database

3) use the fpspreadsheet to read the file and append it to another spreadsheet (the same each time). In that case you still have a xslx but it grows with each data-import.

Method 2 uses a real database (like Firebird or SQLite) and method 3 will just use a xlsx-file as a complete file where all the results are appended to.

Did you already try to install fpspreadsheet?
If you did, here is a small snippet to open the file and begin reading.

Code: Pascal  [Select][+][-]

1. var
2.   MyWorkbook: TsWorkbook;
3.   MyWorksheet: TsWorksheet;
4. begin
5.   MyWorkbook := TsWorkbook.Create;
6.   try
7.     MyWorkbook.ReadFromFile('Laboratroy_SCLAB_01.xlsx', sfOOXML);
8.     MyWorksheet := MyWorkbook.GetFirstWorksheet;
9.     ShowMessage(MyWorksheet.ReadText(1, 0));
10.   finally
11.     MyWorkbook.Free;
12.   end;
13. end;

This can be adjusted to read all the result lines and add it to another complete result-workbook or add it to a database.

[flori]

yes. I installed fpspreadsheet.
fpspreadsheet is working.

[flori]

just MyWorkbook := TsWorkbook.Create;  not working

[rvk]

just MyWorkbook := TsWorkbook.Create;  not working

Then just add this under the implementation:

Code: Pascal  [Select][+][-]

1. uses fpspreadsheet, fpsallformats;

Otherwise, explain "what isn't working". Do you get an error?

[flori]

I create export button. That is working perfect.
sWorksheetGrid not good for me, because i dont know how do I import my content into dbgrid and save.

MyWorkbook := TsWorkbookSource.Create; 
unit2.pas(53,34) Error: Wrong number of parameters specified for call to "Create"

[flori]

i upload export import
https://1drv.ms/f/s!AqVsnEe7He7Ngvx7CL0GmuYEPr6yLg

[rvk]

MyWorkbook := TsWorkbookSource.Create; 
unit2.pas(53,34) Error: Wrong number of parameters specified for call to "Create"

Not sure why you get this error. Try the attached example. Does that work for you?

i upload export import
https://1drv.ms/f/s!AqVsnEe7He7Ngvx7CL0GmuYEPr6yLg

What the H$#%#$% 
Why is there a .xls in this example. You do know .xls is a different format from .xlsx? fpspreadsheet can handle both but why are you working with files (.csv and .xls and phone) while you have files from the Lab which are different? Why are you not just working with those files in your development?

[wp]

The attached demo imports the xlsx file of rvk's post into a TBufDataset and displays it in a DBGrid. You certainly can generalize the example for the ZEOS components.

Please note that field type is extracted from the data found in the second data row (underneath the title row). If a cell is empty here, just as here with the PMOL cell, then the field type is assumed to be a string - maybe not what you want. Therefore you must make sure that the first file that you import has complete data.

Note also that new data are added if the data file already exists, but it is not checked whether the fields definitions are the same. I hope you can implement this on the basis of the code provided.

[flori]

Thank you very much!I am very grateful for the work!!!!!!!!!!!!!

[flori]

Yes I  generalized the example for the ZEOS components.
Just BufDataset1.CreateDataset not generalized for the zeos. I do not know what its the equivalent.
ZReadOnlyQuery1.CreateDataset is not working.

[wp]

This is a example shortended from TvPlanIt which creates a table by means of ZEOS:

Code: Pascal  [Select][+][-]

1. procedure TVpZeosDatastore.CreateTable;
2. begin
3.   FConnection.ExecuteDirect(
4.     'CREATE TABLE Contacts ('+
5.       'RecordID INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, '+
6.       'ResourceID INTEGER, ' +
7.       'FirstName VARCHAR(50), '+
8.       'LastName VARCHAR(50), '+
9.       'Title VARCHAR(20) ,'+
10.       'Category INTEGER, '+
11.       'Birthdate DATE, '+
12.       'Anniversary DATE, '+
13.       'Company VARCHAR(50), '+
14.       'Department VARCHAR(50), '+
15.       'UserField VARCHAR(100) )'
16.   );
17. end;

[flori]

Thank you. The TBufDataset is working perfect.
When I try to CREATE TABLE ZEOS...  I get error code -607.

https://1drv.ms/f/s!AqVsnEe7He7Ngvx7CL0GmuYEPr6yLg

[rvk]

When I try to CREATE TABLE ZEOS...  I get error code -607.

Please, always provide the complete error message.

You can click in the message and press Ctrl+C to copy it.
Then paste it in a message (and strip irrelevant info).

PS. I see that the PHONE table already exists in your PHONE.GDB. So if it does, you shouldn't try to create it again.

[flori]

ok. I try to change db name.
I get