Importing DBF file in SQL Server 2012

Question

I have over 200+ dbf files having different schema. I have used SSIS to import .dbf files, but in order to automate the task I want to use OpenRowset. OpenRowSet is working fine for Excel Files, but not for .dbf. I have written

SELECT [LRSNum],[AppUpdD],[AppStat],[PIN] FROM   OPENROWSET('MICROSOFT.ACE.OLEDB.12.0','dBASE 5.0;Database=D:\Sales-  data\SalesSourcefile\2016\December\Shape\Martin\real_land\', 'SELECT [LRSNum],[AppUpdD],[AppStat],[PIN] FROM real_land.dbf'); 

Any help will be appreciated.

I am using SQL Server 2012, Windows 8.1.

Installed Foxpro driver, but when selected foxpro using DTS, it fails.

Answer 1

FYI - You can do automated uploads of DBFs to SQL Server using SQL Server Upsizing Wizard for FoxPro.

With this tool, you have to do the uploading from FoxPro, and the DBFs must be attached to a FoxPro DBC.

http://www.codemag.com/article/0703052

The latest version if available from VFPX on GitHub: UpsizingWizard

Answer 2

Finally, this is working

SELECT  * FROM OPENROWSET ('MICROSOFT.ACE.OLEDB.12.0','dBase 5.0;HDR=YES;IMEX=2;DATABASE=\Dbf Directory\',
'SELECT * FROM dbf_filename.dbf')

Answer 3

I once had to do this too and wrote some C# code for the import.

Just as a try: With this line I open the connection within C#

var con = new OdbcConnection("Driver={{Microsoft dBASE Driver (*.dbf)}};Dbq=C:\\SomePath;DriverID=277;");

Might be, that you can get something out of this.

Some C# code

The following code is taken from one of my C# projects. I modified it to be neutral, but I cannot guarantee, that this is working right so:

    public List<string> Ambus;
    public List<string> Tbls;
    public List<string> errList;
    public List<string> SQLs;

    private void btnImport_Click(object sender, EventArgs e) {
        SqlConnection sqlcon;
        SqlCommand sqlcmd;
        
            SQLs.Clear();
            Tbls.Clear();
            
            var con = new OdbcConnection("Driver={{Microsoft dBASE Driver (*.dbf)}};Dbq=C:\\SomePath;DriverID=277;");
            con.Open();

            var tbls = con.GetSchema(OdbcMetaDataCollectionNames.Tables);
            foreach (System.Data.DataRow r in tbls.Rows) {
                Tbls.Add(r["TABLE_NAME"].ToString());
            }

            DataTable cols = null;
            var sb = new StringBuilder();

            int i = 0;
            foreach (var tblnm in Tbls) {
                i++;

                sb.Clear();

                try {
                    cols = con.GetSchema(OdbcMetaDataCollectionNames.Columns, new string[] { null, null, tblnm, null });

                    sb.AppendFormat(" CREATE TABLE dbo.[{0}](TableName VARCHAR(100) NOT NULL ", tblnm);
                    int count = 0;
                    foreach (DataRow colrow in cols.Rows) {
                        var colInf = string.Format(" ,{0} {1} NULL", colrow["COLUMN_NAME"].ToString(), this.createDataType(colrow["TYPE_NAME"].ToString(), colrow["COLUMN_SIZE"].ToString(), colrow["DECIMAL_DIGITS"].ToString(), colrow["NUM_PREC_RADIX"].ToString()));
                        sb.Append(colInf);
                        count++;
                    }
                    sb.Append("); ");
                    SQLs.Add(sb.ToString());
                    sb.Clear();

                    var cmd = new OdbcCommand("SELECT * FROM [" + tblnm + "]", con);
                    var reader = cmd.ExecuteReader();

                    while (reader.Read()) {
                        var vals = createVals(cols, reader, tblnm);
                        string insStat = string.Format(" INSERT INTO dbo.[{0}] VALUES ('{0}',{1});", tblnm, vals);
                        SQLs.Add(insStat);
                    }
                }
                catch (Exception exo) {
                    errList.Add(string.Format("{0}:{1}", tblnm, exo.Message));
                }
            con.Close();

            sqlcon = new SqlConnection("Data Source=SomeSQLServer;Initial Catalog=master;User ID=sa;pwd=SomePwd");
            sqlcon.Open();
            sqlcmd = new SqlCommand("USE SomeTargetDB;", sqlcon);
            sqlcmd.ExecuteNonQuery();
            
            i = 0;
            foreach (string s in SQLs) {
                i++;
                //Progress-output: this.Text = string.Format("{0} von {1}", i, SQLs.Count);
                sqlcmd = new SqlCommand(s, sqlcon);
                sqlcmd.ExecuteNonQuery();
            }
            sqlcon.Close();
        }
    }

    private string createDataType(string typ, string size, string dec, string prec) {
        switch (typ.ToLower()) {
            case "char":
                return "NVARCHAR(" + size + ")";
            case "logical":
                return "BIT";
            case "numeric":
                dec = dec == string.Empty ? null : dec;
                prec = prec == string.Empty ? null : prec;
                int d = int.Parse(dec ?? "0");
                int p = int.Parse(prec ?? "0");
                if (d == 0 && p == 0)
                    return "INT";
                else if (d > 0 && p > 0)
                    return string.Format("DECIMAL({0},{1})", d, p);
                else if (d == 0 && p > 0)
                    return "FLOAT";
                else
                return null;
            case "date":
                return "DATETIME";
            default:
                return null;
        }
    }

    private string createVals(DataTable cols, OdbcDataReader reader, string tblnm) {
        var sb = new StringBuilder();
        sb.Append("'" + tblnm + "'");
        foreach (DataRow colrow in cols.Rows) {
            var val = string.Empty;
            try {
                val = reader[colrow["COLUMN_NAME"].ToString()].ToString();
            }
            catch { }
            if (val.Trim().Length == 0)
                val = "NULL";
            else {
                if (colrow["TYPE_NAME"].ToString().ToLower() == "char")
                    val = val.Replace("'", "''");
                if (colrow["TYPE_NAME"].ToString().ToLower() == "numeric")
                    val = val.Replace(".", "").Replace(",", ".");
                if (colrow["TYPE_NAME"].ToString().ToLower() == "date") {
                    var d = DateTime.Parse(val, System.Globalization.CultureInfo.CurrentCulture);
                    if (d.Year < 1900 || d.Year > 2020)
                        d = new DateTime(1900, d.Month, d.Day, d.Hour, d.Minute, d.Second);
                    val = d.ToString("dd.MM.yyyy HH:mm:ss");
                }
                val = "'" + val + "'";
            }
            
            sb.AppendFormat(",{0}", val);
        }
        return sb.ToString();
    }