上传CSV文件到SQL Server
本文关键字:SQL Server 文件 CSV 上传 | 更新日期: 2023-09-27 18:07:30
我正在构建一个每月读取5个CSV文件的系统。这些文件应该遵循一定的格式和顺序。我有一个主表和5个临时表。首先读取每个CSV文件,然后将其批量插入到相应的临时表中。在将5个csv文件批量插入到它们各自的临时表中之后,我再次将临时表中的所有记录插入到主表中。这可以确保在将数据插入到主表之前先上传所有文件。
我用ASP.net构建了这个系统,在调试和测试过程中一切都很顺利。每当我将应用程序部署到生产服务器时,就会出现这个问题。在部署应用程序之后,我使用了在开发和测试期间上传的相同csv文件,系统显示了从字符串到日期时间格式的数据转换错误。
我尝试了很多事情来解决这个问题,但似乎问题仍然存在。我尝试将生产数据库的排序改为与开发期间使用的相同的排序。我还尝试更改生产服务器中的一些区域设置,但仍然不起作用。
我想也许我可以通过编程来处理这个问题,而不是从临时表批量插入到主表,我会写一些for循环,将每条记录手动插入到主表,但我想这会造成性能问题,因为我每次都会插入大约10万条记录。
我想知道是否有人在部署时遇到过类似的问题。我仍然觉得应用程序的行为在部署后发生了变化,这很奇怪。
下面是代码的一部分,它将inventory.csv文件上传到服务器,然后将csv批量插入临时表TB_TEMP_INVENTORY,然后将记录从temp插入到主表TB_CATTLE。对其他4个文件执行此操作,并且几乎与此相同。
OleDbConnection conn = new OleDbConnection(ConfigurationManager.AppSettings["LivestockConnectionString"]);
OleDbCommand comm;
OleDbDataAdapter adapter;
DataTable table = new DataTable();
string file = string.Empty;
string content = string.Empty;
StreamReader reader;
StreamWriter writer;
string month = monthDropDownList.SelectedValue;
string year = yearDropDownList.SelectedItem.Text;
// upload inventory file
file = System.IO.Path.GetFileName(inventoryFileUpload.PostedFile.FileName);
inventoryFileUpload.PostedFile.SaveAs("C://LivestockCSV//" + file);
// clean inventory file
file = "C://LivestockCSV//" + file;
reader = new StreamReader(file);
content = reader.ReadToEnd();
reader.Close();
writer = new StreamWriter(file);
writer.Write(content.Replace("'"", "")); // remove quotation
writer.Close();
writer = new StreamWriter(file);
writer.Write(content.Replace(",NULL,", ",,")); // remove NULL
writer.Close();
writer = new StreamWriter(file);
writer.Write(content.Replace(",0,", ",,")); // remove 0 dates
writer.Close();
writer = new StreamWriter(file);
writer.Write(content.Replace(",0", ",")); // remove 0 dates at eol
writer.Close();
try
{
conn.Open();
comm = new OleDbCommand("TRUNCATE TABLE TB_TEMP_INVENTORY", conn); // clear temp table
comm.ExecuteNonQuery();
// bulk insert from csv to temp table
comm = new OleDbCommand(@"SET DATEFORMAT DMY;
BULK INSERT TB_TEMP_INVENTORY
FROM '" + file + "'" +
@" WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = ''n'
)", conn);
comm.ExecuteNonQuery();
// check if data for same month exists in cattle table
comm = new OleDbCommand(@"SELECT *
FROM TB_CATTLE
WHERE Report='Inventory' AND Month=" + month + " AND Year=" + year, conn);
if (comm.ExecuteScalar() != null)
{
comm = new OleDbCommand(@"DELETE
FROM TB_CATTLE
WHERE Report='Inventory' AND Month=" + month + " AND Year=" + year, conn);
comm.ExecuteNonQuery();
}
// insert into master cattle table
comm = new OleDbCommand(@"SET DATEFORMAT MDY;
INSERT INTO TB_CATTLE(ID, Sex, BirthDate, FirstCalveDate, CurrentUnit, OriginalCost, AccumulatedDepreciation, WrittenDownValue, NetRealizableValue, CapitalGainLoss, Month, Year, Report, Locked, UploadedBy, UploadedAt)
SELECT DISTINCT ID, Sex, BirthDate, FirstCalveDate, CurrentUnit, 0, 0, 0, 0, 0, " + month + ", " + year + @", 'Inventory', 0, 'Admin', '" + DateTime.Now + @"'
FROM TB_TEMP_INVENTORY", conn);
comm.ExecuteNonQuery();
conn.Close();
}
catch (Exception ex)
{
ClientScript.RegisterStartupScript(typeof(string), "key", "<script>alert('" + ex.Message + "');</script>");
return;
}
您没有指定如何进行插入,但这里的合理选项是SqlBulkCopy
,它可以将DataTable
或IDataReader
作为输入;这将为您提供足够的机会处理数据—在内存中(DataTable
)或通过流API (IDataReader
),同时仍然使用有效的导入。CsvReader
是加载CSV文件的好选项。
另一种选择是在staging表中使用非常基本的插入操作,并通过TSQL代码来处理数据。
为什么它在开发/生产之间改变;最可能的答案是:
- 你在开发中使用的数据不具有代表性
- 两者之间存在环境/配置差异
1)检查开发/测试的SQL Server语言和DATEFORMAT设置&生产env。
DBCC USEROPTIONS
2) CSV文件使用什么日期格式(源)?
日期/时间字段(目标)使用什么数据类型?DECLARE @v VARCHAR(10) = '2010-08-23';
SET DATEFORMAT mdy;
SELECT CAST(@v AS DATETIME)
,CAST(@v AS DATE)
,YEAR(CAST(@v AS DATETIME))
,MONTH(CAST(@v AS DATETIME))
,DAY(CAST(@v AS DATETIME));
SET DATEFORMAT dmy;
SELECT CAST(@v AS DATETIME)
,CAST(@v AS DATE);