none
Using vfpoledb driver in .net 2.0 Creating .DBF File Successfully but not open in Any Excel Version

    Question

  • Hi,

    Here is my code with it i create a file. It create successfully but problem is that i am not able to open it in Excel.

    so  if any body have any idea about it so please help us. (Here i am Specifically talking about .DBF Files)

     

      string connString = "Provider=vfpoledb; Data Source=" + System.Web.HttpContext .Current.Server.MapPath(folderPath) + "; Collating Sequence=general;" ;

     

                string createStatement = "Create Table " + tableName + " ( " ;

                OleDbCommand cmd = new OleDbCommand ();

                OleDbConnection conn = new OleDbConnection (connString);           

                createStatement += "USR_TXN_NO" ;

                createStatement += " Numeric(10,0), " ;//USR_TXN_NO

                createStatement += "UNITS" ;

                createStatement += " numeric(19,8), " ;//UNITS

                createStatement += "DOB" ;

                createStatement += " Date, " ;//DOB

                createStatement += "ECS_NO" ;

                createStatement += "  numeric(9,0)) " ;//ECS_NO

                DataSet dsCreateTable = new DataSet ();

                //Create the DBF table

                DataSet dsFill = new DataSet ();

     OleDbDataAdapter daInsertTable = new OleDbDataAdapter (createStatement, conn);

                daInsertTable.Fill(dsFill);

    Wednesday, June 10, 2009 2:27 PM

Answers

  • You can create and run a macro (or automate from C#).

    ActiveSheet.QueryTables.Add( _
        Connection:="OLEDB;Provider=VFPOLEDB;Data Source=C:\YourFolderPath", _
        Destination:=Range("A1"), _
        Sql:="select * from myTable").Refresh

    • Proposed as answer by CetinBasoz Thursday, June 11, 2009 6:10 PM
    • Marked as answer by Riquel_Dong Wednesday, June 17, 2009 1:24 AM
    Wednesday, June 10, 2009 4:52 PM
  • Rushit,

    Please do not post questons here then direct email me with the same questions.

    The answer is, Microsoft changed the format of the dbf file so that new features could be added. Excel only recognizes the old format. But, Excel can use the VFP OLE DB Provider to open the table.
    Craig Berntson MCSD, Visual FoxPro MVP www.craigberntson.com
    • Proposed as answer by Craig Berntson Thursday, June 11, 2009 1:34 PM
    • Marked as answer by Riquel_Dong Wednesday, June 17, 2009 1:24 AM
    Thursday, June 11, 2009 1:34 PM
  • As I understand you are new to development. Is that your school assignment? Even in that case you should try to read and understand the replies. Anyway here is some code that does it via autmation:

    using System;
    using System.Data;
    using System.Data.OleDb;
    using System.Runtime.InteropServices;
    using Microsoft.Office.Interop;
    using System.Reflection;
    
    
    class Sample
    {
        static void Main()
        {
            Sample s = new Sample();
            s.VFPSample("MyVFPTable");
        }
    
        public void VFPSample(string tableName)
        {
            string connString = "Provider=vfpoledb; Data Source=c:\\temp";
            OleDbConnection conn = new OleDbConnection(connString);
    
            string createStatement = String.Format("Create Table {0} " +
                "(USR_TXN_NO n(10,0), UNITS n(19,8), DOB d, ECS_NO n(9,0))",
                tableName);
            conn.Open();
            OleDbCommand cmdCreate = new OleDbCommand(createStatement, conn);
            cmdCreate.ExecuteNonQuery();
    
            OleDbCommand cmd = new OleDbCommand(
                String.Format("insert into {0} values (?,?,?,?)", tableName),
                conn);
            cmd.Parameters.AddWithValue("p1", 1);
            cmd.Parameters.AddWithValue("p2", 10);
            cmd.Parameters.AddWithValue("p3", DateTime.Now.AddDays(-100));
            cmd.Parameters.AddWithValue("p4", 5);
            cmd.ExecuteNonQuery();
            for (int sample = 2; sample <= 10; sample++)
            {
                cmd.Parameters[0].Value = sample;
                cmd.Parameters[1].Value = sample * 10;
                cmd.Parameters[2].Value = DateTime.Now.AddDays(-sample);
                cmd.Parameters[3].Value = sample * 5;
                cmd.ExecuteNonQuery();
            }
            conn.Close();
    
            string XLSFileName = @"c:\temp\VFP9Table.xls";
            string query = String.Format("select * from {0}", tableName);
    
            Microsoft.Office.Interop.Excel._Application xl = 
                new Microsoft.Office.Interop.Excel.Application();
            Microsoft.Office.Interop.Excel._Workbook wb =
                (Microsoft.Office.Interop.Excel._Workbook)(xl.Workbooks.Add(Missing.Value));
            Microsoft.Office.Interop.Excel._Worksheet sheet =
                (Microsoft.Office.Interop.Excel._Worksheet)wb.ActiveSheet;
            Microsoft.Office.Interop.Excel.Range destination = 
                (Microsoft.Office.Interop.Excel.Range)sheet.Cells[1, 1];
            Microsoft.Office.Interop.Excel.QueryTable qt =
            sheet.QueryTables.Add("OLEDB;" + connString, destination, query);
            qt.Refresh(Missing.Value);
            wb.SaveAs(XLSFileName, 
                Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal,
                null, null, false, false, 
                Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlShared,
                false, false, null, null, null);
            wb.Close(true, null, null);
            xl.Workbooks.Close();
            xl.Quit();
            sheet = null;
            wb = null;
            xl = null;
            GC.Collect();
        }
    }
    • Marked as answer by Riquel_Dong Wednesday, June 17, 2009 1:24 AM
    Friday, June 12, 2009 1:18 PM

All replies

  • Suppose I have a file in 'C'-Drive called "Book1.xls" and sheet name is "sheet1". Now from your DataTable 'dsFill'
    column-0 is send to Book1.xls file.

    Dim
    oExcel As Object = CreateObject("Excel.Application")

    Dim oBook As Object = oExcel.Workbooks.Open("C:\Book1.xls")

    Dim oSheet As Object = oBook.Worksheets(1) 'or oBook.Worksheets("SheetName")

    For yx As Integer = 0 To dsFill.Rows.Count - 1

        oSheet.Range("A" & (yx + 1)).value = dsFill.Rows(yx).Item(0).ToString

    Next

    oBook.SaveAs("C:\Book1.xls", True)

    oExcel.Quit()

    For Your Title : You may check by change your field type as “Float” – [Instead of Numeric]

     

    • Edited by Paramu Thursday, June 11, 2009 5:54 AM
    Wednesday, June 10, 2009 4:03 PM
  • You can create and run a macro (or automate from C#).

    ActiveSheet.QueryTables.Add( _
        Connection:="OLEDB;Provider=VFPOLEDB;Data Source=C:\YourFolderPath", _
        Destination:=Range("A1"), _
        Sql:="select * from myTable").Refresh

    • Proposed as answer by CetinBasoz Thursday, June 11, 2009 6:10 PM
    • Marked as answer by Riquel_Dong Wednesday, June 17, 2009 1:24 AM
    Wednesday, June 10, 2009 4:52 PM
  • Hi  CentiBasoz,

    my .dbf file is created successfully but my problem is that after creation of that file with vfpoledb (VFP 9) drivers i am not able to open that file in any of the Excel version.

    i am trying to open dbf manually in excel by right click it.

    so if you know how to solve this problem please let me know about it
    Thursday, June 11, 2009 7:00 AM
  • Rushit,

    Please do not post questons here then direct email me with the same questions.

    The answer is, Microsoft changed the format of the dbf file so that new features could be added. Excel only recognizes the old format. But, Excel can use the VFP OLE DB Provider to open the table.
    Craig Berntson MCSD, Visual FoxPro MVP www.craigberntson.com
    • Proposed as answer by Craig Berntson Thursday, June 11, 2009 1:34 PM
    • Marked as answer by Riquel_Dong Wednesday, June 17, 2009 1:24 AM
    Thursday, June 11, 2009 1:34 PM
  • Hi  CentiBasoz,

    my .dbf file is created successfully but my problem is that after creation of that file with vfpoledb (VFP 9) drivers i am not able to open that file in any of the Excel version.

    i am trying to open dbf manually in excel by right click it.

    so if you know how to solve this problem please let me know about it

    My name is Cetin not Centi.

    I can open any version of VFP table in any version of Excel with the sample I sent. IOW you need to use VFPOLEDB driver. Manually go to macros and paste that code, edit for your path/tablename:)
    You can also use Import external data menu option and specify udl as source, follow steps.
    • Proposed as answer by CetinBasoz Thursday, June 11, 2009 6:13 PM
    Thursday, June 11, 2009 6:13 PM
  • Hi Sorry for Wrong Pronounce your name Cetin

    my question is how can i open dbf file which is created using VFPOLEDB in Excel2003/Open Office 3.0/DBF Viewer ?

    Can you please tell me that ?

    Right now i am using VFP 9.0 and i dont know how to open it. when i try to right click on .dbf file and open it into excel it gives me error that not proper format.

    Mircosoft/ Open Office 3.0/ DBF Viewer says that they support VFP 9.0 so can you please help me
    Friday, June 12, 2009 6:23 AM
  •  Hi Craig ,

    Microsoft/ Open Office 3.0/ DBF Viewer says that they support VFP 9.0. We just have to change Heder of that .dbf files.
    we are creating application with C# and we dont find any help regarding it so if you have any idea then please let me know about it.
    Friday, June 12, 2009 6:46 AM
  • As I understand you are new to development. Is that your school assignment? Even in that case you should try to read and understand the replies. Anyway here is some code that does it via autmation:

    using System;
    using System.Data;
    using System.Data.OleDb;
    using System.Runtime.InteropServices;
    using Microsoft.Office.Interop;
    using System.Reflection;
    
    
    class Sample
    {
        static void Main()
        {
            Sample s = new Sample();
            s.VFPSample("MyVFPTable");
        }
    
        public void VFPSample(string tableName)
        {
            string connString = "Provider=vfpoledb; Data Source=c:\\temp";
            OleDbConnection conn = new OleDbConnection(connString);
    
            string createStatement = String.Format("Create Table {0} " +
                "(USR_TXN_NO n(10,0), UNITS n(19,8), DOB d, ECS_NO n(9,0))",
                tableName);
            conn.Open();
            OleDbCommand cmdCreate = new OleDbCommand(createStatement, conn);
            cmdCreate.ExecuteNonQuery();
    
            OleDbCommand cmd = new OleDbCommand(
                String.Format("insert into {0} values (?,?,?,?)", tableName),
                conn);
            cmd.Parameters.AddWithValue("p1", 1);
            cmd.Parameters.AddWithValue("p2", 10);
            cmd.Parameters.AddWithValue("p3", DateTime.Now.AddDays(-100));
            cmd.Parameters.AddWithValue("p4", 5);
            cmd.ExecuteNonQuery();
            for (int sample = 2; sample <= 10; sample++)
            {
                cmd.Parameters[0].Value = sample;
                cmd.Parameters[1].Value = sample * 10;
                cmd.Parameters[2].Value = DateTime.Now.AddDays(-sample);
                cmd.Parameters[3].Value = sample * 5;
                cmd.ExecuteNonQuery();
            }
            conn.Close();
    
            string XLSFileName = @"c:\temp\VFP9Table.xls";
            string query = String.Format("select * from {0}", tableName);
    
            Microsoft.Office.Interop.Excel._Application xl = 
                new Microsoft.Office.Interop.Excel.Application();
            Microsoft.Office.Interop.Excel._Workbook wb =
                (Microsoft.Office.Interop.Excel._Workbook)(xl.Workbooks.Add(Missing.Value));
            Microsoft.Office.Interop.Excel._Worksheet sheet =
                (Microsoft.Office.Interop.Excel._Worksheet)wb.ActiveSheet;
            Microsoft.Office.Interop.Excel.Range destination = 
                (Microsoft.Office.Interop.Excel.Range)sheet.Cells[1, 1];
            Microsoft.Office.Interop.Excel.QueryTable qt =
            sheet.QueryTables.Add("OLEDB;" + connString, destination, query);
            qt.Refresh(Missing.Value);
            wb.SaveAs(XLSFileName, 
                Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal,
                null, null, false, false, 
                Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlShared,
                false, false, null, null, null);
            wb.Close(true, null, null);
            xl.Workbooks.Close();
            xl.Quit();
            sheet = null;
            wb = null;
            xl = null;
            GC.Collect();
        }
    }
    • Marked as answer by Riquel_Dong Wednesday, June 17, 2009 1:24 AM
    Friday, June 12, 2009 1:18 PM
  • Thnaks Cetin,

    But you are providing solution which i never asked you.

    I found error when i try to open file by Double Click on File nothing else i was ask you. I used so many time a code which you mentioned here.

    but its k fine my problem with DBF Viewer and with Office ORG is solved but still i am facing problem with VFP 9.0 and Excel.
    Friday, June 12, 2009 2:05 PM
  • Why don't you tell it from the start! DoubleClick opening is related with file associations. Install VFP9 and relate dbf with VFP "NOT EXCEL". VFP9 tables are not excel files!
    Friday, June 12, 2009 4:17 PM
  • Dear Cetin,

    Yes i know that VFP Tables are not Excel Files. But when we right click on .DBF Files(VFP tables) and browse it to open with Excel it will open with excel.

    That done with all files which i have created with Microsoft.Jet.OLEDB 4.0. but the same thing is not working when i create files with VFPOLEDB provider of VFP 9.0

    With my research i have come to know that  while we create .DBF files with C# and VBFOLEDB we face this problem and solutions for that we have to change or made some modification in Header of the files then it will be possible to open with Excel.

    But i dont get any help how to change the header of dbf files with c#.

    I am aware of Files which are opened in Excel and in VFP 9.0 and also aware of .NET Technology in C#.

    Saturday, June 13, 2009 5:23 AM
  • Dear Cetin,

    Yes i know that VFP Tables are not Excel Files. But when we right click on .DBF Files(VFP tables) and browse it to open with Excel it will open with excel.

    That done with all files which i have created with Microsoft.Jet.OLEDB 4.0. but the same thing is not working when i create files with VFPOLEDB provider of VFP 9.0

    With my research i have come to know that  while we create .DBF files with C# and VBFOLEDB we face this problem and solutions for that we have to change or made some modification in Header of the files then it will be possible to open with Excel.

    But i dont get any help how to change the header of dbf files with c#.

    I am aware of Files which are opened in Excel and in VFP 9.0 and also aware of .NET Technology in C#.


    Files you create with Microsoft.Jet.OLEDB 4.0 are NOT VFP9 tables. Just having ythe same extension DBF doesn't make a file VFP9 compatible. Why don't you simply create the file once using Jet, and then update from VFPOLEDB? VFPOLEDB can do that.

    If you directly create using VFPOLEDB then it creates a "VFP9" table which again is NOT an Excel or Jet compatible file.

    Yes you might change the header. You don't get any help how to change the header of dbf files with C#??? Strange, DBF header is fully documented:

    http://msdn.microsoft.com/en-us/library/st4a0s68(VS.80).aspx

    http://msdn.microsoft.com/en-us/library/st4a0s68(VS.71).aspx

    In C# it is easy to update a binary file. 
    Saturday, June 13, 2009 9:32 PM