EXPORT TO .DBF

[Guest]

I need to export some tables in dBase format from Access format. Before I
even buy the Access license I need to be sure that I will be able to exactly
export the data. I need to be sure that all the fields TYPE and LENGHT will
remain as in the original Access format.

How does Access manipulate the export to .dbf, it mantains the Access format
or asks in a previous step which type and leght the fields should have in the
dBase format?

Thanks for your time,

Román.

 

[John Nurick]

Hi Román,

There isn't a 1:1 match between field types in Access (or rather Jet, if
you're referring to data in .mdb files) and in dBASE, so it's not always
possible to preserve the field type and length.

For example, a dBASE date field stores a yyyymmdd string, while a Jet
date/time field is a Double whose integer part is the number of days
since 30/12/1899 and whose fractional part represents the time (midnight
= 0, noon = 0.5).

However, you may not need to buy Access. Jet is installed on all or
almost all Windows computers regardless of whether they have Access, and
you can manipulate data in most .mdb files by writing to the DAO or ADO
libraries in any OLE-aware language. I've pasted a sample VBScript
below; see also http://www.smithvoice.com/vbExport.aspx (among others):


'----------------start of script----------------------
'Sample VBScript to export a table from a .mdb
'file to a .dbf file.
'MDBFile: path and name of .mdb file
'dBaseFolder: folder containing .dbf file
'TableName: name of table to export

Option Explicit

Sub DBImport(MDBFile, dBaseFolder, TableName)
'Procedure to do the work
Dim oJet ' DAO.DBEngine
Dim oDB ' DAO.Database
Dim strSQL ' String

Set oJet = CreateObject("DAO.DBEngine.36")
Set oDB = oJet.OpenDatabase(MDBFile)

strSQL = "SELECT * INTO " _
& "[dBASE 5.0; DATABASE=" & dBaseFolder & ";].[" _
& TableName & "] FROM [" & TableName & "];"

WScript.Echo strSQL

oDB.Execute strSQL

oDB.Close
End Sub


'Main
If WScript.Arguments.Count <> 3 Then
MsgBox "Usage:" & Chr(13) & Chr(10) & Chr(10) _
& " DbfToMdb.vbs MDBFile dBaseFolder TableName " _
& Chr(13) & Chr(10) & Chr(10) _
& "MDBFile: .mdb file containing table to export" _
& Chr(13) & Chr(10) _
& "dBaseFolder: location of .dBase files" _
& Chr(13) & Chr(10) _
& "TableName: name of table to export", _
64, "Export Access/Jet table to .DBF"
Else
DBImport WScript.Arguments(0), _
WScript.Arguments(1), WScript.Arguments(2)
End If
'---------------END OF SCRIPT----------------------------

 

[Guest]

John:

Thanks for your feedback.

I do not have yet the .mdb files, it was just a question.

Anyway it seems that there is no better solution than using access, and
since I do not have the .mdb yet, I will have to ask my manager for an
upgrade from Office 2002 Standard to Professional, do the .dbf to .mdb
conversion, edit the data and then back again to .dbf. My biggest concern is
with decimal numbers. I understand that Access does not allow you to
specifically state the amount of decimals, instead you can have single,
double and the like. Any way I will choose the most precise for my needs and
then export to .dbf.

Thanks,

Hi Román,

There isn't a 1:1 match between field types in Access (or rather Jet, if
you're referring to data in .mdb files) and in dBASE, so it's not always
possible to preserve the field type and length.

For example, a dBASE date field stores a yyyymmdd string, while a Jet
date/time field is a Double whose integer part is the number of days
since 30/12/1899 and whose fractional part represents the time (midnight
= 0, noon = 0.5).

However, you may not need to buy Access. Jet is installed on all or
almost all Windows computers regardless of whether they have Access, and
you can manipulate data in most .mdb files by writing to the DAO or ADO
libraries in any OLE-aware language. I've pasted a sample VBScript
below; see also http://www.smithvoice.com/vbExport.aspx (among others):


'----------------start of script----------------------
'Sample VBScript to export a table from a .mdb
'file to a .dbf file.
'MDBFile: path and name of .mdb file
'dBaseFolder: folder containing .dbf file
'TableName: name of table to export

Option Explicit

Sub DBImport(MDBFile, dBaseFolder, TableName)
'Procedure to do the work
Dim oJet ' DAO.DBEngine
Dim oDB ' DAO.Database
Dim strSQL ' String

Set oJet = CreateObject("DAO.DBEngine.36")
Set oDB = oJet.OpenDatabase(MDBFile)

strSQL = "SELECT * INTO " _
& "[dBASE 5.0; DATABASE=" & dBaseFolder & ";].[" _
& TableName & "] FROM [" & TableName & "];"

WScript.Echo strSQL

oDB.Execute strSQL

oDB.Close
End Sub


'Main
If WScript.Arguments.Count <> 3 Then
MsgBox "Usage:" & Chr(13) & Chr(10) & Chr(10) _
& " DbfToMdb.vbs MDBFile dBaseFolder TableName " _
& Chr(13) & Chr(10) & Chr(10) _
& "MDBFile: .mdb file containing table to export" _
& Chr(13) & Chr(10) _
& "dBaseFolder: location of .dBase files" _
& Chr(13) & Chr(10) _
& "TableName: name of table to export", _
64, "Export Access/Jet table to .DBF"
Else
DBImport WScript.Arguments(0), _
WScript.Arguments(1), WScript.Arguments(2)
End If
'---------------END OF SCRIPT----------------------------

I need to export some tables in dBase format from Access format. Before I
even buy the Access license I need to be sure that I will be able to exactly
export the data. I need to be sure that all the fields TYPE and LENGHT will
remain as in the original Access format.

How does Access manipulate the export to .dbf, it mantains the Access format
or asks in a previous step which type and leght the fields should have in the
dBase format?

Thanks for your time,

Román.

 

[John Nurick]

But why install Access if you want to edit data in dbf files? Why not
use an application that works natively with the .dbf format, such as
dBASE, FoxPro or Lotus Approach (among others)?

Recent versions of Jet have Currency and Decimal data types, which are
scaled extra-long integers. I don't know how well these map to dBASE
number fields, but it's worth investigating.

John:

Thanks for your feedback.

I do not have yet the .mdb files, it was just a question.

Anyway it seems that there is no better solution than using access, and
since I do not have the .mdb yet, I will have to ask my manager for an
upgrade from Office 2002 Standard to Professional, do the .dbf to .mdb
conversion, edit the data and then back again to .dbf. My biggest concern is
with decimal numbers. I understand that Access does not allow you to
specifically state the amount of decimals, instead you can have single,
double and the like. Any way I will choose the most precise for my needs and
then export to .dbf.

Thanks,

Hi Román,

There isn't a 1:1 match between field types in Access (or rather Jet, if
you're referring to data in .mdb files) and in dBASE, so it's not always
possible to preserve the field type and length.

For example, a dBASE date field stores a yyyymmdd string, while a Jet
date/time field is a Double whose integer part is the number of days
since 30/12/1899 and whose fractional part represents the time (midnight
= 0, noon = 0.5).

However, you may not need to buy Access. Jet is installed on all or
almost all Windows computers regardless of whether they have Access, and
you can manipulate data in most .mdb files by writing to the DAO or ADO
libraries in any OLE-aware language. I've pasted a sample VBScript
below; see also http://www.smithvoice.com/vbExport.aspx (among others):


'----------------start of script----------------------
'Sample VBScript to export a table from a .mdb
'file to a .dbf file.
'MDBFile: path and name of .mdb file
'dBaseFolder: folder containing .dbf file
'TableName: name of table to export

Option Explicit

Sub DBImport(MDBFile, dBaseFolder, TableName)
'Procedure to do the work
Dim oJet ' DAO.DBEngine
Dim oDB ' DAO.Database
Dim strSQL ' String

Set oJet = CreateObject("DAO.DBEngine.36")
Set oDB = oJet.OpenDatabase(MDBFile)

strSQL = "SELECT * INTO " _
& "[dBASE 5.0; DATABASE=" & dBaseFolder & ";].[" _
& TableName & "] FROM [" & TableName & "];"

WScript.Echo strSQL

oDB.Execute strSQL

oDB.Close
End Sub


'Main
If WScript.Arguments.Count <> 3 Then
MsgBox "Usage:" & Chr(13) & Chr(10) & Chr(10) _
& " DbfToMdb.vbs MDBFile dBaseFolder TableName " _
& Chr(13) & Chr(10) & Chr(10) _
& "MDBFile: .mdb file containing table to export" _
& Chr(13) & Chr(10) _
& "dBaseFolder: location of .dBase files" _
& Chr(13) & Chr(10) _
& "TableName: name of table to export", _
64, "Export Access/Jet table to .DBF"
Else
DBImport WScript.Arguments(0), _
WScript.Arguments(1), WScript.Arguments(2)
End If
'---------------END OF SCRIPT----------------------------

I need to export some tables in dBase format from Access format. Before I
even buy the Access license I need to be sure that I will be able to exactly
export the data. I need to be sure that all the fields TYPE and LENGHT will
remain as in the original Access format.

How does Access manipulate the export to .dbf, it mantains the Access format
or asks in a previous step which type and leght the fields should have in the
dBase format?

Thanks for your time,

Román.