在将数据加载到数据表时删除csv中的额外逗号提供了一些条件。 [英] Removing extra comma in csv while loading data to a datatable provided some conditions.
问题描述
我有一种情况，我使用DataTable将数据从csv加载到SQL。最初已知数据的一个挑战是，在其中一个需要删除的字段中可能有一个额外的逗号。这完全如下面的代码所示使用if块。
现在最近这个应用程序失败了，因为csv文件包含更多包含逗号的字段。有没有一种方法我们可以使用while块来测试是否仍然是oStreamDataValues [22]！=然后其余的字段保持不变并且连接发生在columnName [12]中，考虑到列[8]是固定的还是已经修好了。我们的想法是运行while循环，直到columnName [22]为空，然后将其设置为Y以标记此行有异常，并将更正的数据加载到DataTable中以上传到SQL。
我的尝试：
I have a situation in which I am loading data from csv to SQL using DataTable. One challenge with the data known initially was that it may have an extra comma in one of the fields which needs to be removed. This is done perfectly as shown in the code below using if block.
Now recently this app failed since the csv file contained more fields with a comma. Is there a way we can use while block here to test if still oStreamDataValues[22] != "" then rest of the fields stay as it is and the concatenation happens in columnName[12] considering column[8] is either fixed or is already fixed. The idea is to run the while loop till columnName[22] is blank and then put that as 'Y' to mark that this row had anomalies and load the corrected data in DataTable to upload to SQL.
What I have tried:
private static void ReadCsvFileData()
{
var destinationFileNameFullPath2 = ConfigurationManager.AppSettings["destinationFileNameFullPath2"]; //full path and name of the csv file
var oStreamReader = new StreamReader(destinationFileNameFullPath2);
var oDataTable = new DataTable();
var rowCount = 0;
string[] columnNames = null; // for column names
while (!oStreamReader.EndOfStream)
{
var oStreamRowData = oStreamReader.ReadLine().Trim();
if (oStreamRowData.Length > 0)
{
var oStreamDataValues = oStreamRowData.Split(',');
if (rowCount == 0)
{
rowCount = 1;
columnNames = oStreamDataValues;
foreach (var csvHeader in columnNames)
{
var oDataColumn = new DataColumn(csvHeader.ToUpper(), typeof(string));
oDataColumn.DefaultValue = string.Empty;
oDataTable.Columns.Add(oDataColumn);
}
}
else
{
var oDataRow = oDataTable.NewRow();
for (var i = 0; i < columnNames.Length; i++)
{
if (oStreamDataValues[22] != "")
{
oDataRow[columnNames[0]] = oStreamDataValues[0] == null ? string.Empty : oStreamDataValues[0].Trim();
oDataRow[columnNames[1]] = oStreamDataValues[1] == null ? string.Empty : oStreamDataValues[1].Trim();
oDataRow[columnNames[2]] = oStreamDataValues[2] == null ? string.Empty : oStreamDataValues[2].Trim();
oDataRow[columnNames[3]] = oStreamDataValues[3] == null ? string.Empty : oStreamDataValues[3].Trim();
oDataRow[columnNames[4]] = oStreamDataValues[4] == null ? string.Empty : oStreamDataValues[4].Trim();
oDataRow[columnNames[5]] = oStreamDataValues[5] == null ? string.Empty : oStreamDataValues[5].Trim();
oDataRow[columnNames[6]] = oStreamDataValues[6] == null ? string.Empty : oStreamDataValues[6].Trim();
oDataRow[columnNames[7]] = oStreamDataValues[7] == null ? string.Empty : oStreamDataValues[7].Trim();
oDataRow[columnNames[8]] = oStreamDataValues[9] == null ? oStreamDataValues[8].Trim() : oStreamDataValues[8].Trim() + ' ' + oStreamDataValues[9].Trim();
oDataRow[columnNames[9]] = oStreamDataValues[10] == null ? string.Empty : oStreamDataValues[10].Trim();
oDataRow[columnNames[10]] = oStreamDataValues[11] == null ? string.Empty : oStreamDataValues[11].Trim();
oDataRow[columnNames[11]] = oStreamDataValues[12] == null ? string.Empty : oStreamDataValues[12].Trim();
oDataRow[columnNames[12]] = oStreamDataValues[13] == null ? string.Empty : oStreamDataValues[13].Trim();
oDataRow[columnNames[13]] = oStreamDataValues[14] == null ? string.Empty : oStreamDataValues[14].Trim();
oDataRow[columnNames[14]] = oStreamDataValues[15] == null ? string.Empty : oStreamDataValues[15].Trim();
oDataRow[columnNames[15]] = oStreamDataValues[16] == null ? string.Empty : oStreamDataValues[16].Trim();
oDataRow[columnNames[16]] = oStreamDataValues[17] == null ? string.Empty : oStreamDataValues[17].Trim();
oDataRow[columnNames[17]] = oStreamDataValues[18] == null ? string.Empty : oStreamDataValues[18].Trim();
oDataRow[columnNames[18]] = oStreamDataValues[19] == null ? string.Empty : oStreamDataValues[19].Trim();
oDataRow[columnNames[19]] = oStreamDataValues[20] == null ? string.Empty : oStreamDataValues[20].Trim();
oDataRow[columnNames[20]] = oStreamDataValues[21] == null ? string.Empty : oStreamDataValues[21].Trim();
oDataRow[columnNames[21]] = oStreamDataValues[22] == null ? string.Empty : oStreamDataValues[22].Trim();
oDataRow[columnNames[22]] = 'Y';
}
else
{
oDataRow[columnNames[i]] = oStreamDataValues[i] == null ? string.Empty : oStreamDataValues[i].Trim();
}
}
//Creating DataTable
oDataTable.Rows.Add(oDataRow);
}
}
}
oStreamReader.Close();
oStreamReader.Dispose();
//SQLBulkUpload code goes here.
}
推荐答案
我建议使用ADO.NET（OleDb）代替自定义csv文件阅读器。 ADO.NET（OleDb） [ ^ ]可让您阅读将分隔的文本文件分隔为datatable对象。使用schema.ini文件，您将能够定义要读取的列集。
请参阅：
从.NET应用程序访问Microsoft Office数据 [ ^ ]
关于文本文件的大量ADO [ ^ ]
Schema.ini文件（文本文件驱动程序） [ ^ ]
如何：向文本文件数据源添加模式定义 [ ^ ]
使用OleDb导入文本文件（选项卡，CSV，自定义） [ ^ ]
读取文本文件特定列 [ ^ ]
I'd suggest to use ADO.NET (OleDb) instead of custom csv file reader. ADO.NET (OleDb)[^] allows you to read delimited text files into datatable object. Using schema.ini file you'll be able to define set of columns to read.
Please see:
Accessing Microsoft Office Data from .NET Applications[^]
Much ADO About Text Files[^]
Schema.ini File (Text File Driver)[^]
How to: Add a Schema Definition to a Text File Data Source[^]
Using OleDb to Import Text Files (tab, CSV, custom)[^]
Read Text File Specific Columns[^]
看看这篇文章 - CSV文件解析器 [ ^ ]
如果线路返回故障ormed你可以在调用解析引擎的代码中处理它。
Look at this article - CSV File Parser[^]
If a line comes back "malformed" you can handle that in the code that calls the parsing engine.
这篇关于在将数据加载到数据表时删除csv中的额外逗号提供了一些条件。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
