I have a huge collection of visual foxpro dbf files that I would like to convert to csv. (If you like, you can download some of the data here. Click on the 2011 link for Transaction Data, and prepare to wait a long time...)
I can open each table with DBF View Plus (an awesome freeware utility), but exporting them to csv takes a few hours per file, and I have several dozen files to work with.
Is there a program like DBF View plus that will allow me to set up a batch of dbf-to-csv conversions to run overnight?
/Edit: Alternatively, is there a good way to import .dbf files straight into SQL Server 2008? They should all go into 1 table, as each file is just a subset of records from the same table and should have all the same column names.
Load up your list of FoxPro files in an array/list then call the ConvertDbf on each to convert them from FoxPro to csv files. See the c# console application code below...
Credit c# datatable to csv for the DataTableToCSV function.
using System;
using System.Data;
using System.Data.OleDb;
using System.IO;
using System.Linq;
using System.Text;
namespace SO8843066
{
class Program
{
static void Main(string[] args)
{
string connectionString = @"Provider=VFPOLEDB.1;Data Source=C:\";
string dbfToConvert = @"C:\yourdbffile.dbf";
ConvertDbf(connectionString, dbfToConvert, dbfToConvert.Replace(".dbf", ".csv"));
Console.WriteLine("End of program execution");
Console.WriteLine("Press any key to end");
Console.ReadKey();
}
static void DataTableToCSV(DataTable dt, string csvFile)
{
StringBuilder sb = new StringBuilder();
var columnNames = dt.Columns.Cast<DataColumn>().Select(column => column.ColumnName).ToArray();
sb.AppendLine(string.Join(",", columnNames));
foreach (DataRow row in dt.Rows)
{
var fields = row.ItemArray.Select(field => field.ToString()).ToArray();
for (int i =0;i < fields.Length;i++)
{
sb.Append("\"" + fields[i].Trim() );
sb.Append((i != fields.Length - 1) ? "\"," : "\"");
}
sb.Append("\r\n");
}
File.WriteAllText(csvFile, sb.ToString());
}
static void ConvertDbf(string connectionString, string dbfFile, string csvFile)
{
string sqlSelect = string.Format("SELECT * FROM {0}", dbfFile);
using (OleDbConnection connection = new OleDbConnection(connectionString))
{
using (OleDbDataAdapter da = new OleDbDataAdapter(sqlSelect, connection))
{
DataSet ds = new DataSet();
da.Fill(ds);
DataTableToCSV(ds.Tables[0], csvFile);
}
}
}
}
}