Recent

Author Topic: Import excel to dbgird  (Read 14795 times)

rvk

  • Hero Member
  • *****
  • Posts: 6111
Re: Import excel to dbgird
« Reply #30 on: April 25, 2018, 01:09:20 pm »
ok. I try to change db name.
I get
That's not a -607 error, is it. You mentioned -607  %)

(You could have pressed Ctrl+C in that message to copy it as text, and Ctrl+V or paste to paste it. There was no need for a screenshot.)

flori

  • Full Member
  • ***
  • Posts: 196
Re: Import excel to dbgird
« Reply #31 on: April 25, 2018, 01:11:20 pm »
I do not want to create a new table. I would like to import into the existing table!

rvk

  • Hero Member
  • *****
  • Posts: 6111
Re: Import excel to dbgird
« Reply #32 on: April 25, 2018, 01:19:34 pm »
I do not want to create a new table. I would like to import into the existing table!
Then you can forget about the whole CREATE TABLE thing.

But you need to make sure any (new) database has the table and it is correctly defined. The code from wp created the database and table if it didn't exist. So if you are sure the table PHONE exists in PHONE.GDB you can delete the whole "check and creation" of the database.

flori

  • Full Member
  • ***
  • Posts: 196
Re: Import excel to dbgird
« Reply #33 on: April 25, 2018, 01:26:51 pm »
My problem is that the import is not working for Zeos just BufDataset. I try to create new table but unnecessary, because i have ,,phone,, table!
The new table create i do not care! I would like to just import into the existing table! I have a good database!

rvk

  • Hero Member
  • *****
  • Posts: 6111
Re: Import excel to dbgird
« Reply #34 on: April 25, 2018, 01:40:07 pm »
I'm missing the ZQuery1.Insert; in your code. You can remove the BufDataset1.Append; and add the Insert into its place.

Code: Pascal  [Select][+][-]
  1.               ZQuery1.Open;
  2.               ZQuery1.DisableControls;
  3.               for r := 1 to maxr do begin
  4.                 // BufDataset1.Append;
  5.                 ZQuery1.Insert;
  6.                 for c := 0 to maxc do begin
  7.                   datacell := worksheet.GetCell(r, c);
Then you probably get an error about PRIMARY INDEX.
Change the "Wheremode" to wmWhereAll in that case because you're not working with a primary index in your table.

After that, the next problem is that you can't have duplicate/identical rows in the table because otherwise Firebird doesn't know which to update when you want to edit a line. For that you would need to add a unique ID and use that as primary key.

flori

  • Full Member
  • ***
  • Posts: 196
Re: Import excel to dbgird
« Reply #35 on: April 25, 2018, 02:09:28 pm »
Rvk,

working :) :) :) :) :)
Thank you very much!

wp

  • Hero Member
  • *****
  • Posts: 11857
Re: Import excel to dbgird
« Reply #36 on: April 25, 2018, 04:16:58 pm »
I'm missing the ZQuery1.Insert; in your code. You can remove the BufDataset1.Append; and add the Insert into its place.
Isn't Append doing the same as Insert (apart from the position where the new record will be added)?

rvk

  • Hero Member
  • *****
  • Posts: 6111
Re: Import excel to dbgird
« Reply #37 on: April 25, 2018, 04:51:39 pm »
I'm missing the ZQuery1.Insert; in your code. You can remove the BufDataset1.Append; and add the Insert into its place.
Isn't Append doing the same as Insert (apart from the position where the new record will be added)?
Yes, but the append was done on BufDataset1 and not on ZQuery1.

flori

  • Full Member
  • ***
  • Posts: 196
Re: Import excel to dbgird
« Reply #38 on: April 25, 2018, 04:56:16 pm »
var
          DataFileName: String;
          fmt: TsCellFormat;
          nfp: TsNumFormatParams;
          workbook: TsWorkbook;
          worksheet: TsWorksheet;
          fieldType: TFieldType;
          fieldsize: Integer;
          fieldname: String;
          datacell: PCell;
          titleCell: PCell;
          r, c: Cardinal;
          maxc, maxr: Integer;
        begin
          Screen.Cursor := crHourglass;
          try
            DataFileName := 'laborminta.xlsx';

            workbook := TsWorkbook.Create;
            try
              workbook.ReadFromFile(DataFileName, sfOOXML);
              worksheet := workbook.GetWorksheetByIndex(0);
              //maxc := worksheet.GetlastColIndex;   //exce;AA-ig
              //maxr := worksheet.GetLastRowIndex;   // excel AA ig
                maxc := worksheet.GetLastColIndex(true);
                 maxr := worksheet.GetLastRowIndex;


              if not FileExists(BufDataset1.FileName) then begin
                // Create a table
                for c:=0 to maxc do begin
                  titlecell := worksheet.GetCell(0, c);
                  datacell := worksheet.GetCell(1, c);
                  fieldsize := 0;
                  fmt := worksheet.ReadCellFormat(dataCell);
                  nfp := workbook.GetNumberFormat(fmt.NumberFormatIndex);
                  case datacell^.ContentType of
                    cctNumber:
                      begin
                        if IsCurrencyFormat(nfp) then
                          fieldType := ftCurrency
                        else
                          fieldType := ftFloat;
                      end;
                    cctDateTime :
                      if IsTimeFormat(nfp) then
                        fieldType := ftTime
                      else if IsDateFormat(nfp) then
                        fieldType := ftDate
                      else
                      fieldType := ftDateTime;
                    else
                      fieldType := ftString;
                      fieldSize := 20;
                  end;
                  fieldname := titlecell^.UTF8StringValue;
                  ZQuery1.FieldDefs.Add(fieldName, fieldType, fieldsize);

                end;

              ZQuery1.Open;
              ZQuery1.DisableControls;
              for r := 1 to maxr do begin
                //BufDataset1.Append;
                  ZQuery1.Insert;
                for c := 0 to maxc do begin
                  datacell := worksheet.GetCell(r, c);
                  case ZQuery1.Fields[c].DataType of
                    ftCurrency,
                    ftFloat,
                    ftInteger : ZQuery1.Fields[c].AsFloat := datacell^.NumberValue;
                    ftDate,
                    ftDateTime,
                    ftTime    : ZQuery1.Fields[c].AsDateTime := datacell^.NumberValue;
                    ftString  : ZQuery1.Fields[c].AsString := datacell^.UTF8StringValue;
                  end;
                end;
                ZQuery1.Post;
                ZQuery1.close;
                ZQuery1.open;
              end;
              ZQuery1.EnableControls;

              end;

            finally
              workbook.Free;
            end;

          finally
            Screen.Cursor := crDefault;
          end;                         

 

TinyPortal © 2005-2018