将缺少的分隔符添加到 .CSV 文件 Visual Basic vb.net

Question

所以我编写(拼凑(了一个程序，它需要一个.CSV 文件并将其注入数据库。我遇到的问题是每周报告末尾最后一行缺少分隔符。(此数据不能修改，除非手动(。我需要能够将分隔符添加到最后一行的末尾，以避免我不断得到的连续"格式错误的行异常"和丢失的最后一行。希望得到一些帮助和建议。

Private Sub subProcessFile(ByVal strFileName As String)
'This is the file location for the CSV File
Using TextFileReader As New Microsoft.VisualBasic.FileIO.TextFieldParser(strFileName)
'removing the delimiter
TextFileReader.TextFieldType = Microsoft.VisualBasic.FileIO.FieldType.Delimited
TextFileReader.SetDelimiters(",")
ProgressBar1.Value = 0
Application.DoEvents()
'variables
Dim TextFileTable As DataTable = Nothing
Dim Column As DataColumn
Dim Row As DataRow
Dim UpperBound As Int32
Dim ColumnCount As Int32
Dim CurrentRow As String()

'Loop To read in data from CSV
While Not TextFileReader.EndOfData
Try
CurrentRow = TextFileReader.ReadFields()
If Not CurrentRow Is Nothing Then
''# Check if DataTable has been created
If TextFileTable Is Nothing Then
TextFileTable = New DataTable("TextFileTable")
''# Get number of columns
UpperBound = CurrentRow.GetUpperBound(0)
''# Create new DataTable
For ColumnCount = 0 To UpperBound
Column = New DataColumn()
Column.DataType = System.Type.GetType("System.String")
Column.ColumnName = "Column" & ColumnCount
Column.Caption = "Column" & ColumnCount
Column.ReadOnly = True
Column.Unique = False
TextFileTable.Columns.Add(Column)
ProgressBar1.Value = 25
Application.DoEvents()
Next
clsDeletePipeLineData.main()
End If
Row = TextFileTable.NewRow
'Dim Rownum As Double = Row
'If Rownum >= 1715 Then
'    MsgBox(Row)
'End If
For ColumnCount = 0 To UpperBound
Row("Column" & ColumnCount) = CurrentRow(ColumnCount).ToString
Next
TextFileTable.Rows.Add(Row)
clsInsertPipeLineData.main(CurrentRow(0).ToString, CurrentRow(1).ToString, CurrentRow(2).ToString, CurrentRow(3).ToString, CurrentRow(4).ToString, CurrentRow(5).ToString, CurrentRow(6).ToString, CurrentRow(7).ToString, CurrentRow(9).ToString)
ProgressBar1.Value = 50
Application.DoEvents()
End If
Catch ex As _
Microsoft.VisualBasic.FileIO.MalformedLineException
MsgBox("Line " & ex.Message &
"is not valid and will be skipped.")
End Try
End While
ProgressBar1.Value = 100
Application.DoEvents()
clsMailConfirmation.main()
TextFileReader.Dispose()
MessageBox.Show("The process has been completed successfully")
End Using

数据的一个很好的例子是：

"1","£10","Joe Bloggs"

一个不好的数据示例是：

"2","£50","Jane Smith

提前感谢！

Answer 1

如果Using ...之前始终缺少结束"，则可以在行上使用File.AppendAllText(strFileName, """")，否则您可能希望先检查文件的最后一个字符，以使其更加健壮。

当我查看代码时，可能有一些更改可能很有用：

• 我注意到 DataTable 实际上并没有在任何地方使用，因此您可以通过删除该代码来加速 Sub。
• clsDeletePipeLineData.main()似乎只是在循环中第一次调用，因此可以使用"标志变量"来确定是否应该调用它。
• 添加一些代码来检查是否存在所需数量的列非常简单，然后再尝试访问它们，例如，currentRow(9).ToString()，并在错误消息中提供一些详细信息。
• 该方法的目的是将数据放入数据库中，因此其中实际上不应该有 MessageBox.Show。您可以将其放在调用subProcessFile之后，以便向用户提供反馈。

这些更改中的第一个可以使其足够快，以至于不需要进度条 - 也许在调用方法之前将光标设置为等待光标并在之后重置它会提供足够的反馈。使用 Application.DoEvents 可能会以意想不到的方式出错，因此，如果它确实需要进度报告，请考虑使用 BackgroundWorker 或异步方法，如如何使用 Async/Await 执行进度报告中的 C# 所示。

Option Infer On
Option Strict On
Imports Microsoft.VisualBasic.FileIO
Imports System.IO
' other code...
Private Sub subProcessFile(ByVal csvFilename As String)
' The CSV files are faulty because of a missing final double-quote. Add it.
File.AppendAllText(csvFilename, """")
Dim expectedCsvColumnCount = 10
Using TextFileReader As New TextFieldParser(csvFilename) With {
.TextFieldType = FieldType.Delimited,
.Delimiters = {","},
.HasFieldsEnclosedInQuotes = True}
Dim lineNo = 0
Dim firstRow = True
While Not TextFileReader.EndOfData
Try
lineNo += 1
Dim currentRow = TextFileReader.ReadFields()
If currentRow IsNot Nothing Then
If firstRow Then
clsDeletePipeLineData.main()
firstRow = False
End If
If currentRow.Count = expectedCsvColumnCount Then
clsInsertPipeLineData.main(currentRow(0).ToString(), currentRow(1).ToString(), currentRow(2).ToString(), currentRow(3).ToString(), currentRow(4).ToString(), currentRow(5).ToString(), currentRow(6).ToString(), currentRow(7).ToString(), currentRow(9).ToString())
Else
Dim errMsg = String.Format("Wrong number of columns (expected {0}, found {1}) at line {2} in file ""{3}"".", expectedCsvColumnCount, currentRow.Count, lineNo, csvFilename)
Throw New InvalidDataException(errMsg)
End If
End If
Catch ex As Microsoft.VisualBasic.FileIO.MalformedLineException
MsgBox("Line " & ex.Message & " is not valid and will be skipped.")
End Try
End While
clsMailConfirmation.main()
End Using
End Sub