-
-
Save sleimanzublidi/27acd30b5d4b452d5ec6 to your computer and use it in GitHub Desktop.
using System; | |
using System.Collections.Generic; | |
using System.Data; | |
using System.Globalization; | |
using System.IO; | |
using System.Linq; | |
using System.Reflection; | |
using System.Runtime.InteropServices; | |
using System.Text; | |
namespace System.IO | |
{ | |
/// <summary> | |
/// This class reads a dbf files | |
/// </summary> | |
public class DBFReader : IDisposable | |
{ | |
private BinaryReader reader; | |
private Encoding encoding; | |
public DBFReader(Stream stream, Encoding encoding) | |
{ | |
this.encoding = encoding; | |
this.reader = new BinaryReader(stream, encoding); | |
ReadHeader(); | |
} | |
public DBFReader(string filename, Encoding encoding) | |
{ | |
if (File.Exists(filename) == false) | |
throw new FileNotFoundException(); | |
this.encoding = encoding; | |
var bs = new BufferedStream(File.OpenRead(filename)); | |
this.reader = new BinaryReader(bs, encoding); | |
ReadHeader(); | |
} | |
private void ReadHeader() | |
{ | |
byte[] buffer = reader.ReadBytes(Marshal.SizeOf(typeof(DBFHeader))); | |
// Marshall the header into a DBFHeader structure | |
GCHandle handle = GCHandle.Alloc(buffer, GCHandleType.Pinned); | |
this.header = (DBFHeader)Marshal.PtrToStructure(handle.AddrOfPinnedObject(), typeof(DBFHeader)); | |
handle.Free(); | |
fields = new List<DBFFieldDescriptor>(); | |
while (reader.PeekChar() != 13) | |
{ | |
buffer = reader.ReadBytes(Marshal.SizeOf(typeof(DBFFieldDescriptor))); | |
handle = GCHandle.Alloc(buffer, GCHandleType.Pinned); | |
var fieldDescriptor = (DBFFieldDescriptor)Marshal.PtrToStructure(handle.AddrOfPinnedObject(), typeof(DBFFieldDescriptor)); | |
if ((fieldDescriptor.Flags & DBFFieldFlags.System) != DBFFieldFlags.System ) | |
{ | |
fields.Add(fieldDescriptor); | |
} | |
handle.Free(); | |
} | |
byte headerTerminator = reader.ReadByte(); | |
byte[] backlink = reader.ReadBytes(263); | |
} | |
private void ReadRecords() | |
{ | |
records = new List<Dictionary<DBFFieldDescriptor, object>>(); | |
// Skip back to the end of the header. | |
reader.BaseStream.Seek(header.HeaderLenght, SeekOrigin.Begin); | |
for (int i = 0; i < header.NumberOfRecords; i++) | |
{ | |
if (reader.PeekChar() == '*') // DELETED | |
{ | |
continue; | |
} | |
var record = new Dictionary<DBFFieldDescriptor, object>(); | |
var row = reader.ReadBytes(header.RecordLenght); | |
foreach (var field in fields) | |
{ | |
byte[] buffer = new byte[field.FieldLength]; | |
Array.Copy(row, field.Address, buffer, 0, field.FieldLength); | |
string text = (encoding.GetString(buffer) ?? String.Empty).Trim(); | |
switch ((DBFFieldType)field.FieldType) | |
{ | |
case DBFFieldType.Character: | |
record[field] = text; | |
break; | |
case DBFFieldType.Currency: | |
if (String.IsNullOrWhiteSpace(text)) | |
{ | |
if ((field.Flags & DBFFieldFlags.AllowNullValues) == DBFFieldFlags.AllowNullValues) | |
{ | |
record[field] = null; | |
} | |
else | |
{ | |
record[field] = 0.0m; | |
} | |
} | |
else | |
{ | |
record[field] = Convert.ToDecimal(text); | |
} | |
break; | |
case DBFFieldType.Numeric: | |
case DBFFieldType.Float: | |
if (String.IsNullOrWhiteSpace(text)) | |
{ | |
if ((field.Flags & DBFFieldFlags.AllowNullValues) == DBFFieldFlags.AllowNullValues) | |
{ | |
record[field] = null; | |
} | |
else | |
{ | |
record[field] = 0.0f; | |
} | |
} | |
else | |
{ | |
record[field] = Convert.ToSingle(text); | |
} | |
break; | |
case DBFFieldType.Date: | |
if (String.IsNullOrWhiteSpace(text)) | |
{ | |
if ((field.Flags & DBFFieldFlags.AllowNullValues) == DBFFieldFlags.AllowNullValues) | |
{ | |
record[field] = null; | |
} | |
else | |
{ | |
record[field] = DateTime.MinValue; | |
} | |
} | |
else | |
{ | |
record[field] = DateTime.ParseExact(text, "yyyyMMdd", CultureInfo.InvariantCulture); | |
} | |
break; | |
case DBFFieldType.DateTime: | |
if (String.IsNullOrWhiteSpace(text) || BitConverter.ToInt64(buffer, 0) == 0) | |
{ | |
if ((field.Flags & DBFFieldFlags.AllowNullValues) == DBFFieldFlags.AllowNullValues) | |
{ | |
record[field] = null; | |
} | |
else | |
{ | |
record[field] = DateTime.MinValue; | |
} | |
} | |
else | |
{ | |
record[field] = JulianToDateTime(BitConverter.ToInt64(buffer, 0)); | |
} | |
break; | |
case DBFFieldType.Double: | |
if (String.IsNullOrWhiteSpace(text)) | |
{ | |
if ((field.Flags & DBFFieldFlags.AllowNullValues) == DBFFieldFlags.AllowNullValues) | |
{ | |
record[field] = null; | |
} | |
else | |
{ | |
record[field] = 0.0; | |
} | |
} | |
else | |
{ | |
record[field] = Convert.ToDouble(text); | |
} | |
break; | |
case DBFFieldType.Integer: | |
if (String.IsNullOrWhiteSpace(text)) | |
{ | |
if ((field.Flags & DBFFieldFlags.AllowNullValues) == DBFFieldFlags.AllowNullValues) | |
{ | |
record[field] = null; | |
} | |
else | |
{ | |
record[field] = 0; | |
} | |
} | |
else | |
{ | |
record[field] = BitConverter.ToInt32(buffer, 0); | |
} | |
break; | |
case DBFFieldType.Logical: | |
if (String.IsNullOrWhiteSpace(text)) | |
{ | |
if ((field.Flags & DBFFieldFlags.AllowNullValues) == DBFFieldFlags.AllowNullValues) | |
{ | |
record[field] = null; | |
} | |
else | |
{ | |
record[field] = false; | |
} | |
} | |
else | |
{ | |
record[field] = (buffer[0] == 'Y' || buffer[0] == 'T'); | |
} | |
break; | |
case DBFFieldType.Memo: | |
case DBFFieldType.General: | |
case DBFFieldType.Picture: | |
default: | |
record[field] = buffer; | |
break; | |
} | |
} | |
records.Add(record); | |
} | |
} | |
public DataTable ReadToDataTable() | |
{ | |
ReadRecords(); | |
var table = new DataTable(); | |
// Columns | |
foreach (var field in fields) | |
{ | |
var colType = ToDbType(field.FieldType); | |
var column = new DataColumn(field.FieldName, colType ?? typeof(String)); | |
table.Columns.Add(column); | |
} | |
// Rows | |
foreach (var record in records) | |
{ | |
var row = table.NewRow(); | |
foreach (var column in record.Keys) | |
{ | |
row[column.FieldName] = record[column] ?? DBNull.Value; | |
} | |
table.Rows.Add(row); | |
} | |
return table; | |
} | |
public IEnumerable<Dictionary<string, object>> ReadToDictionary() | |
{ | |
ReadRecords(); | |
return records.Select(record => record.ToDictionary(r => r.Key.FieldName, r => r.Value)).ToList(); | |
} | |
public IEnumerable<T> ReadToObject<T>() | |
where T : new() | |
{ | |
ReadRecords(); | |
var type = typeof(T); | |
var list = new List<T>(); | |
foreach (var record in records) | |
{ | |
T item = new T(); | |
foreach (var pair in record.Select(s => new { Key = s.Key.FieldName, Value = s.Value })) | |
{ | |
var property = type.GetProperty(pair.Key, BindingFlags.IgnoreCase | BindingFlags.Public | BindingFlags.Instance); | |
if (property != null) | |
{ | |
if (property.PropertyType == pair.Value.GetType()) | |
{ | |
property.SetValue(item, pair.Value, null); | |
} | |
else | |
{ | |
if (pair.Value != DBNull.Value) | |
{ | |
property.SetValue(item, System.Convert.ChangeType(pair.Value, property.PropertyType), null); | |
} | |
} | |
} | |
} | |
list.Add(item); | |
} | |
return list; | |
} | |
private DBFHeader header; | |
private List<DBFFieldDescriptor> fields = new List<DBFFieldDescriptor>(); | |
private List<Dictionary<DBFFieldDescriptor, object>> records = new List<Dictionary<DBFFieldDescriptor,object>>(); | |
#region IDisposable | |
public void Dispose() | |
{ | |
Dispose(true); | |
GC.SuppressFinalize(this); | |
} | |
protected void Dispose(bool disposing) | |
{ | |
if (disposing == false) return; | |
if (reader != null) | |
{ | |
reader.Close(); | |
reader.Dispose(); | |
reader = null; | |
} | |
} | |
~DBFReader() | |
{ | |
Dispose(false); | |
} | |
#endregion | |
/// <summary> | |
/// Convert a Julian Date as long to a .NET DateTime structure | |
/// Implemented from pseudo code at http://en.wikipedia.org/wiki/Julian_day | |
/// </summary> | |
/// <param name="julianDateAsLong">Julian Date to convert (days since 01/01/4713 BC)</param> | |
/// <returns>DateTime</returns> | |
private static DateTime JulianToDateTime(long julianDateAsLong) | |
{ | |
if (julianDateAsLong == 0) return DateTime.MinValue; | |
double p = Convert.ToDouble(julianDateAsLong); | |
double s1 = p + 68569; | |
double n = Math.Floor(4 * s1 / 146097); | |
double s2 = s1 - Math.Floor(((146097 * n) + 3) / 4); | |
double i = Math.Floor(4000 * (s2 + 1) / 1461001); | |
double s3 = s2 - Math.Floor(1461 * i / 4) + 31; | |
double q = Math.Floor(80 * s3 / 2447); | |
double d = s3 - Math.Floor(2447 * q / 80); | |
double s4 = Math.Floor(q / 11); | |
double m = q + 2 - (12 * s4); | |
double j = (100 * (n - 49)) + i + s4; | |
return new DateTime(Convert.ToInt32(j), Convert.ToInt32(m), Convert.ToInt32(d)); | |
} | |
/// <summary> | |
/// This is the file header for a DBF. We do this special layout with everything | |
/// packed so we can read straight from disk into the structure to populate it | |
/// </summary> | |
[StructLayout(LayoutKind.Sequential, CharSet = CharSet.Ansi, Pack = 1)] | |
private struct DBFHeader | |
{ | |
/// <summary>The version.</summary> | |
public readonly DBFVersion Version; | |
/// <summary>The update year.</summary> | |
public readonly byte UpdateYear; | |
/// <summary>The update month.</summary> | |
public readonly byte UpdateMonth; | |
/// <summary>The update day.</summary> | |
public readonly byte UpdateDay; | |
/// <summary>The number of records.</summary> | |
public readonly int NumberOfRecords; | |
/// <summary>The length of the header.</summary> | |
public readonly short HeaderLenght; | |
/// <summary>The length of the bytes records.</summary> | |
public readonly short RecordLenght; | |
[MarshalAs(UnmanagedType.ByValArray, SizeConst = 16)] | |
public readonly byte[] Reserved; | |
/// <summary>Table Flags</summary> | |
public readonly DBFTableFlags TableFlags; | |
/// <summary>Code Page Mark</summary> | |
public readonly byte CodePage; | |
/// <summary>Reserved, contains 0x00</summary> | |
public readonly short EndOfHeader; | |
} | |
public enum DBFVersion : byte | |
{ | |
Unknown = 0, | |
FoxBase = 0x02, | |
FoxBaseDBase3NoMemo = 0x03, | |
VisualFoxPro = 0x30, | |
VisualFoxProWithAutoIncrement = 0x31, | |
dBase4SQLTableNoMemo = 0x43, | |
dBase4SQLSystemNoMemo = 0x63, | |
FoxBaseDBase3WithMemo = 0x83, | |
dBase4WithMemo = 0x8B, | |
dBase4SQLTableWithMemo = 0xCB, | |
FoxPro2WithMemo = 0xF5, | |
FoxBASE = 0xFB | |
} | |
[Flags] | |
public enum DBFTableFlags : byte | |
{ | |
None = 0x00, | |
HasStructuralCDX = 0x01, | |
HasMemoField = 0x02, | |
IsDBC = 0x04 | |
} | |
/// <summary> | |
/// This is the field descriptor structure. There will be one of these for each column in the table. | |
/// </summary> | |
[StructLayout(LayoutKind.Sequential, CharSet = CharSet.Ansi, Pack = 1)] | |
private struct DBFFieldDescriptor | |
{ | |
/// <summary>The field name.</summary> | |
[MarshalAs(UnmanagedType.ByValTStr, SizeConst = 11)] | |
public readonly string FieldName; | |
/// <summary>The field type.</summary> | |
public readonly char FieldType; | |
/// <summary>The field address.</summary> | |
public readonly int Address; | |
/// <summary>The field length in bytes.</summary> | |
public readonly byte FieldLength; | |
/// <summary>The field precision.</summary> | |
public readonly byte DecimalCount; | |
/// <summary>Field Flags</summary> | |
public readonly DBFFieldFlags Flags; | |
/// <summary>AutoIncrement next value</summary> | |
public readonly int AutoIncrementNextValue; | |
/// <summary>AutoIncrement step value</summary> | |
public readonly byte AutoIncrementStepValue; | |
/// <summary>Reserved</summary> | |
[MarshalAs(UnmanagedType.ByValArray, SizeConst = 8)] | |
public readonly byte[] Reserved; | |
public override string ToString() | |
{ | |
return String.Format("{0} {1}", FieldName, FieldType); | |
} | |
} | |
[Flags] | |
public enum DBFFieldFlags : byte | |
{ | |
None = 0x00, | |
System = 0x01, | |
AllowNullValues = 0x02, | |
Binary = 0x04, | |
AutoIncrementing = 0x0C | |
} | |
public enum DBFFieldType : int | |
{ | |
Character = 'C', | |
Currency = 'Y', | |
Numeric = 'N', | |
Float = 'F', | |
Date = 'D', | |
DateTime = 'T', | |
Double = 'B', | |
Integer = 'I', | |
Logical = 'L', | |
Memo = 'M', | |
General = 'G', | |
Picture = 'P' | |
} | |
public static Type ToDbType(char type) | |
{ | |
switch ((DBFFieldType)type) | |
{ | |
case DBFFieldType.Float: | |
return typeof(float); | |
case DBFFieldType.Integer: | |
return typeof(int); | |
case DBFFieldType.Currency: | |
return typeof(decimal); | |
case DBFFieldType.Character: | |
case DBFFieldType.Memo: | |
return typeof(string); | |
case DBFFieldType.Date: | |
case DBFFieldType.DateTime: | |
return typeof(DateTime); | |
case DBFFieldType.Logical: | |
return typeof(bool); | |
case DBFFieldType.General: | |
case DBFFieldType.Picture: | |
return typeof(byte[]); | |
default: | |
return null; | |
} | |
} | |
} | |
} |
Loaded you classes, however it seem like it is only reading the headers for. Can you elaborate on how this class should be implemented.
First of all, I want to thank the author for the solution of this DBF reader. For the users who have questions above, I have modified this solution and made it possible to read everything from the DBF file. You guys could refer to my solution. Initialize a variable first like Dictionary<string, string> metadata = new Dictionary<string, string>(); then pass the metadata as an argument of the readRecords function and try to use a loop to call the readRecord() function I made to read the entire file record by record and you will be able to get all the data.
public void ReadRecord(Dictionary<string, string> fieldRecord)
{
fieldRecord = new Dictionary<string, string>();
var row = reader.ReadBytes(header.RecordLenght);
int fieldsLength = 0;
int position = 0;
//foreach (var field in fields)
//{
// fieldsLength = fieldsLength + field.FieldLength;
//}
foreach (var field in fields)
{
byte[] buffer = new byte[field.FieldLength];
//Array.Copy(row, field.Address, buffer, 0, field.FieldLength);
Array.Copy(row, position+1, buffer, 0, field.FieldLength);
position = position + field.FieldLength;
string text = (encoding.GetString(buffer) ?? String.Empty).Trim();
switch ((DBFFieldType)field.FieldType)
{
case DBFFieldType.Character:
fieldRecord[field.FieldName] = text;
break;
case DBFFieldType.Currency:
if (String.IsNullOrWhiteSpace(text))
{
if ((field.Flags & DBFFieldFlags.AllowNullValues) == DBFFieldFlags.AllowNullValues)
{
fieldRecord[field.FieldName] = null;
}
else
{
fieldRecord[field.FieldName] = "0.0m";
}
}
else
{
//fieldRecord[field.FieldName] = Convert.ToDecimal(text).ToString();
fieldRecord[field.FieldName] = text;
}
break;
case DBFFieldType.Numeric:
if (String.IsNullOrWhiteSpace(text))
{
if ((field.Flags & DBFFieldFlags.AllowNullValues) == DBFFieldFlags.AllowNullValues)
{
fieldRecord[field.FieldName] = null;
}
else
{
fieldRecord[field.FieldName] = "0.0f";
}
}
else
{
//fieldRecord[field.FieldName] = Convert.ToSingle(text).ToString();
fieldRecord[field.FieldName] = text;
}
break;
case DBFFieldType.Float:
if (String.IsNullOrWhiteSpace(text))
{
if ((field.Flags & DBFFieldFlags.AllowNullValues) == DBFFieldFlags.AllowNullValues)
{
fieldRecord[field.FieldName] = null;
}
else
{
fieldRecord[field.FieldName] = "0.0f";
}
}
else
{
//fieldRecord[field.FieldName] = Convert.ToSingle(text).ToString();
fieldRecord[field.FieldName] = text;
}
break;
case DBFFieldType.Date:
if (String.IsNullOrWhiteSpace(text))
{
if ((field.Flags & DBFFieldFlags.AllowNullValues) == DBFFieldFlags.AllowNullValues)
{
fieldRecord[field.FieldName] = null;
}
else
{
fieldRecord[field.FieldName] = DateTime.MinValue.ToString();
}
}
else
{
fieldRecord[field.FieldName] = DateTime.ParseExact(text, "yyyyMMdd", CultureInfo.InvariantCulture).ToString();
}
break;
case DBFFieldType.DateTime:
if (String.IsNullOrWhiteSpace(text) || BitConverter.ToInt64(buffer, 0) == 0)
{
if ((field.Flags & DBFFieldFlags.AllowNullValues) == DBFFieldFlags.AllowNullValues)
{
fieldRecord[field.FieldName] = null;
}
else
{
fieldRecord[field.FieldName] = DateTime.MinValue.ToString();
}
}
else
{
fieldRecord[field.FieldName] = JulianToDateTime(BitConverter.ToInt64(buffer, 0)).ToString();
}
break;
case DBFFieldType.Double:
if (String.IsNullOrWhiteSpace(text))
{
if ((field.Flags & DBFFieldFlags.AllowNullValues) == DBFFieldFlags.AllowNullValues)
{
fieldRecord[field.FieldName] = null;
}
else
{
fieldRecord[field.FieldName] = "0.0";
}
}
else
{
//fieldRecord[field.FieldName] = Convert.ToDouble(text).ToString();
fieldRecord[field.FieldName] = text;
}
break;
case DBFFieldType.Integer:
if (String.IsNullOrWhiteSpace(text))
{
if ((field.Flags & DBFFieldFlags.AllowNullValues) == DBFFieldFlags.AllowNullValues)
{
fieldRecord[field.FieldName] = null;
}
else
{
fieldRecord[field.FieldName] = "0";
}
}
else
{
fieldRecord[field.FieldName] = BitConverter.ToInt32(buffer, 0).ToString();
}
break;
case DBFFieldType.Logical:
if (String.IsNullOrWhiteSpace(text))
{
if ((field.Flags & DBFFieldFlags.AllowNullValues) == DBFFieldFlags.AllowNullValues)
{
fieldRecord[field.FieldName] = null;
}
else
{
fieldRecord[field.FieldName] = "false";
}
}
else
{
fieldRecord[field.FieldName] = (buffer[0] == 'Y' || buffer[0] == 'T').ToString();
}
break;
case DBFFieldType.Memo:
case DBFFieldType.General:
case DBFFieldType.Picture:
default:
fieldRecord[field.FieldName] = buffer.ToString();
break;
}
}
}
nothing to say, except thanks!
I spent a whole day trying to solve a problem when importing a database in dbf.
No connection string would do. Simply, some fields were appearing as empty and they had data.
I started to make a reader but I saw the work it would do. It was just looking on google for a reader that ended up here.
It solved right away.
Thank you very much
@MaiconLL You're welcome! Glad I could help a fellow developer.
First of all, sorry for my flawed english
I had a small problem with a dbf with a binary field.
Using DBFManager, the information appeared as a "memo field"
However, in this data base, this information was supposed to have a double value.
In your class I realized that in this case type double is the character 'B' in the enum
`
public enum DBFFieldType : int
{
Character = 'C',
Currency = 'Y',
Numeric = 'N',
Float = 'F',
Date = 'D',
DateTime = 'T',
Double = 'B',
Integer = 'I',
Logical = 'L',
Memo = 'M',
General = 'G',
Picture = 'P'
}
`
However in the dbf structure documentation, I saw that 'B' is binary and 'O' is double.
Well, to solve my problem, I adjusted so that in enum 'B' a binary and 'O' a double would return.
`
public enum DBFFieldType : int
{
Character = 'C',
Currency = 'Y',
Numeric = 'N',
Float = 'F',
Date = 'D',
DateTime = 'T',
Double = 'O',
Integer = 'I',
Logical = 'L',
Memo = 'M',
General = 'G',
Picture = 'P',
Binary = 'B'
}
`
In addition when converting the information I added the following statement in the "select case"
`
case DBFFieldType.Binary:
if (field.DecimalCount> 0)
record[field] = BitConverter.ToDouble(buffer, 0);
else
record[field] = buffer;
break;
`
It's probably not the best way to deal with it, but for my case it solved.
So if anyone else has this problem, I hope it helps.
Exactly what I needed, thank you very much. I found almost no info on reading DBF with C#, but then I stumbled across this master piece. Works like a charm, great job!
First, love this thing. It does a ton of work for me.
Second, there's a critical flaw when you detect and skip Deleted records. The reader doesn't get advanced so data is skipped from when a deleted record is first seen until EoF.
The fix is to add this reader.ReadBytes just before the continue on line 77.
if (reader.PeekChar() == '*') // DELETED
{
reader.ReadBytes(Header.RecordLength);
continue;
}
Hi Guys, I'm a very noob in c#. How to use the code?
Great tool, thank you!
Any idea, however, why can't I open a DBF file already in use (non exclusive/shared use)?
I want to import some data from DBF files while they are in use by the application.
I tried to read the contents using **FileStream s = new FileStream(filename, FileMode.Open, FileAccess.Read, FileShare.Read);**
but yet I keep having the same error: The process cannot access the file 'document.dbf' because it is being used by another process.
I´m here in 2023 and this code it´s still useful. Thanks a lot.
Hello,
I want to read DBF file using C#. I tried your code with one dbf file.
It is throwing error in datetime field.
can you help me to resolve this issue ?
sample Dbf File
https://dl.dropboxusercontent.com/u/51587522/tempdata.zip
Thanks
Kalpesh
Email : kalpesh2804@yahoo.com