In Microsoft Excel - CSV is displayed OK.
However - in SSIS - some records are broken - for example records where Description field contains: ""WHITE HOT.""
I'm not expert on CSV format - but aren't double quotes used for escape in csv - and if so then WHY microsoft implements it one of their products and not it SSIS?
So what am I going to do now?Search the forums, man! You aren't alone and SSIS doesn't handle embedded quotes.|||http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=546729&SiteID=1|||Thanks for the link.
I was mostly pissed off that my 2 hour project was going to be days worth of work because they decided not to implement CSV.
However - it did not take long for me to write the following code in VB Script Task
(The components marked as asnwer in this linked post seemed not quite what i needed - and Im somewhat suspicious of installing another SSIS component)
Sub ProcessFile(ByVal source_file As String, ByVal destination_file As String)
Dim sReader As New System.IO.StreamReader(source_file)
Dim sWriter As New System.IO.StreamWriter(destination_file)
Do
sWriter.WriteLine(FixCsvQuotes(sReader.ReadLine()))
Loop Until sReader.Peek = -1
sWriter.Close()
sReader.Close()
End Sub
Function FixCsvQuotes(ByVal csv_row As String) As String
Dim qiStr As String = Chr(34) & "," & Chr(34) ' ","
Dim qiStr1 As String = Chr(34) & "," ' ",
Dim qiStr2 As String = "," & Chr(34) ' ,"
Dim str1 As String = ""
Dim str2 As String = ""
Dim str3 As String = ""
str1 = csv_row.Substring(0, csv_row.IndexOf(Chr(34)) + 1)
str3 = csv_row.Substring(csv_row.LastIndexOf(Chr(34)), csv_row.Length - csv_row.LastIndexOf(Chr(34)))
str2 = csv_row.Substring(str1.Length, csv_row.Length - str1.Length - str3.Length)
str2 = Replace(str2, qiStr, "|&|")
str2 = Replace(str2, qiStr1, "|&")
str2 = Replace(str2, qiStr2, "&|")
str2 = Replace(str2, Chr(34), """)
str2 = Replace(str2, "|&|", qiStr)
str2 = Replace(str2, "|&", qiStr1)
str2 = Replace(str2, "&|", qiStr2)
Return (str1 & str2 & str3)
End Function|||Note:
following string will still cause an error if in description:
...available in ""The Siena"", the most sought after ...|||
TheViewMaster wrote:
Thanks for the link. I was mostly pissed off that my 2 hour project was going to be days worth of work because they decided not to implement CSV.
They have done.
never ever trust what Excel says about CSVs. If you want to look at the raw data, open it up in a text editor.
-Jamie
|||Well - it still doesn't work - is there a VB function which can fix CSV files for SSIS import?|||Allrite - my 3rd hack at Visual basic FixCsv function:
Function FixCsvQuotes(ByVal csv_row As String) As String
Dim qiStr0 As String = "," & Chr(34) & Chr(34) & "," ' ,"",
Dim qiStr As String = Chr(34) & "," & Chr(34) ' ","
Dim qiStr1 As String = Chr(34) & "," ' ",
Dim qiStr2 As String = "," & Chr(34) ' ,"
Dim str1 As String = ""
Dim str2 As String = ""
Dim str3 As String = ""
str1 = csv_row.Substring(0, csv_row.IndexOf(Chr(34)) + 1)
str3 = csv_row.Substring(csv_row.LastIndexOf(Chr(34)), csv_row.Length - csv_row.LastIndexOf(Chr(34)))
str2 = csv_row.Substring(str1.Length, csv_row.Length - str1.Length - str3.Length)
While InStr(str2, qiStr0) > 0
str2 = Replace(str2, qiStr0, ",&||&,")
End While
str2 = Replace(str2, qiStr, "|&|")
str2 = Replace(str2, Chr(34) & Chr(34), """)
str2 = Replace(str2, qiStr1, "|&")
str2 = Replace(str2, qiStr2, "&|")
str2 = Replace(str2, Chr(34), """)
str2 = Replace(str2, "&||&", """""")
str2 = Replace(str2, "|&|", qiStr)
str2 = Replace(str2, "|&", qiStr1)
str2 = Replace(str2, "&|", qiStr2)
Return (str1 & str2 & str3)
End Function
