My friend, Ilya, had a problem in SSIS. He had a .csv file with too
many commas. The meaning is that strings that started and ended with
inverted commas (") and had commas inside it were recognized by SSIS as
new column. For example, the row:
"My name, is Miky", 200, 10 was recognised by SSIS as four columns
instead of three. Ilya wrote down a code for SSIS (in VB) that run
before the package begin its work. Here it is, hope it will help who
ever seen this.
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.IO
Imports System.Text
Imports Microsoft.VisualBasic.FileIO
Public Class ScriptMain
Public Sub Main()
Dim csvFileFullPath As String
Dim tabFileFullPath As String
csvFileFullPath = Dts.Connections("Your CSV Connnection").ConnectionString
tabFileFullPath = Dts.Connections("Your Table Connection").ConnectionString
Using tabStreamWriter As New StreamWriter(tabFileFullPath, False, System.Text.Encoding.GetEncoding(1255))
Using csvFileReader As New StreamReader(csvFileFullPath, System.Text.Encoding.GetEncoding(1255),True)
Dim currentRow As String
currentRow = csvFileReader.ReadLine()
tabStreamWriter.WriteLine(currentRow)
While Not csvFileReader.EndOfStream
Dim outputRow As New Text.StringBuilder()
Dim tmp, tmp1 as String
Dim offset as Int32 = 1
Dim beginS, endS As Int32
beginS = 1
currentRow = csvFileReader.ReadLine()
beginS = InStr(offset, currentRow, """")
While Not beginS = 0 Or offset > Len(currentRow)
endS = InStr(beginS+1, currentRow, """")
tmp = Mid(currentRow, beginS, endS - beginS)
tmp1 = Replace(tmp, ",", " ")
currentRow = Replace(currentRow, tmp, tmp1)
offset = endS + 1
beginS = InStr(offset, currentRow, """"")
End While
outputRow.Append(currentRow)
tabStreamWriter.WriteLine(outputRow.ToString())
End While
End Using
End Using
Dts.TaskResult = Dts.Result.Success
End Sub
End Class
The solution here is to search for any comma (,) that is between two inverted commas (") and replace it by space.
Although it is a good solution, I would take another solution: Replace
any comma by special string, such as &Miky&, convert the csv
file into table, and after that go over that column(s) and replace any
&Miky& by comma.