FiveTech Support Forums

FiveWin / Harbour / xBase community
Board indexAll discussionsLogin (GitHub)
Board index FiveWin for Harbour/xHarbour Transform .DB file to .DBF
sambomb
Posts: 389
Joined: Mon Oct 13, 2008 11:26 AM
Transform .DB file to .DBF
Posted: Thu Feb 05, 2015 11:52 AM

I have a .DB file that I wish to convert to .DBF files
Can I make it through fivewin?
Can I export to CSV and import it to a DBF afterward?

Email: SamirSSabreu@gmail.com
xHarbour 1.2.3 + Fwhh 20.2
nageswaragunupudi
Posts: 10733
Joined: Sun Nov 19, 2006 05:22 AM
Re: Transform .DB file to .DBF
Posted: Thu Feb 05, 2015 01:19 PM
Normally .DB extension is used for SQLite database, though there can be exceptions.
If you know the .db file is sqlite database and if you have sqlite3 ODBC installed on your computer, you can use this function to open all tables in the db and save them as dbf files one by one. I am also assuming that the *.db database is not password protected/

Usage
dbtodbf( <filename>.db )

Code (fw): Select all Collapse
function dbtodbf( cDb )

   local oCn, oRs, aTables, cTable
   
   oCn      := FW_OpenAdoConnection( cDb )  // connect to sqlite3 database cDb
   aTables  := FW_AdoTables( oCn )          // get list of tables
   xbrowser aTables   // view the list of tables for information
   
   for each cTable in aTables
      oRs   := FW_OpenRecordSet( oCn, cTable )
      FW_AdoExportToDBF( oRs, cFileSetExt( cTable, "dbf" ), .t. ) 
           // By setting the last parameter as .T. 
           // you can view/edit/modify structure before saving
           // if the fieldnames exceed 10 chars you will have an opportunity to rename the field names
      oRs:Close()
   next
   
   ? "Done"
   
return nil
Regards



G. N. Rao.

Hyderabad, India
sambomb
Posts: 389
Joined: Mon Oct 13, 2008 11:26 AM
Re: Transform .DB file to .DBF
Posted: Thu Feb 05, 2015 03:50 PM
sqlite3 ODBC installed on your computer


How I check this?

It is SQLite database in the notepad the first characters is:
"SQLite format 3"
Email: SamirSSabreu@gmail.com
xHarbour 1.2.3 + Fwhh 20.2
nageswaragunupudi
Posts: 10733
Joined: Sun Nov 19, 2006 05:22 AM
Re: Transform .DB file to .DBF
Posted: Thu Feb 05, 2015 04:11 PM

Download and install latest ODBC driver from here

http://www.sqlite.org/

Regards



G. N. Rao.

Hyderabad, India
sambomb
Posts: 389
Joined: Mon Oct 13, 2008 11:26 AM
Re: Transform .DB file to .DBF
Posted: Thu Feb 05, 2015 07:31 PM
Code (fw): Select all Collapse
Error: Unresolved external '_HB_FUN_FW_OPENADOCONNECTION' referenced from OBJ\MAIN.OBJ
Error: Unresolved external '_HB_FUN_FW_ADOTABLES' referenced from OBJ\MAIN.OBJ
Error: Unresolved external '_HB_FUN_FW_OPENRECORDSET' referenced from OBJ\MAIN.OBJ
Error: Unresolved external '_HB_FUN_FW_ADOEXPORTTODBF' referenced from OBJ\MAIN.OBJ
Error: Unresolved external '_HB_FUN_CFILESETEXT' referenced from OBJ\MAIN.OBJ
Email: SamirSSabreu@gmail.com
xHarbour 1.2.3 + Fwhh 20.2
nageswaragunupudi
Posts: 10733
Joined: Sun Nov 19, 2006 05:22 AM
Re: Transform .DB file to .DBF
Posted: Thu Feb 05, 2015 07:36 PM

Sorry I did not notice that you are using very old fwh versions.
All these functions are available in FWH for almost a year or so.

Regards



G. N. Rao.

Hyderabad, India
sambomb
Posts: 389
Joined: Mon Oct 13, 2008 11:26 AM
Re: Transform .DB file to .DBF
Posted: Thu Feb 05, 2015 07:40 PM

Can I send you the file?

Email: SamirSSabreu@gmail.com
xHarbour 1.2.3 + Fwhh 20.2
nageswaragunupudi
Posts: 10733
Joined: Sun Nov 19, 2006 05:22 AM
Re: Transform .DB file to .DBF
Posted: Thu Feb 05, 2015 07:43 PM

ok send me.
I can also attempt a special program for you, but sending the db to me is easier

Regards



G. N. Rao.

Hyderabad, India
nageswaragunupudi
Posts: 10733
Joined: Sun Nov 19, 2006 05:22 AM
Re: Transform .DB file to .DBF
Posted: Fri Feb 06, 2015 04:13 AM
You can try this code. This code does not use any FWH functions but only xHarbour functions.
If you already installed SqLite 3 ODBC driver on your computer this program will work.
I tested the function at my end with some db files I have with me.
Code (fw): Select all Collapse
function db2csv( cDb )

   local oCn, cCnStr, oTables

   ? "start"
   cCnStr   := "Driver=SQLite3 ODBC Driver;Database=" + cDb
   oCn      := TOleAuto():New( "ADODB.Connection" )
   oCn:ConnectionString    := cCnStr
   oCn:CursorLocation      := 3
   oCn:Open()
   oTables  := oCn:OpenSchema( 20, { nil, nil, nil, "TABLE" } )
   do while !oTables:Eof()
      table2csv( oTables:Fields( "TABLE_NAME" ):Value, oCn )
      oTables:MoveNext()
   enddo
   oTables:Close()
   oCn:Close()
   ? "done"

return nil

static function table2csv( cTable, oCn )

   local oRs, n, cStr, cCsv

   oRs   := TOleAuto():New( "ADODB.RecordSet" )
   WITH OBJECT oRs
      :Source           := 'SELECT * FROM "' + cTable + '"'
      :ActiveConnection := oCn
      :Open()
   END

   cStr  := oRs:Fields( 0 ):Name
   for n := 1 to oRs:Fields:Count() - 1
      cStr  += "," + oRs:Fields( n ):Name
   next
   cStr  += CRLF
   if oRs:RecordCount() > 0
      cStr  += oRs:GetString( 2, oRs:RecordCount() , ",", CRLF )
   endif
   cCsv  := StrTran( cTable, ' ', '_' ) + ".csv"
   MEMOWRIT( cCsv, cStr, .f. )

return nil

This creates one csv file for each table in the db.
Usage : db2csv( "filename.db" )
Regards



G. N. Rao.

Hyderabad, India
sambomb
Posts: 389
Joined: Mon Oct 13, 2008 11:26 AM
Re: Transform .DB file to .DBF
Posted: Fri Feb 06, 2015 10:17 AM
nageswaragunupudi wrote:You can try this code. This code does not use any FWH functions but only xHarbour functions.
If you already installed SqLite 3 ODBC driver on your computer this program will work.
I tested the function at my end with some db files I have with me.
Code (fw): Select all Collapse
function db2csv( cDb )

   local oCn, cCnStr, oTables

   ? "start"
   cCnStr   := "Driver=SQLite3 ODBC Driver;Database=" + cDb
   oCn      := TOleAuto():New( "ADODB.Connection" )
   oCn:ConnectionString    := cCnStr
   oCn:CursorLocation      := 3
   oCn:Open()
   oTables  := oCn:OpenSchema( 20, { nil, nil, nil, "TABLE" } )
   do while !oTables:Eof()
      table2csv( oTables:Fields( "TABLE_NAME" ):Value, oCn )
      oTables:MoveNext()
   enddo
   oTables:Close()
   oCn:Close()
   ? "done"

return nil

static function table2csv( cTable, oCn )

   local oRs, n, cStr, cCsv

   oRs   := TOleAuto():New( "ADODB.RecordSet" )
   WITH OBJECT oRs
      :Source           := 'SELECT * FROM "' + cTable + '"'
      :ActiveConnection := oCn
      :Open()
   END

   cStr  := oRs:Fields( 0 ):Name
   for n := 1 to oRs:Fields:Count() - 1
      cStr  += "," + oRs:Fields( n ):Name
   next
   cStr  += CRLF
   cStr  += oRs:GetString( 2, oRs:RecordCount() , ",", CRLF )
   cCsv  := StrTran( cTable, ' ', '_' ) + ".csv"
   MEMOWRIT( cCsv, cStr, .f. )

return nil

This creates one csv file for each table in the db.
Usage : db2csv( "filename.db" )



Code (fw): Select all Collapse
Application
===========
   Path and name: C:\DbToDbf\Projeto.EXE (32 bits)
   Size:   2,795,520 bytes
   Compiler version: xHarbour build 1.2.1 Intl. (SimpLex) (Rev. 6715)
   FiveWin  Version: FWHX 10.6
   Windows version: 6.1, Build 7601 Service Pack 1

   Time from start: 0 hours 0 mins 17 secs 
   Error occurred at: 06/02/2015, 08:15:21
   Error description: Error ADODB.RecordSet/6  DISP_E_UNKNOWNNAME: GETSTRING
   Args:
     [   1] = N   2
     [   2] = N   0
     [   3] = C   ,
     [   4] = C   


Stack Calls
===========
   Called from: source\rtl\win32ole.prg => TOLEAUTO:GETSTRING(0)
   Called from: C:\DbToDbf\Source\Main.PRG => TABLE2CSV(402)
   Called from: C:\DbToDbf\Source\Main.PRG => DB2CSV(377)
   Called from: C:\DbToDbf\Source\Main.PRG => ACTION1(335)


ok send me.
I can also attempt a special program for you, but sending the db to me is easier


Send me your email please.
Email: SamirSSabreu@gmail.com
xHarbour 1.2.3 + Fwhh 20.2
nageswaragunupudi
Posts: 10733
Joined: Sun Nov 19, 2006 05:22 AM
Re: Transform .DB file to .DBF
Posted: Fri Feb 06, 2015 11:33 AM

1) You got this error because that particular table did not contain any records. I did not check for this in the code. Now I edited and modified the code to check if the table is blank. I advise you to copy the edited code again and try.

2) While you can try the code, you can also send me the db file.
nageswaragunupudi at gmail dot com.

Regards



G. N. Rao.

Hyderabad, India
sambomb
Posts: 389
Joined: Mon Oct 13, 2008 11:26 AM
Re: Transform .DB file to .DBF
Posted: Fri Feb 06, 2015 08:10 PM

Mr. Rao

I received your email, but I can't reduce the length of the collums.
I can split then in two separate fields but I can't loss any data.

Email: SamirSSabreu@gmail.com
xHarbour 1.2.3 + Fwhh 20.2
sambomb
Posts: 389
Joined: Mon Oct 13, 2008 11:26 AM
Re: Transform .DB file to .DBF
Posted: Tue Feb 10, 2015 10:24 AM

Can you break the fields greater than 255 into two fields?

Email: SamirSSabreu@gmail.com
xHarbour 1.2.3 + Fwhh 20.2

Continue the discussion

Static archive · New replies & topics via GitHub Discussions