Convert Visual Foxpro dbf to dBaseIII

Question

EDIT: I found out that this error was being thrown due to the new data file being in Visual Foxpor dbf form, while my master file is dBaseIII. Any suggestions on how to programmatically change VFP to dBaseIII?

I am opening two .dbf files. One is a master file and one is a file with new data. I wish to insert the new data into the master file.

I am connecting to the directory that holds the files like so:

 Connection connection = null;
                    String dbString = "jdbc:odbc:Driver={Microsoft dBASE Driver (*.dbf)};DBQ=" + dealerSNS + "\\";

                    try
                    {
                        System.out.println("Opening Database Directory " + dealerSNS );
                        Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
                        connection = DriverManager.getConnection(dbString, "", "");
                        System.out.println("Connection established!");
                    }
                    catch (ClassNotFoundException cnfEx)
                    {
                        System.out.println("* Unable to load driver! *");
                        System.exit(1);
                    }
                    catch (SQLException sqlEx)
                    {
                        System.out.println(dbString);
                        System.out.println("* Cannot connect to database! * SQL = " + sqlEx);
                        System.exit(1);
                    }

Then I call my prepared statement:

 try
        {
        String update = "INSERT INTO fullSNS SELECT * FROM newSNS";
         PreparedStatement ps = connection.prepareStatement(update);
               ps.executeUpdate();
                  System.out.println("query: " + ps+ " worked!");
                           }
                           catch (SQLException se)
                           {
                                se.printStackTrace();  

                           }

Both .dbf's have the exact same fields and are in the same directory, so I'm not sure what I'm doing wrong that it won't link them up.

Any suggestions/ideas?

My stacktrace looks like this:

java.sql.SQLException: [Microsoft][ODBC dBase Driver] External table is not in the expected format.
at sun.jdbc.odbc.JdbcOdbc.createSQLException(JdbcOdbc.java:6964)
at sun.jdbc.odbc.JdbcOdbc.standardError(JdbcOdbc.java:7121)
at sun.jdbc.odbc.JdbcOdbc.SQLExecute(JdbcOdbc.java:3156)
at sun.jdbc.odbc.JdbcOdbcPreparedStatement.execute(JdbcOdbcPreparedStatement.java:215)
at sun.jdbc.odbc.JdbcOdbcPreparedStatement.executeUpdate(JdbcOdbcPreparedStatement.java:137)
at ads.SmooshNMoveFiles.checkNSmoosh(SmooshNMoveFiles.java:116)
at ads.ActiveTimer.reportToWork(ActiveTimer.java:82)
at ads.ActiveTimer.run(ActiveTimer.java:28)
at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:471)
at java.util.concurrent.FutureTask.runAndReset(FutureTask.java:304)
at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$301(ScheduledThreadPoolExecutor.java:178)
at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:293)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
at java.lang.Thread.run(Thread.java:724)

DRapp DRapp · Accepted Answer · 2013-10-25T14:44:09

It depends on how "dBaseIII" oriented the table really is. Some of the earlier versions going way back have different file header information and that may be the issue. In that case, you might need to do a one-time process of opening and copying the VFP version table to a dBaseIII format, then move that into the production path for your connection.

if you use the Microsoft Visual Foxpro OleDb Provider, you can connect to the path where the table is, and execute a script something like

USE YourTable
COPY to NewVersion TYPE FOXPLUS

Disadvantage of older dBASE file formats is it does not recognize the corresponding index files without being explicitly opened and won't optimize queries... but again that is based on very old dBASE file formats.

Here's one showing a sample SQL Command for execution

Convert Visual Foxpro dbf to dBaseIII

2 Answers