Excel to Text convert all to general format RSS

• • Reply
  

  johnzee

  Member

  100 Points

  139 Posts

  Excel to Text convert all to general format

  Jan 30, 2013 08:52 PM|LINK

  Hi,

  I have following code I use to convert Excel files into text files. It works fine for the most part but some times where users have entered numbers in the format 1.2345 and also have changed the cell formatting to a number, 2 digits it only exports 1.23 like a WYSIWYG. So it's working as expected but my new requirement is to ignore the formatting and export all the data. Is there any way to change the Excel data in a general format regardless of how it's orginally formatted so the text file could also contain all the 4 digits after decimal. Most of the suggestions I see are to use the @Text etc which is only applicable to Excel Interop but it's all on the server side code and Excel is not installed. Thanks

   

  Public Sub Main()
          Dim StrConn As String
          Dim DA As New OleDbDataAdapter
          Dim DS As New DataSet
          Dim Str As String
          Dim ColumnCount As Integer
          Dim OuterCount As Integer
          Dim InnerCount As Integer
          Dim RowCount As Integer

  
          StrConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Dts.Variables("FileToDelete").Value.ToString() + ";Extended Properties=""Excel 8.0;HDR=YES;IMEX=1;TypeGuessRows=0;ImportMixedTypes=Text"""
          Dim objConn As New OleDbConnection(StrConn)
          Try
              objConn.Open()
              If objConn.State = ConnectionState.Closed Then
                  Exit Sub
              End If
          Catch ex As Exception
              SendEmail("There was an error while processing the Vendor Catalog file: " _
           & vbLf & vbLf & ex.ToString())
              Exit Sub
          End Try
          Dim objCmd As New OleDbCommand("SELECT * FROM [Sheet1$],[Data$B2:B3] WHERE SUPPLIERSKU IS NOT NULL ", objConn)
          objCmd.CommandType = CommandType.Text
          Dim ObjStreamWriter As StreamWriter = New StreamWriter(Dts.Variables("FileTransfer").Value.ToString + "\CustBooking.txt")
          Try
              DA.SelectCommand = objCmd
              DA.Fill(DS, "XLData")
          Catch ex As Exception
              SendEmail("There was an error while processing the file: " _
           & vbLf & vbLf & ex.ToString())

          End Try
          Try
              RowCount = DS.Tables(0).Rows.Count
              ColumnCount = DS.Tables(0).Columns.Count
              'get the column titles
              Str = ""
              For i As Integer = 0 To ColumnCount - 1
                  Str &= DS.Tables(0).Columns(i).ColumnName & Chr(9) 'Tab delimited
              Next
              ObjStreamWriter.WriteLine(Str)

              'get the acutal data from Rows & Cells
              For OuterCount = 0 To RowCount - 1
                  Str = ""
                      For InnerCount = 0 To ColumnCount - 1
                      Dim str2 As String
                      str2 = DS.Tables(0).Rows(OuterCount).Item(InnerCount).ToString() 
                      If str2.Contains(".") And str2.Contains("+") And Not str2.Contains("""") Then
                          Str &= CInt(DS.Tables(0).Rows(OuterCount).Item(InnerCount)) & Chr(9)
                          'Strip dollar sign from the cells
                      ElseIf str2.Contains("$") Then
                          Str &= DS.Tables(0).Rows(OuterCount).Item(InnerCount).ToString().Remove(1, 1) & Chr(9)

  
                      Else
                          Str &= DS.Tables(0).Rows(OuterCount).Item(InnerCount).ToString() & Chr(9)
                      End If

  
                  Next
                  ObjStreamWriter.WriteLine(Str)
              Next

              ObjStreamWriter.Close()
              Dts.Variables("FileToDeleteTXT").Value = Dts.Variables("FileTransfer").Value.ToString + "\CustBooking.txt"
              'Try
          Catch ex As Exception
              SendEmail("There was an error while processing the file: Missing column(s)." _
           & vbLf & vbLf & ex.ToString())
          End Try
          objCmd.Dispose()
          objCmd = Nothing
          objConn.Close()
          objConn.Dispose()
          objConn = Nothing

          'Successful exit
          Dts.TaskResult = Dts.Results.Success
      End Sub