Need to remove double quotes around values(Data)

• p.shabbir

  SSCrazy

  Points: 2404

  More actions

  December 21, 2017 at 3:39 pm

  #326948

  Hi,
  First Merry Christmas to all.🙂
  Please help me to remove double quotes around values(Data). Headers are loading properly with out double quotes.
  Source : csv file. cloumns are not fixed.
  Thanks to Stan Kulp,I got the below code from sqlservercentral article written by him.
  Data is loading but with double quotes around them. here is the link also.
  http://www.sqlservercentral.com/articles/Integration+Services+(SSIS)/76850/

  Dim source_directory As String = "C:\Users\spinjari\Documents\Visual Studio 2015\Projects\"
          Dim source_file As String = "*.csv"

          Dim di As New System.IO.DirectoryInfo(source_directory)
          Dim aryFi As IO.FileInfo() = di.GetFiles(source_file)
          Dim fi As IO.FileInfo

          Dim message As String = "The following files matching the pattern '" & source_file & "' were found in '" & source_directory & ".'" & Chr(13) & Chr(10) & Chr(13) & Chr(10)

          For Each fi In aryFi
              message += di.ToString & fi.ToString & Chr(13) & Chr(10)
          Next

          'Dim button1 As DialogResult = MessageBox.Show(message, "Matching Files in Designated Subdirectory", MessageBoxButtons.OK)

          For Each fi In aryFi

              Dim sLine As String
              Dim arrText As New ArrayList()
              Dim objReader As New StreamReader(di.ToString & fi.ToString)

              Do
                  sLine = objReader.ReadLine()
                  If Not sLine Is Nothing Then
                      arrText.Add(sLine)
                  End If
              Loop Until sLine Is Nothing

              objReader.Close()

              Dim FirstLine As String = ""

              For Each sLine In arrText
                  If FirstLine = "" Then
                      FirstLine = sLine
                  End If
              Next

              Dim FieldNames() As String = Split(FirstLine, ",")
              Dim FieldList As String = "These are the parsed fields in the first line of the '" & fi.ToString & "' file." & Chr(13) & Chr(10) & Chr(13) & Chr(10)
              Dim count As Integer = Split(FirstLine, ",").Length
              Dim mySqlStatement As String = "BEGIN TRY DROP TABLE [" & fi.ToString.Substring(0, fi.ToString.Length - 4) & "]" & " END TRY BEGIN CATCH END CATCH CREATE TABLE [" & fi.ToString.Substring(0, fi.ToString.Length - 4) & "]" & " ("
              Dim comma As String = ""

              For index As Integer = 0 To count - 1
                  mySqlStatement += comma & FieldNames(index) & " VARCHAR(50)"
                  comma = ","
                  FieldList += FieldNames(index) & Chr(13) & Chr(10)
              Next

              mySqlStatement += ")"

              'Dim button2 As DialogResult = MessageBox.Show(FieldList, "Field Names", MessageBoxButtons.OK)

              Dim msg As String = "This SQL statement will be used to create the '" & fi.ToString.Substring(0, fi.ToString.Length - 4) & "' table." & Chr(13) & Chr(10) & Chr(13) & Chr(10)

              'Dim button3 As DialogResult = MessageBox.Show(msg & mySqlStatement, "DDL Statement", MessageBoxButtons.OK)

              Dim myADONETConnection As SqlClient.SqlConnection = DirectCast(Dts.Connections("MyDB").AcquireConnection(Dts.Transaction), SqlClient.SqlConnection)
              Dim myCommand As New SqlClient.SqlCommand(mySqlStatement, myADONETConnection)
              myCommand.ExecuteNonQuery()
              myADONETConnection.Close()

              FirstLine = ""
              Dim line_count As Integer = 1

              For Each sLine In arrText

                  If FirstLine = "" Then
                      FirstLine = sLine
                  Else
                      line_count += 1

                      Dim fields() As String = Split(sLine, ",")
                      Dim ValueList As String = "These are the parsed values in line " & line_count & " of the '" & fi.ToString & "' file." & Chr(13) & Chr(10) & Chr(13) & Chr(10)
                      Dim FieldCount As Integer = Split(sLine, ",").Length

                      mySqlStatement = "INSERT INTO [" & fi.ToString.Substring(0, fi.ToString.Length - 4) & "]" & " VALUES ("

                      comma = ""

                      For FieldIndex As Integer = 0 To FieldCount - 1
                          mySqlStatement += comma & "'" & fields(FieldIndex) & "'"
                          ValueList += fields(FieldIndex) & Chr(13) & Chr(10)
                          'ValueList += Replace(ValueList, ""","")
                          comma = ", "
                      Next

                      mySqlStatement += ")"

                      Dim button4 As DialogResult = MessageBox.Show(ValueList, "Field Values", MessageBoxButtons.OK)

                      msg = "This SQL statement will be used To insert record " & line_count - 1 & " into the '" & fi.ToString.Substring(0, fi.ToString.Length - 4) & "' table." & Chr(13) & Chr(10) & Chr(13) & Chr(10)

                      Dim button5 As DialogResult = MessageBox.Show(msg & mySqlStatement, "DML Statement", MessageBoxButtons.OK)

                      myADONETConnection = DirectCast(Dts.Connections("MyDB").AcquireConnection(Dts.Transaction), SqlClient.SqlConnection)
                      myCommand = New SqlClient.SqlCommand(mySqlStatement, myADONETConnection)
                      myCommand.ExecuteNonQuery()
                      myADONETConnection.Close()

                  End If

              Next

              '    Dim button6 As DialogResult = MessageBox.Show("The file '" & di.ToString & fi.ToString & "' will be moved to '" & di.ToString & "Backup\" & fi.ToString & ".'", "'Move Data File' Command", MessageBoxButtons.OK)
              '    Dim file As New System.IO.FileInfo(di.ToString & fi.ToString)

              '    Try

              '        file.MoveTo(di.ToString & "Backup\" & fi.ToString)

              '    Catch ex As Exception

              '        Dim temp As New System.IO.FileInfo(di.ToString & "Backup\" & fi.ToString)
              '        temp.Delete()
              '        file.MoveTo(di.ToString & "Backup\" & fi.ToString)

              '    End Try

          Next
  Thnx in Advnc.