导入CSV文件到数据库不工作asp
本文关键字:工作 asp 数据库 CSV 文件 导入 | 更新日期: 2023-09-27 17:55:03
我正在尝试使用asp导入CSV文件到数据库中,此代码来自http://www.aspsnippets.com/Articles/Import-Upload-CSV-file-data-to-SQL-Server-database-in-ASPNet-using-C-and-VBNet.aspx
try
{
//Upload and save the file
string csvPath = Server.MapPath("/upload/") + Path.GetFileName(FileUpload2.PostedFile.FileName);
FileUpload2.SaveAs(csvPath);
DataTable dt = new DataTable();
dt.Columns.AddRange(new DataColumn[3] { new DataColumn("Id", typeof(int)),
new DataColumn("Name", typeof(string)),
new DataColumn("Country",typeof(string)) }
);
string csvData = File.ReadAllText(csvPath);
foreach (string row in csvData.Split(''n'))
{
if (!string.IsNullOrEmpty(row))
{
dt.Rows.Add();
int i = 0;
foreach (string cell in row.Split(','))
{
dt.Rows[dt.Rows.Count - 1][i] = cell;
i++;
}
}
}
string consString = ConfigurationManager.ConnectionStrings["TOP2000_IAO4B_GROEP5ConnectionString"].ConnectionString;
using (SqlConnection con = new SqlConnection(consString))
{
using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(con))
{
//Set the database table name
sqlBulkCopy.DestinationTableName = "[Customers]";
con.Open();
sqlBulkCopy.WriteToServer(dt);
con.Close();
}
}
}
catch(Exception ex)
{
Response.Write(ex);
}
然而,当我进入调试模式,我看看字符串csvData的值是什么,它是一个空字符串:/我想知道是什么原因,因为显然没有数据插入这种方式。
这是CSV
1,John Hammond,United States
2,Mudassar Khan,India
3,Suzanne Mathews,France
4,Robert Schidner,Russia
CSV真的很简单,让它很容易,有人能帮我这个吗?
既然您在调试时提到您看到了CsvData
,我怀疑问题出在csvData.Split(''n')
语句上。
我刚刚修改了那个特定的逻辑,应该对你有用。
try
{
//Upload and save the file
string csvPath = Server.MapPath("/upload/") + Path.GetFileName(FileUpload2.PostedFile.FileName);
FileUpload2.SaveAs(csvPath);
DataTable dt = new DataTable();
dt.Columns.AddRange(new DataColumn[3] { new DataColumn("Id", typeof(int)),
new DataColumn("Name", typeof(string)),
new DataColumn("Country",typeof(string)) });
foreach (string row in File.ReadAllLines(csvPath))
{
if (!string.IsNullOrEmpty(row))
{
dt.Rows.Add();
int i = 0;
foreach (string cell in row.Split(','))
{
dt.Rows[dt.Rows.Count - 1][i] = cell;
i++;
}
}
}
string consString = ConfigurationManager.ConnectionStrings["TOP2000_IAO4B_GROEP5ConnectionString"].ConnectionString;
using (SqlConnection con = new SqlConnection(consString))
{
using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(con))
{
//Set the database table name
sqlBulkCopy.DestinationTableName = "[Customers]";
con.Open();
sqlBulkCopy.WriteToServer(dt);
con.Close();
}
}
}
catch(Exception ex)
{
Response.Write(ex);
}
我猜你的问题是分裂CSV数据。试运行代码:
private string[] SplitString(string inputString)
{
System.Text.RegularExpressions.RegexOptions options = ((System.Text.RegularExpressions.RegexOptions.IgnorePatternWhitespace | System.Text.RegularExpressions.RegexOptions.Multiline)
| System.Text.RegularExpressions.RegexOptions.IgnoreCase);
Regex reg = new Regex("(?:^|,)('''"(?:[^'''"]+|'''"'''")*'''"|[^,]*)", options);
MatchCollection coll = reg.Matches(inputString);
string[] items = new string[coll.Count];
int i = 0;
foreach (Match m in coll)
{
items[i++] = m.Groups[0].Value.Trim('"').Trim(',').Trim('"').Trim();
}
return items;
}