Subject Re: dbimport2
From Mustansir Ghor <mustan31@hotmail.com>
Date Tue, 01 Jun 2021 12:10:16 -0400
Newsgroups dbase.getting-started

Dear Mervyn Sir

Thank You.

I shall go ahead  using your program.

Best Regards
Mustansir

Mervyn Bick Wrote:

> On 2021/05/31 18:27, Mustansir Ghor wrote:
 > > Dear All
 > >
> > I am trying to import from Excel Worksheet into a dbf table using dbimport2. But it hangs.
 > >
> > I am attaching the excel file and snapshot of program.
 > >
> > I would request somebody to find the error may be it is in my excel file.
 > >
>
> It is quite possible that dbfImport2 doesn't know how to handle .xlsx
> files.  There doesn't seem to be a separate website for dbfImport2 so
> you should perhaps raise the problem in the bug_report newsgroup.
 >
> I'm using an older version of Excel but Microsoft has provided an add-on
> which allows me to read, but not save to, .xlsx files.  Unfortunately
> the add-on doesn't work with oleautoclient().  If you have a version of
> Excel that can handle .xlsx files you should be able to use
> oleautoclient() successfully.
 >
> I opened the .xlsx file in Excel and saved it as a .xls file.  From
> there I was able to use oleautoclient() to save to a .csv file and from
> there into a .dbf file.  For some unknown reason my copy of Excel uses a
> semi-colon as the separator instead of a comma in the .csv file.
 >
> This is a circuitous way of dealing with the problem but it may help you
> to carry on until dBase LLC can sort out the problem with dbfImport2.
 >
> The little program I used and the resulting .dbf are attached.
 >
> Mervyn
 >
> if file('nhif2.dbf')
 >  drop table nhif2
 > endif
 >
> if not file('nhif2.dbf')
 >    create table nhif2  (fname character(15),sname character(15),;
 >      tname character(15))
 > endif
 >
>
> cWorkbook = "d:\examples\plus2019\nhif2.xls"  //path required
 > cworkbookSave = "d:\examples\plus2019\nhif2.csv" //path required
 > oExcel  = new oleAutoclient("Excel.Application")
 > oExcel.workbooks.open(cWorkbook)
 > oExcel.application.displayalerts = false
 > //oExcel.visible = true
 > //if type( "SetForegroundWindow" ) <> "FP"
 > //   extern CLONG SetForegroundWindow( CHANDLE ) user32.dll
 > //endif
 > //SetForegroundWindow( oExcel.Application.Hwnd)
 > oSheet = oExcel.Sheets("APr 2021 NHIF").Select()
 > oExcel.activeWorkbook.saveAs(cWorkbookSave,6)   //As .csv file
 > oExcel.activeWorkbook.close()
 > set procedure to :duflp:stringex.cc
 > q = new query()
 > q.sql = "select * from nhif2"
 > q.active = true
 > f = new file()
 > f.open(cWorkbookSave)
 > cRead = f.gets(100000) // Skip record with field names
 > do while not f.eof()
 >   cRead = f.gets(100000)
 >   aRecord = new stringex(cRead).breakstring(";",true) //check separator in .csv file
 > q.rowset.beginAppend()
 >   for n = 1 to aRecord.size
 >       q.rowset.fields[n].value = aRecord[n]
 >    next
 >    q.rowset.save()
 > enddo
 > f.close()
 > q.active = false
 > close procedure :duflp:stringex.cc
 > msgbox('Done')
 >