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
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