附加信息:外部表不是预期的格式.CSV文件中
本文关键字:格式 CSV 文件 信息 外部 | 更新日期: 2023-09-27 17:57:27
我在c#windows应用程序中工作,我做了一个项目,将数据从csv文件导入mysql服务器。我有可能链接导入数据,但当我尝试时,我有问题
附加信息:外部表格不是预期的格式
在我的代码中
我在微软Visual studio 2015工作。NET框架4.5.2。
编码
private void button1_Click(object sender, EventArgs e)
{
OpenFileDialog dlg = new OpenFileDialog();
dlg.Filter = "Text files | *.csv";
if (dlg.ShowDialog() == DialogResult.OK)
{
string fileName;
fileName = dlg.FileName;
textBox1.Text = fileName;
}
}
private void button2_Click(object sender, EventArgs e)
{
if (textBox1.Text != "")
{
string path = textBox1.Text;
string name = "";
string age = "";
string class = "";
string sec = "";
string address = "";
string phno = "";
OleDbConnection my_con = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=Excel 12.0;");
my_con.Open();
OleDbCommand icmd = new OleDbCommand("SELECT * FROM [dataGridView1_Data$]", my_con);
OleDbDataReader dr = icmd.ExecuteReader();
while (dr.Read())
{
name = dr[0].ToString();
age = dr[1].ToString();
class = dr[2].ToString();
sec = dr[3].ToString();
address = dr[4].ToString();
phno = dr[6].ToString();
MySqlConnection con = new MySqlConnection("SERVER=10.65.43.687;" +
"DATABASE=student;" +
"UID=root;" +
"PASSWORD=root123");
con.Open();
MySqlCommand icmmd = new MySqlCommand("INSERT INTO student(name,age,class,sec,address,phno)VALUES(@a,@b,@c,@d,@e,@f)", con);
icmmd.Parameters.AddWithValue("a", Name);
icmmd.Parameters.AddWithValue("b", Age);
icmmd.Parameters.AddWithValue("c", Class);
icmmd.Parameters.AddWithValue("d", Section);
icmmd.Parameters.AddWithValue("e", Address);
icmmd.Parameters.AddWithValue("f", Phone);
icmmd.ExecuteNonQuery();
con.Close();
}
MessageBox.Show("data Imported");
textBox1.Text = "";
}
else if (textBox1.Text == "")
{
}
MessageBox.Show("Upload Successfull!");
}
线路错误:
my_con.Open();
请帮我解决我的问题。
您需要对连接字符串进行以下更改
string _name = string.Empty;
OpenFileDialog OFD = new OpenFileDialog();
if(OFD.ShowDialog()==DialogResult.OK)
{
_name = OFD.FileName;
}
var pathstring = System.IO.Path.GetDirectoryName(_name);
con = new OleDbConnection();
con.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source="+pathstring+";Extended Properties='Text;HDR=YES;FMT=Delimited;'";
con.Open();
da = new OleDbDataAdapter("Select * from "+System.IO.Path.GetFileName(_name), con);
dt = new DataTable();
da.Fill(dt); con.Close();
这将帮助您打开到csv文件的连接&读取文件。
使用此代码将Excel的数据(.xlx、.xlsx、.csv)转换为DataTable。
private DataTable ConvertExcelToDataTable(string filename) {
using (System.IO.Stream inputStream = System.IO.File.OpenRead(filename)) {
using (Syncfusion.XlsIO.ExcelEngine excelEngine = new Syncfusion.XlsIO.ExcelEngine()) {
Syncfusion.XlsIO.IApplication application = excelEngine.Excel; Syncfusion.XlsIO.IWorkbook workbook = application.Workbooks.Open(inputStream);
Syncfusion.XlsIO.IWorksheet worksheet = workbook.Worksheets[0];
DataTable dataTable = worksheet.ExportDataTable(worksheet.UsedRange, Syncfusion.XlsIO.ExcelExportDataTableOptions.ColumnNames);
return dataTable;
}
}
}