Simple thing - trying to set up connection for CSV file.
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
