Create .DBF file from SQL table records
Asked Answered
S

4

8

I want to create a .DBF file from SQL table records.

Such as if there is a table named CountryMaster in SQL and it has 5 columns:

  1. ID int identity(1,1)
  2. Name varchar(100)
  3. Details varchar(200)
  4. Status bit
  5. CreatedDate datetime

And it has 100 rows.

How can I export these records with headers in .DBF file from C#?

NOTE: Created .DBF file size must be very compact.

Selfaddressed answered 15/9, 2015 at 5:19 Comment(5)
I guess this might answer your question #323292Paludal
Which RDBMS is this for? Please add a tag to specify whether you're using mysql, postgresql, sql-server, oracle or db2 - or something else entirely.Penholder
So you want to export data from SQL Server to a dBase, Clipper or Foxpro file (which one)? You shouldn't need to use C# for this at all, have you tried Integration Services or the Export Data Wizard in SSMS? The wizard actually creates an SSIS package which you can edit to suit your needs. You can even use the same providers, connection strings in your C# code if you wantCata
@PanagiotisKanavos deleted answer as it was using a tool.OP can follow example at github.com/SocialExplorer/FastDBF/blob/master/TestDbfLib/…Stephainestephan
@Penholder what is your idea on his NOTE which say: dbf file size must be very compact.Barnabe
B
12

You can see the Xbase Data file (*.dbf) structure and write your own code but I have done the implementation and have been using it for years. Here you can find it on GitHub


How to use the library

There are some write methods in a file named DbfFile.cs. You may use any of them. I will explain some of them:

The First Write Method

Save a DataTable as dbf file:

static void Write(string fileName, System.Data.DataTable table, Encoding encoding)
  • fileName: is the location which you want the .dbf output file be saved.
  • table: is your data which you have read from the SQL Server or any other source.
  • encoding: the encoding to be used when saving the string data

The Second Write Method

Save a List<T> into a dbf file.

static void Write<T>(string fileName,
                                    List<T> values,
                                    List<Func<T, object>> mapping,
                                    List<DbfFieldDescriptor> columns,
                                    Encoding encoding)

Read the database and save the result into some class type then save class value to dbf file using this method. Here is description of it's parameters:

  • fileName: the dbf file name to be saved
  • values: Your data as a List of objects of type T to be saved into a dbf file
  • mapping: A list of functions that tell this method how to retrieve data from the class type.
  • columns: dbf column information
  • encoding: the encoding of the dbf file.

Example for the Second Write Method

As the first approach is straight forward, I provide you with and example on the second write method. Consider you want to save a List<MyClass> data into a dbf file. Here is the code

class MyClass
{
    public int Id {get;set;}
    public string Name {get;set;}
}

Now you can save a List<MyClass> into a dbf file like this:

var idColumn = DbfFieldDescriptors.GetIntegerField("Id");
var nameColumn = DbfFieldDescriptors.GetStringField("Name");
var columns = new List<DbfFieldDescriptor>() { idColumn, nameColumn };

Func<MyClass, object> mapId = myClass => myClass.Id;
Func<MyClass, object> mapName = myClass => myClass.Name;
var mapping = new List<Func<MyClass, object>>() { mapId, mapName };

List<MyClass> values = new List<MyClass>();
values.Add(new MyClass() { Id = 1, Name = "name1" });

DbfFileFormat.Write(@"C:\yourFile.dbf", values, mapping, columns, Encoding.ASCII);

Also using this library you can read dbf files and your code do not depend on Microsoft.Jet.OLEDB or anything else.

enjoy it.

Barnabe answered 17/9, 2015 at 15:52 Comment(17)
I am getting error in first method "The type or namespace name 'IO' does not exist in the namespace 'IRI.Ket' (are you missing an assembly reference?)"Selfaddressed
@Selfaddressed I update the GitHub source. Let me know if you have any other problemBarnabe
I have added IRI.Ket dll but still i am getting an error System.IO.File.WriteAllText(Shapefile.GetCpgFileName(fileName), encoding.BodyName); (Shapefile does not exists)Selfaddressed
there is error : Method not found: 'Int32 System.Runtime.InteropServices.Marshal.SizeOf(IRI.Ket.ShapefileFormat.Dbf.DbfHeader)'. Would you please build a .net 4.5 project with my sample code DataTable dt =new DataTable(); dt.Columns.Add("Id"); dt.Columns.Add("Name"); dt.Rows.Add("1","Chirag"); DbfFile.Write(@"D:\Chirag\dBase\WPTPSLST.DBF", dt, Encoding.UTF8);Selfaddressed
I will not downvote, but I would definitely hesitate to use a random GitHub library by an unknown programmer when it has errors like these.Jarita
@Selfaddressed I update a complete project with an extra test projectBarnabe
Your code works in testing, but when i passing actual data (Table records contains 10,000 rows) it gives error 'The output byte buffer is too small to contain the encoded data, encoding 'Unicode (UTF-8)' fallback 'System.Text.EncoderReplacementFallback'. Parameter name: bytes'. but file created with (7104 reocords SIZE : 18.9 MB).Selfaddressed
I have already made a project which is not giving any error, and create a file with 10,000 records with size of 13 MB, but i want to reduce it. But I really appreciate your help. thank you very much @HosseinNarimaniRadSelfaddressed
@Selfaddressed You cannot use UTF-8 encoding because the specification of the dbf file tell us there is only two byte space for any character. So remember if you use the UTF-8 and you have non-English characters you may loss some data. Have a look at ANSI encoding and find the one which fit your needsBarnabe
I have tried with Encoding.ASCII and Encoding.Default but the result is same.Selfaddressed
@Selfaddressed It was just a technical point, not a fix. I haven't encounter with such problem and as far as I cannot reproduce your error I may not be able to fix it. Anyway if you fix it, you may want to update the GitHub project :)Barnabe
@Selfaddressed just one more point. In the MakeDbfFields method I'm using 100 as the default length for string columns, you may change it to the max value which is 255. Another limitation of the dbf file is that string columns cannot go beyond 255 length limit. This may fix your problem. I update the GitHub.Barnabe
Yes. I have already changed that to 200 and error solved, but the file size is 54 MB (too large). what you say if i want to reduce it to less than 10 MB?Selfaddressed
The Field size is fixed for every row and I believe there is no way to change the dbf file size with a given data.Barnabe
@HosseinNarimaniRad i want an answer who can reduce dbf file size.Selfaddressed
@Selfaddressed dbf file size is a function of number of the records and the number and the size of its fields (columns). The length of the string columns can be changed (range from 1 to 255) and the resulting file size will also be changed. BUT with a given dataset which may contain a 200-length string you have to set that column's length to 200.Barnabe
Anyway if you are looking for an answer who can reduce dbf file size (however technically it has no meaning with a given dataset), consider editing your question and mention that point in your question. Anyway I recommend you looking for other formats and forget the .dbf format.Barnabe
S
1

Mostly all of the information you need can be seen at Trouble with Insert Into .dbf file, because it shows how to create a table and insert values into it while creating a .dbf file. You will need to make some modifications for the fields you have specified, but the page describes everything you need.

Shirleenshirlene answered 18/9, 2015 at 4:15 Comment(3)
Whoever voted me down: I offered just as much information as the OP did. The OP did not even show any work they had tried. Requests for spoon-feeding are best answered with direction on enabling the requester to think, communicate, and involve themselves more.Shirleenshirlene
I didn't down vote, but as you see the answer is edited and it seems he is looking for a completely different problem, so you may want to edit your answer if you can help and believe what he want (compacting a dbf file with a given dataset!) can be accomplished.Barnabe
@Chirag, I added the ability to export out the results as CSV file too which automatically trims the data and includes the column headers.Purgatorial
H
0

Id like to help. However, id stick with the plain process with OLEDB for VPF.

CREATE TABLE DBF

This could be your create table script for .DBF

CREATE TABLE "C:\test.dbf" ([ID] Numeric(18,0), [Name] Char(100) NULL, [Details] Char(200) NULL, [Status] Logical NULL, [CreateDate] DATETIME NULL)

Then, you can call this one from an OLE DB script like as used below.

    string vpfScript = "CREATE TABLE \"C:\test.dbf\" ([ID] Numeric(18,0), [Name] Char(100) NULL, [Details] Char(200) NULL, [Status] Logical NULL, [CreateDate] DATETIME NULL)";
    string connectionString = @"Provider=VFPOLEDB.1;Data Source=C:\test.dbf";
    OleDbConnection connection = new OleDbConnection(connectionString);

    using (OleDbCommand scriptCommand = connection.CreateCommand())
    {
         connection.Open();
         scriptCommand.CommandType = CommandType.StoredProcedure;
         scriptCommand.CommandText = "ExecScript";
         scriptCommand.Parameters.Add("myScript", OleDbType.Char).Value = vfpScript;
         scriptCommand.ExecuteNonQuery();
    }

IMPORT ROW IN TABLE

To import rows in a DBF table, its not as far as the usual SQL scripts we do in other DB's.

    string vpfScript = "INSERT INTO \"C:\test.dbf\" ([ID], [Name], [Details], [Status], [CreateDate]) VALUES (1,'test john','test details',.t.,{^2015-09-15)";
    string connectionString = @"Provider=VFPOLEDB.1;Data Source=C:\test.dbf";
    OleDbConnection connection = new OleDbConnection(connectionString);

    using (OleDbCommand scriptCommand = connection.CreateCommand())
    {
        connection.Open();
        scriptCommand.CommandType = CommandType.StoredProcedure;
        scriptCommand.CommandText = "ExecScript";
        scriptCommand.Parameters.Add("myScript", OleDbType.Char).Value = vfpScript;
        scriptCommand.ExecuteNonQuery();
    }

You can just now change the values what fits your need, just like the usage below.

    DataTable dt = new DataTable(); // assuming this is already populated from your SQL Database.
    StringBuilder buildInsert = new StringBuilder();
    string connectionString = @"Provider=VFPOLEDB.1;Data Source=C:\test.dbf";
    OleDbConnection connection = new OleDbConnection(connectionString);

    using (OleDbCommand scriptCommand = connection.CreateCommand())
    {
         connection.Open();

         foreach(DataRow dr in dt.Rows)
         {
             string id = dr["ID"].ToString();
             string name = dr["Name"].ToString();
             string details = dr["Details"].ToString();
             string status = Convert.ToBoolean(dr["Status"]) ? ".t." : ".f.";
             string createDate = "{^" + Convert.ToDateTime(dr["CreateDate"]).ToString("yyyy-MM-dd") + "}";
             builderInsert.Append("INSERT INTO \"C:\test.dbf\" ([ID], [Name], [Details], [Status], [CreateDate]) VALUES (" + id + ",\"" + name + "\",\"" + details + "\"," + status + "," + createDate + ")" + Environment.NewLine);

             scriptCommand.CommandType = CommandType.StoredProcedure;
             scriptCommand.CommandText = "ExecScript";
             scriptCommand.Parameters.Add("myScript", OleDbType.Char).Value = builderInsert;
             scriptCommand.ExecuteNonQuery();
             builderInsert = "";
          }
     }

Please let me know if you have other concerns. Don't forget to install this one in your machine. VFP OLE DB

Haberdasher answered 20/9, 2015 at 6:17 Comment(2)
Although you have good intentions by using the VFP OleDB, you have issues (including SQL-Injection). The connection string should always point to a PATH, not an explicit table. From that, when you issue a CREATE TABLE or INSERT INTO, you just need to specify the table name and it will apply to the table in the PATH specified. You may not have read/write/modify permissions especially at the root C:\ level as your sample has. It COULD work, but needs adjustments and also needs an object to pull from SQL-Server and then insert into VFP table.Purgatorial
I agree with the security points. My intention here is just help the user export from any database to .dbf, assuming the user is not aware of OLE-DB usage with FoxPro scripts. This could have even made through SSIS or DTS 2000 to its simplest export form, but I'd rather explain things a little bit further through C# rather than drag-drop setup. thanks for your time!Haberdasher
P
0

To offer a C# solution for you I would start by downloading Microsoft Visual Foxpro OleDb Provider which works with .DBF tables.

at the top of your C# code, add

using System.Data.SqlClient;
using System.Data.OleDb;

Then, within a method to move the data

    void MoveFromSQLToDBF()
    {    
        // have a table to pull down your SQL Data...
        var dataFromSQL = new DataTable();

        // However your connection to your SQL-Server
        using (var sqlConn = new  SqlConnection("YourSQLConnectionString"))
        {
           using (var sqlCmd = new SqlCommand("", sqlConn))
           {
              // Get all records from your table
              sqlCmd.CommandText = "select * from CountryMaster";
              sqlConn.Open();
              var sqlDA = new SqlDataAdapter();
              // populate into a temp C# table
              sqlDA.Fill(dataFromSQL);
              sqlConn.Close();
           }
        }

        // Now, create a connection to VFP 
        // connect to a PATH where you want the data.
        using (var vfpConn = new OleDbConnection(@"Provider=VFPOLEDB.1;Data Source=C:\SomePathOnYourMachine\"))
        {
           using (var vfpCmd = new OleDbCommand("", vfpConn))
           {
              // Create table command for VFP
              vfpCmd.CommandText = "CREATE TABLE testFromSQL ( ID Numeric(18,0), [Name] Char(100) NULL, [Details] Char(200) NULL, [Status] Logical NULL, [CreateDate] DATETIME NULL)";

              vfpConn.Open();
              vfpCmd.ExecuteNonQuery();

              // Now, change the command to a SQL-Insert command, but PARAMETERIZE IT.
              // "?" is a place-holder for the data
              vfpCmd.CommandText = "insert into testFromSQL "
                 + "( ID, [Name], [Details], [Status], [CreateDate]) values ( ?, ?, ?, ?, ? )";

              // Parameters added in order of the INSERT command above.. 
              // SAMPLE values just to establish a basis of the column types
              vfpCmd.Parameters.Add( new OleDbParameter( "parmID", 10000000 ));
              vfpCmd.Parameters.Add( new OleDbParameter( "parmName", "sample string" ));
              vfpCmd.Parameters.Add(new OleDbParameter( "parmDetails", "sample string" ));
              vfpCmd.Parameters.Add(new OleDbParameter( "parmStatus", "sample string" ));
              vfpCmd.Parameters.Add( new OleDbParameter( "parmCreateDate", DateTime.Now ));

              // Now, for each row in the ORIGINAL SQL table, apply the insert to VFP
              foreach (DataRow dr in dataFromSQL.Rows)
              {
                 // set the parameters based on whatever current record is
                 vfpCmd.Parameters[0].Value = dr["ID"];
                 vfpCmd.Parameters[1].Value = dr["Name"];
                 vfpCmd.Parameters[2].Value = dr["Details"];
                 vfpCmd.Parameters[3].Value = dr["Status"];
                 vfpCmd.Parameters[4].Value = dr["CreateDate"];
                 // execute it
                 vfpCmd.ExecuteNonQuery();
              }

              // Finally, for compactness, use a VFP Script to copy to Excel (CSV) format
              using (var vfpCmd2 = new OleDbCommand("", vfpConn))
              {
                 vfpCmd2.CommandType = CommandType.StoredProcedure;
                 vfpCmd2.CommandText = "ExecScript";

                 vfpCmd2.Parameters.Add(new OleDbParameter( "csvScript", 
@"Use testFromSQL
copy to YourCompactFile.csv type csv
use" ));

                 vfpCmd2.ExecuteNonQuery();
              }

              // close VFP connection
              vfpConn.Close();

           }
        }
    }

Since the OleDb does not support copy TYPE CSV, I have found this post on S/O to dump into CSV format for you

Purgatorial answered 21/9, 2015 at 13:29 Comment(7)
He is not just looking for data transfer from SQL to dbf. He though it is possible to have compact dbf file so he is also looking for that!Barnabe
@HosseinNarimaniRad, this will EXACTLY create a simple, compact .DBF stand-alone file of those records queried from SQL. As for compact, getting to a CSV file would be about as compact (trimmed) as you would get.Purgatorial
I agree with you and already suggest him to forget about the dbf file, but he believe it is possible to reduce the dbf file size with a given number of records and columns and with some restriction on column sizes (however I told him this is not possible according to the dbf specification). For more explanation you may look at comments bellow my answer.Barnabe
@kenlacoste, yes, but parameterizing, pulling their data, AND dumping out to compact CSV format. The .DBF is not really required and could have been just dumped to CSV raw, but did not know their overall implementation needs.Purgatorial
sir i used your above code. I am getting error that feature is not available while using your code for compactnessPhenazine
@MuneemHabib, -- after looking around, I found that others tried and the COPY TYPE CSV is NOT a supported function native, you might have to do something via a stored procedure within a VFP database container, or other script code.Purgatorial
@MuneemHabib, revised answer... at bottom is link also on S/O to dump to CSV from datatable that might work for your needs.Purgatorial

© 2022 - 2024 — McMap. All rights reserved.