Import af CSV fil med anførselstegn på nogle enkelte records
Hej.Jeg importere en CSV fil direkte ind til en SQL server hver nat via et lille VB.NET program. Det går rigtig godt for langt de fleste records, men der er nogle af dem som min rutine springer over. Jeg har identificeret dem til at være de rækker som indeholder et anførselstegn et eller andet sted. Efter anførselstegnet blivet resten af linjen betraget som én samlet information.
Dette er fejlen:
Error: Linje 26427 kan ikke parses ved hjælp af de aktuelle afgrænsere.
Her er et eksempel på linjen der fejler:
4100;XE20;FSMA CONTRACT ;00999999;01.02.2010;31.12.9999; ; ; ; ;3320999998 ; ;7120V_S ;"FJERNSYN ATG COP;20.07.2010; ;3997 ;FINDE(CHARGER) ;PD801;TV MODEL ;P9900;FLATSCREEN PRODUCTS ;44888881 ;44488881 ; ;21.07.2010;001; ; ; ; ; ; ; ; 45978 ; 901 ; ;26.07.2010;26.07.2010;
Her er min kode:
Using MyReader As New Microsoft.VisualBasic.FileIO.TextFieldParser("\\Kkakn2p\pads_dat\BASS_FTP\mifno", System.Text.Encoding.GetEncoding(28605))
MyReader.TextFieldType = Microsoft.VisualBasic.FileIO.FieldType.Delimited
MyReader.Delimiters = New String() {";"}
Dim currentRow As String()
'Loop through all of the fields in the file.
'If any lines are corrupt, report an error and continue parsing.
My.Computer.FileSystem.WriteAllText("\\dnkcopeuas01\root\BASS Activity\SOE_LOG\BassConvertLog.txt", "NORWAY - MIF" & vbCrLf, True)
While Not MyReader.EndOfData
Try
currentRow = MyReader.ReadFields()
' Her skal info skrives til SQL filen
For Each currentField As String In currentRow
currentField = currentField.Replace(Chr(34), "") 'replace double quote with single quote if needed
Next
myConnection = New SqlClient.SqlConnection(myConnString)
myQuery = "INSERT INTO tblmifnordic (Country, Ctyp, ContractTypeDesc, ContractNr, BP, SerialNumber, Model, SoldTo, Cal, FlatRate, BWA4Rate, ColA4Rate, BA4Inclusive, CA4Inclusive, BA4AYCV, CA4AYCV, StartDate, EndDate, InstDate, ShipTo, MeterCard, BDU, ContractStartDate, ContractEndDate) VALUES (@Country, @Ctyp, @ContractTypeDesc, @ContractNr, @BP, @SerialNumber, @Model, @SoldTo, @Cal, @FlatRate, @BWA4Rate, @ColA4Rate, @BA4Inclusive, @CA4Inclusive, @BA4AYCV, @CA4AYCV, @StartDate, @EndDate, @InstDate, @ShipTo, @MeterCard, @BDU, @ContractStartDate, @ContractEndDate)"
myCommand = New System.Data.SqlClient.SqlCommand(myQuery, myConnection)
myCommand.Parameters.AddWithValue("@Country", LocStr(currentRow(0)))
myCommand.Parameters.AddWithValue("@Ctyp", LocStr(currentRow(1)))
myCommand.Parameters.AddWithValue("@ContractTypeDesc", LocStr(currentRow(2)))
myCommand.Parameters.AddWithValue("@ContractNr", LocStr(currentRow(3)))
myCommand.Parameters.AddWithValue("@BP", LocStr(currentRow(9)))
myCommand.Parameters.AddWithValue("@SerialNumber", LocStr(currentRow(10)))
myCommand.Parameters.AddWithValue("@Model", LocStr(currentRow(17)))
myCommand.Parameters.AddWithValue("@SoldTo", LocStr(currentRow(22)))
myCommand.Parameters.AddWithValue("@Cal", LocStr(currentRow(26)))
myCommand.Parameters.AddWithValue("@FlatRate", LocStr(currentRow(27)))
myCommand.Parameters.AddWithValue("@BWA4Rate", LocStr(currentRow(28)))
myCommand.Parameters.AddWithValue("@ColA4Rate", LocStr(currentRow(29)))
myCommand.Parameters.AddWithValue("@BA4Inclusive", LocStr(currentRow(31)))
myCommand.Parameters.AddWithValue("@CA4Inclusive", LocStr(currentRow(32)))
myCommand.Parameters.AddWithValue("@BA4AYCV", LocStr(currentRow(34)))
myCommand.Parameters.AddWithValue("@CA4AYCV", LocStr(currentRow(35)))
myCommand.Parameters.AddWithValue("@StartDate", LocStr(currentRow(4)))
myCommand.Parameters.AddWithValue("@EndDate", LocStr(currentRow(5)))
myCommand.Parameters.AddWithValue("@InstDate", LocStr(currentRow(15)))
myCommand.Parameters.AddWithValue("@ShipTo", LocStr(currentRow(23)))
myCommand.Parameters.AddWithValue("@MeterCard", LocStr(currentRow(24)))
myCommand.Parameters.AddWithValue("@BDU", LocStr(currentRow(21)))
myCommand.Parameters.AddWithValue("@ContractStartDate", LocStr(currentRow(4)))
myCommand.Parameters.AddWithValue("@ContractEndDate", LocStr(currentRow(5)))
myCommand.Connection = myConnection
myCommand.Connection.Open()
myCommand.ExecuteScalar()
myCommand.Connection.Close()
myConnection.Close()
Catch ex As Microsoft.VisualBasic.FileIO.MalformedLineException
'Generate report for failed imports..
My.Computer.FileSystem.WriteAllText("\\dnkcopeuas01\root\BASS Activity\SOE_LOG\BassConvertLog.txt", "Error: " & ex.Message & vbCrLf, True)
End Try
End While
End Using
Jeg vil sætte pris på enhver hjælp... :)