我正在尝试使用C#将CSV文件插入SQL数据库 . 我可以读取csv文件并将其插入表中,但是任何带有嵌入式逗号的字段都无法正确读取 . 我怎么能解决这个问题呢 . 下面是我正在使用的代码:
protected void UploadFile_Click(object sender, EventArgs e)
{
conStr = "workstation id=" + ServerName + ";packet size=4096;user id=xx;password=" yyyyy ";data source=" blahblah ";persist security info=False;initial catalog=";
conStr = conStr + DB;
string filepath = "D:\\Work\\Sample01.csv";
StreamReader sr = new StreamReader(filepath);
int NrLines = 0;
string[,] mline;
mline = new string[NrLines, 50];
int cntra = 0;
int counter = 0;
using (StreamReader cr = new StreamReader(filepath))
{
while ((cr.ReadLine()) != null)
{
NrLines++;
}
cr.Close();
}
mline = new string[NrLines, 25];
for (int lcounter = 1; (lcounter <= NrLines); lcounter++)
{
string[] sline = sr.ReadLine().Split(',');
//strElem = strElem.Append("");
if (sline != null)
{
for (int c = 0; c < sline.Length; c++)
mline[cntra, c] = sline[c];
cntra++;
}
}
sr.Close();
for (counter = 1; counter < NrLines; counter++)
{
string Date = mline[counter, 0].ToString();
string SiteUD = mline[counter, 1].ToString();
string SiteName = mline[counter, 2].ToString();
string ModelNo = mline[counter, 3].ToString();
string MachID = mline[counter, 4].ToString();
string Manufacture = mline[counter, 5].ToString();
string TotalCashIn = mline[counter, 6].ToString();
string TotalCashOut = mline[counter, 7].ToString();
string NotesIN = mline[counter, 8].ToString();
string CoinsIn = mline[counter, 9].ToString();
string CoinsOut = mline[counter, 10].ToString();
string CoinstoDrop = mline[counter, 11].ToString();
string RemoteCashIn = mline[counter, 12].ToString();
string RemoteCashOut = mline[counter, 13].ToString();
string TotalWin = mline[counter, 14].ToString();
string TotalBet = mline[counter, 15].ToString();
string GGR = mline[counter, 16].ToString();
string GamesPlayed = mline[counter, 17].ToString();
string HandPays = mline[counter, 18].ToString();
string HopperRefill = mline[counter, 19].ToString();
SQL = "INSERT INTO ztrewVNLCemsImport " +
"([Date], [SiteUD], [SiteName], [ModelNo.], [MachID], " +
"[Manufacture], [TotalCashIn], [TotalCashOut], [NotesIN], [CoinsIn], " +
"[CoinsOut], [CoinstoDrop], [RemoteCashIn], [RemoteCashOut], [TotalWin], " +
"[TotalBet], [GGR], [GamesPlayed], [HandPays], [HopperRefill] ) " +
"VALUES " +
"('" + Date + "', '" + SiteUD + "', '" + SiteName + "', '" + ModelNo + "', '" + MachID + "', " +
"'" + Manufacture + "', '" + TotalCashIn + "', '" + TotalCashOut + "', '" + NotesIN + "', '" + CoinsIn + "', " +
"'" + CoinsOut + "', '" + CoinstoDrop + "', '" + RemoteCashIn + "', '" + RemoteCashOut + "', '" + TotalWin + "', " +
"'" + TotalBet + "', '" + GGR + "', '" + GamesPlayed + "', '" + HandPays + "', '" + HopperRefill + "') ";
SQL = SQL.Replace('\t', ' ');
2 回答
尝试使用CSV解析器库来读取CSV文件,而不是自己动手 . 有很多库可用:CSV parser/reader for C#?
读取CSV数据后,可以使用ORM或普通ADO.Net将其写回数据库,如您在示例中所使用的那样 . 如果您自己编写SQL,则应确保在查询中使用SQL参数 . 这不仅可以保证每种数据类型都能正确存储,而且还可以保护您免受任何恶意内容的侵害(不是您的情况下很可能) .
最简单的解决方案可能是使用基于数据库表的SqlDataAdapter .
Visual Studio 2017
文件 - >新建 - > Projcet - > Visual C# - >控制台应用程序(.NET Framework) - >文件名:-ImportCSVToSQL
3.打开“Program.cs”编写代码