How to import excel and foxpro (.dbf) files into Datagrid using c #?

F D

Hello All,
I am new to the site and the C# language as well. I am trying to find out how I can import data into my Grid using C sharp. I was able to import Foxpro dbf's now I want to import excel files. Rather than only filtering DBF's I also added .XLS's whcich you will see in the code. Unfortunately, I am getting a not known or unknow Driver error, which ofcourse is the connection string for DBF's and not excel. I'm trying to set it up such that u can choose either DBF or excel. Here is the code for this particualr form.
Thanks for your help, it is much appreciated.

CODE:
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.IO;
using System.Data.Odbc;
namespace MySQLData
{
public partial class Form7 : Form
{
public Form7()
{
InitializeComponent();
}

private void BrowseButton_Click(object sender, EventArgs e)
{
OpenFileDialog file = new OpenFileDialog();
file.Title = "Select a DBF or XLS file";
file.InitialDirectory = @" c:\gsc\";
file.FileName = txtFilename.Text;
file.Filter = "Excel Sheet(*.xls)|*.xls|DBF Files(*.dbf)|*.dbf";
file.FilterIndex = 100;
file.RestoreDirectory = true;

if (file.ShowDialog() == DialogResult.OK)
{

txtFilename.Text = file.FileName; //Filename and Path

Application.DoEvents();
}

}

public static DataTable GetDataTableDBF(string strFileName)
{
OdbcConnection conn = new OdbcConnection("Driver={Microsoft Visual FoxPro Driver};SourceType=DBF;SourceDB=" + Path.GetFullPath(strFileName).Replace(Path.GetFileName(strFileName), "") + ";Exclusive=No");
conn.Open();
string strQuery = "SELECT * FROM [" + Path.GetFileName(strFileName) + "]";
OdbcDataAdapter adapter = new OdbcDataAdapter(strQuery, conn);
DataSet ds = new DataSet();
adapter.Fill(ds);

return ds.Tables[0];

}

private void ViewButton_Click_1(object sender, EventArgs e)
{

try
{

DataTable dt = GetDataTableDBF(txtFilename.Text);

dataGridView1.DataSource = dt.DefaultView;
label1.Text = "Click [BROWSE] to select and view another file.";
label2.Text = "Total records: [" + dt.Rows.Count + "]";
MessageBox.Show("Total records imported [" + dt.Rows.Count + "]");
}
catch (Exception ex)
{
MessageBox.Show("An error has occured. Please click browse and select a file to view. " + ex.Message);
}
}
private void MainMenuButton_Click(object sender, EventArgs e)
{
Form4 Form4 = new Form4();
Form4.Show();
this.Hide();
}

}
}

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

Sam Hobbs16y ago
Accepted answer
I think you do not want to actually import; I think you want to open the table, where the Excell spreadsheet can be opened as a table. There are various ways to open tables.

Will the columns always the the same, as in the same number of columns, all with the same data type in each column? The solution depends on the answers to those questions in the sense that if the format is always the same then the programming is easier than if the format varies and then your program must be more dynamic.
0
Wallace Martin15y ago
Transfer Data from Database to DBF via GridView
Transfer Data from Database to PDF via GridView
Transfer Data from Database to HTML via GridView
Transfer Data from Database to Excel via GridView
0
Sam Hobbs16y ago
There are basically two ways to open the spreadsheet; either as a table or as a spreadsheet. Note that those terms are not precise; I am being general now. If you open a spreadsheet as a table, we typically use ADO .Net. If you open it as a spreadsheet, then you use the Excel interface. The advantage of using ADO is that it is easier but using ADO you can only access a spreadsheet as a table with columns and rows and every row has the same number of columns and the data types of each column is the same for each column. I suggest using ADO if it can do what you need done.

See my article Database Table Update in a DataGridView without Writing Code.

Then go to the Data Sources window in Visual Studio and try to create a data source for the spreadsheet. You might have to use the Server Explorer window in Visual Studio too; I am not sure. If you can create a data source, then drop that into a form as described in my article, then you are in business to do more.

As stated previously, the problem is if you want to support both xls and dbf formats and any others. Then it gets complicated, especially if you need to support varying columns in the sense of varying numbers of columns and such.
0
F D16y ago
Sam,
Thanks for responding to my questions. Much aprreciated!
Actually I want to open and view as a table with the option of importing into my database (FYI - I'm currently using MySql). The columns and their datatypes will always be in the same format.
Fabe
0