How to Read/Write Data from DBF file using C#.net application.
Overview:
This document covers the following
Windows 2000, XP and above
Language
C#.NET and ASP.NET, Visual Studio 2003 and above
Overview:
This document is useful to create import export utility for DBF file. Some time it does require import/export data from DBF file from your DOTNET application. Currently I am working on same kind of requirement where I have developed Export Import Utility it's include export/import data from various file type. One of toughest type is DBF file import export, I did some research on that and got one reboots solution that I want to share with you all.
This document covers the following
1) How to create DBF file database table
2) How to Insert data in created DBF file
3) How to Read data from DBF file.
Introduction:
DBF is a FoxPro database file, this we can create from C#.net application even we can use basic DDL and DML statement on this like 'Create table' , 'Insert table' etc.
If our requirement is to export your data which in is the Dataset (in C#.net) in DBF file this document will help you. You can also read data from any DBF file using this document. Using the same function with different connection string you can use to read / write excel file too.
How function works.
1) I have created one function for Export DBF file; this will takes one dataset as an input parameter
2) Based on input dataset parameter I am creating the database table using 'create table' statement.
3) Then I open the connection using 'Microsoft.Jet.OLEDB.4.0' provider.
4) Using OleDbDataAdapter object I am filling the dataset (dsFill) this will create one DBF table structure with given table name.
5) Now using the Same connection you can fire the next DML statement 'Insert into ..' to insert the data into the DBF file.
6) And same way you can read the data using 'Select..' statement. Fro Import.
Some points are important here
1) DBF file creates with Table name (TEMP.dbf) given in the 'Create table TEMP...' statement.
2) If you are reading the DBF file using 'Select ...' statement the select statement should be like 'Select * from TEMP'.
3) Key words are not acceptable in the DDL or DML statement. Like table, date etc.
4) This will take data source as folder name where DBF file exist like If you want to export / Import DBF file from "C:\Temp\country.dbf." then your Data source will be "C:\Temp" and Table name will be "country".
Source Code:
Follow the simple steps to create application. You can simply copy and past code from here in your application
1) Create the function which will give you the folder path and table name
private void GetFileNameAndPath(string completePath, ref string fileName, ref string folderPath)
{
string[] fileSep = completePath.Split('\\');
for (int iCount = 0; iCount < fileSep.Length; iCount++)
{
if (iCount == fileSep.Length - 2)
{
if (fileSep.Length == 2)
{
folderPath += fileSep[iCount] + "\\";
}
else
{
folderPath += fileSep[iCount];
}
}
else
{
if (fileSep[iCount].IndexOf(".") > 0)
{
fileName = fileSep[iCount];
fileName = fileName.Substring(0, fileName.IndexOf("."));
}
else
{
folderPath += fileSep[iCount] + "\\";
}
}
}
}
Note: Use the folderPath and DataSource in the connection string and fileName in the 'Create tabel' statement.
1) Create ExportDBF function.
// This function takes Dataset (to be exported) and filePath as input parameter and return // bool status as output parameter
// comments are written inside the function to describe the functionality
public bool EportDBF(DataSet dsExport, string filePath)
{
string tableName = string.Empty;
string folderPath = string.Empty;
bool returnStatus = false;
// This function give the Folder name and table name to use in
// the connection string and create table statement.
GetFileNameAndPath(filePath, ref tableName, ref folderPath);
// here you can use DBASE IV also
string connString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + folderPath + "; Extended Properties=DBASE III;";
string createStatement = "Create Table " + tableName + " ( ";
string insertStatement = "Insert Into " + tableName + " Values ( ";
string insertTemp = string.Empty;
OleDbCommand cmd = new OleDbCommand();
OleDbConnection conn = new OleDbConnection(connString);
if (dsExport.Tables[0].Columns.Count <= 0) { throw new Exception(); }
// This for loop to create "Create table statement" for DBF
// Here I am creating varchar(250) datatype for all column.
// for formatting If you don't have to format data before
// export then you can make a clone of dsExport data and transfer // data in to that no need to add datatable, datarow and
// datacolumn in the code.
for (int iCol = 0; iCol < dsExport.Tables[0].Columns.Count; iCol++)
{
createStatement += dsExport.Tables[0].Columns[iCol].ColumnName.ToString();
if (iCol == dsExport.Tables[0].Columns.Count - 1)
{
createStatement += " varchar(250) )";
}
else
{
createStatement += " varchar(250), ";
}
}
//Create Temp Dateset
DataSet dsCreateTable = new DataSet();
//Open the connection
conn.Open();
//Create the DBF table
DataSet dsFill = new DataSet();
OleDbDataAdapter daInsertTable = new OleDbDataAdapter(createStatement, conn);
daInsertTable.Fill(dsFill);
//Adding One DataTable into the dsCreatedTable dataset
DataTable dt = new DataTable();
dsCreateTable.Tables.Add(dt);
for (int row = 0; row < dsExport.Tables[0].Rows.Count; row++)
{
insertTemp = insertStatement;
//Adding Rows to the dsCreatedTable dataset
DataRow dr = dsCreateTable.Tables[0].NewRow();
dsCreateTable.Tables[0].Rows.Add(dr);
for (int col = 0; col < dsExport.Tables[0].Columns.Count; col++)
{
if (row == 0)
{
//Adding Columns to the dsCreatedTable dataset
DataColumn dc = new DataColumn();
dsCreateTable.Tables[0].Columns.Add(dc);
}
// Remove Special character if any like dot,semicolon,colon,comma // etc
dsExport.Tables[0].Rows[row][col].ToString().Replace("LF", "");
// do the formating if you want like modify the Date symbol , //thousand saperator etc.
dsCreateTable.Tables[0].Rows[row][col] = dsExport.Tables[0].Rows[row][col].ToString().Trim();
} // inner for loop close
// Create Insert Statement
if (col == dsExport.Tables[0].Columns.Count - 1)
{
insertTemp += "'" + dsCreateTable.Tables[0].Rows[row][col] + "' ) ;";
}
else
{
insertTemp += "'" + dsCreateTable.Tables[0].Rows[row][col] + "' , ";
}
// This lines of code insert Row One by one to above created
// datatable.
daInsertTable = new OleDbDataAdapter(insertTemp, conn);
daInsertTable.Fill(dsFill);
} // close outer for loop
MessageBox.Show("Exported done Successfully to DBF File.");
returnStatus = true;
} // close function
Note: for formatting like date and thousand separators add below lines of code while transfer data from one dataset to another.
1) Char decimalSymbol = '/'; /. Or "."
dsCreateTable.Tables[0].Rows[row][col] = Convert.ToString(Convert.ToDecimal(dsExport.Tables[0].Rows[row][col].ToString().Trim())).Replace('.', decimalSymbol);
2) string thousandSeparator = "#" + "" + "###"; // or ","
dsCreateTable.Tables[0].Rows[row][col] = Convert.ToString(Convert.ToDouble(dsExport.Tables[0].Rows[row][col].ToString().Trim()).ToString(thousandSeparator));
3) string dateFormat ="MM/dd/yyyy";
dsCreateTable.Tables[0].Rows[row][col] = Convert.ToString(Convert.ToDateTime(dsExport.Tables[0].Rows[row][col].ToString().Trim()).ToString(dateFormat));
2) Create ImportDBF function.
// This function takes filePath as input parameter and return DataSet as output parameter
// comments are written inside the function to describe the functionality
public DataSet ImportDBF(string filePath)
{
string ImportDirPath = string.Empty;
string tableName = string.Empty;
// This function give the Folder name and table name to use in
// the connection string and create table statement.
GetFileNameAndPath(filePath, ref tableName, ref ImportDirPath);
DataSet dsImport = new DataSet();
string thousandSep = thousandSeparator;
string connString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + ImportDirPath + "; Extended Properties=DBASE IV;";
OleDbConnection conn = new OleDbConnection(connString);
DataSet dsGetData = new DataSet();
OleDbDataAdapter daGetTableData = new OleDbDataAdapter("Select * from " + tableName , conn);
// fill all the data in to dataset
daGetTableData.Fill(dsGetData);
DataTable dt = new DataTable(dsGetData.Tables[0].TableName.ToString());
dsImport.Tables.Add(dt);
// here I am copying get Dataset into another dataset because //before return the dataset I want to format the data like change //"datesymbol","thousand symbol" and date format as did while
// exporting. If you do not want to format the data then you can // directly return the dsGetData
for (int row = 0; row < dsGetData.Tables[0].Rows.Count; row++)
{
DataRow dr = dsImport.Tables[0].NewRow();
dsImport.Tables[0].Rows.Add(dr);
for (int col = 0; col < dsGetData.Tables[0].Columns.Count; col++)
{
if (row == 0)
{
DataColumn dc = new DataColumn(dsGetData.Tables[0].Columns[col].ColumnName.ToString());
dsImport.Tables[0].Columns.Add(dc);
}
if (!String.IsNullOrEmpty(dsGetData.Tables[0].Rows[row][col].
ToString()))
{
dsImport.Tables[0].Rows[row][col] = Convert.ToString(dsGetData.Tables[0].Rows[row][col].ToString().Trim());
}
} // close inner for loop
}// close ouer for loop
MessageBox.Show("Import done Successfully to DBF File.");
Return dsImport;
} // close function
Note: Same function you can use to Export Import from Excel file also
Use
string connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties=Excel 8.0";
// filePath = complete path like "C:\Temp\country.xls"
Import Excel
string strconn = "Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source=" + filePath + ";Extended Properties=Excel 8.0";
OleDbDataAdapter da = new OleDbDataAdapter("select * from [" + fileName + "$]", strconn);
DataSet dsExcel = new DataSet();
da.Fill(dsExcel);
Export Excel
Same as Export DBF file with different connection string.
string connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties=Excel 8.0";
// filePath = complete path like "C:\Temp\country.xls"
Windows 2000, XP and above
Language
C#.NET and ASP.NET, Visual Studio 2003 and above
16 comments:
Thanks Ritesh Kesharwani,
Your peice of code was very helpful
Thanks. This is exactly what I am looking for.
Just a quick note however, instead of writing GetFileNameAndPath, I suggest checking out the System.IO.Path methods which do the same thing.
For example:
string tableName =
Path.GetFileNameWithoutExtension(filePath);
string folderPath = Path.GetDirectoryName(filePath);
Thanks, this really helped me.
It was exactly what I was looking for.
Again thanks for sharing your knowledge.
thanks dude
Thanks Ritesh Kesharwani
for such a useful article
i have some pblm in generating dbf file from c#.net with dbf IV oledb provider.
when i try to Crete a numeric field of (25,8) length it show error as max lenght is 20 for numeric field.
but our client want field of (25,8) width only.
can u tell me is it possible any way to create a numeric field in dbf file with (25,8) width.
do reply.
Hi, I am trying to use this code to write to an excel file. The function fails to write into excel file. Let me know if you know of things I can look for.
thanks,
--Sarath.
Here is the code I am using some of the fields are hardcoded for testing..
public static void CreateWorkbook(DataSet reportData, String fileNameAndPath)
{
fileNameAndPath = "c:\\temp\\sample.xlsx";
string tableName = "bulk_load";
string folderPath = string.Empty;
bool returnStatus = false;
// This function give the Folder name and table name to use in
// the connection string and create table statement.
//GetFileNameAndPath(filePath, ref tableName, ref folderPath); --todo check if the file already exists
// here you can use DBASE IV also
string connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileNameAndPath +
";Extended Properties=Excel 12.0 Xml";
string createStatement = "Create Table " + tableName + " ( ";
string insertStatement = "Insert Into " + tableName + " Values ( ";
string insertTemp = string.Empty;
OleDbCommand cmd = new OleDbCommand();
OleDbConnection conn = new OleDbConnection(connString);
if (reportData.Tables[0].Columns.Count <= 0) { throw new Exception(); }
// This for loop to create "Create table statement" for DBF
// Here I am creating varchar(250) datatype for all column.
// for formatting If you don't have to format data before
// export then you can make a clone of dsExport data and transfer
// data in to that no need to add datatable, datarow and
// datacolumn in the code.
for (int iCol = 0; iCol < reportData.Tables[0].Columns.Count; iCol++)
{
createStatement += reportData.Tables[0].Columns[iCol].ColumnName.ToString();
if (iCol == reportData.Tables[0].Columns.Count - 1)
{
createStatement += " varchar(250) )";
}
else
{
createStatement += " varchar(250), ";
}
}
//Create Temp Dateset
DataSet dsCreateTable = new DataSet();
//Open the connection
conn.Open();
//Create the DBF table
DataSet dsFill = new DataSet();
OleDbDataAdapter daInsertTable = new OleDbDataAdapter(createStatement, conn);
daInsertTable.Fill(dsFill);
//Adding One DataTable into the dsCreatedTable dataset
DataTable dt = new DataTable();
dsCreateTable.Tables.Add(dt);
for (int row = 0; row < reportData.Tables[0].Rows.Count; row++)
{
insertTemp = insertStatement;
//Adding Rows to the dsCreatedTable dataset
DataRow dr = dsCreateTable.Tables[0].NewRow();
dsCreateTable.Tables[0].Rows.Add(dr);
for (int col = 0; col < reportData.Tables[0].Columns.Count; col++)
{
if (row == 0)
{
//Adding Columns to the dsCreatedTable dataset
DataColumn dc = new DataColumn();
dsCreateTable.Tables[0].Columns.Add(dc);
}
// Remove Special character if any like dot,semicolon,colon,comma // etc
reportData.Tables[0].Rows[row][col].ToString().Replace("LF", "");
// do the formating if you want like modify the Date symbol , //thousand saperator etc.
dsCreateTable.Tables[0].Rows[row][col] = reportData.Tables[0].Rows[row][col].ToString().Trim();
} // inner for loop close
// Create Insert Statement
for (int col = 0; col < reportData.Tables[0].Columns.Count; col++)
{
if (col == reportData.Tables[0].Columns.Count - 1)
{
insertTemp += "'" + dsCreateTable.Tables[0].Rows[row][col] + "' ) ;";
}
else
{
insertTemp += "'" + dsCreateTable.Tables[0].Rows[row][col] + "' , ";
}
}
// This lines of code insert Row One by one to above created
// datatable.
daInsertTable = new OleDbDataAdapter(insertTemp, conn);
daInsertTable.Fill(dsFill);
} // close outer for loop
//MessageBox.Show("Exported done Successfully to DBF File.");
returnStatus = true;
}
Hi Ritesh,
Tried your code, inserting mutiple records in a single dbf file failed telling me "Characters found after end of SQL statement. ", Access does not multiple insert in one sql statements??, is there another way around this? Thanks
how can i convert a dbf file to sql server 2005 database file using C# ?
can anybody help me with the code
thank you
Perhaps you could post this question on DoMyHomeworkForMe.com
The logic is simple.
1. Open DBF connection
2. Load DBF into DataSet
3. Open SQL connection
4. Write DataSet into SQL
I've also had problems getting writing multiple lines to a DBF file. I've stumbled upon numerous sites that have documented that it is not possible, which supports my hours of trying every possible combination.
Thanks, I modify it to take into account the data type :
// This for loop to create "Create table statement" for DBF
// Here I am creating varchar(250) datatype for all column.
// for formatting If you don't have to format data before
// export then you can make a clone of dsExport data and transfer // data in to that no need to add datatable, datarow and
// datacolumn in the code.
for (int iCol = 0; iCol < dsExport.Tables[0].Columns.Count; iCol++)
{
createStatement += dsExport.Tables[0].Columns[iCol].ColumnName.ToString();
switch (Type.GetTypeCode(dsExport.Tables[0].Columns[iCol].DataType))
{
case TypeCode.Decimal:
createStatement += " numeric(10,2)";
break;
case TypeCode.String:
createStatement += " varchar(40)";
break;
case TypeCode.DateTime:
createStatement += " date";
break;
}
if (iCol != dsExport.Tables[0].Columns.Count - 1)
createStatement += ",";
}
createStatement += ")";
Hi Ritesh, I need to create a DBF file , but I do not have DBASE in my system.Is it possible to create a DBF file?
Thanks in Advance,
Jaimie
I'm Getting a error while exporting a dataset to dbf file.
Error: The field is to small to accept the amount of data you attempted to add.Try inserting or pasting less data.
Mohit
It will be difficult to comment on your post without some context as to information you're writing. Also, the bulk of posts on this thread are now 7 years old, so you might have to set your expectations of a reply accordingly.
Finger's crossed.
Post a Comment