excel load results in null values
-
loading an excel sheet into a table results in null values for some entities in one column. What could be the reason for this ?
Target table is build using the DTS transformation task (matches with the XLS sheet)
Thanks.
-
Hi hbkdba,
quote:
loading an excel sheet into a table results in null values for some entities in one column. What could be the reason for this ?
I would start at making sure that no column in the original excel sheet is made invisible (=width=0).
Also I think DTS wizard generates its sample based on the first rows and not on the entire spreadsheet. You should make sure that there are no NULL values later on in the sheet.
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Hi,
DTS looks for first 8 rows to make certain decisions about the data to be loaded. also if sheet is not being ended properly then it also puts niull values at the end of the data rows.
Hope this helps.
Regards,
Affan
-
thanks for the replies
-
I have had this problem and someone helped me out with a fiddly (and odd) but working 'solution'.
You select all of the data in the Excel column. Copy and paste it into notepad. Get the focus back on Excel and with the cells still selected, press Delete to remove the data. Then with the cells still selected, go to the menus and choose Format-Cells and select the Text category. Then go back to Notepad and copy the data and paste back into the Excel cells. You can deal with at least 65,000 cells of short data this way – probably more – and I think you may even be able to operate on multiple columns – they copy and paste OK into and back. If you don't delete the data it doesn't work.
The data reads in correctly (or has every time for me). This works irrespective of whether the first cells are numeric and therefore it nulls all subsequent non-numeric data or where the first cells are alpha in which case it nulls the numerics.
'pling
-
I use a similar trick to make sure I don't wind up with row after row of nulls. The only real difference is that after I paste it into a text editor, I save the text file and import it with DTS instead. Mostly because then I don't have to reformat the Excel file and I trust the import a bit more from text vs. Excel.
Matthew Galbraith
-
Hi Kevin,
quote:
I have had this problem and someone helped me out with a fiddly (and odd) but working 'solution'.You select all of the data in the Excel column. Copy and paste it into notepad. Get the focus back on Excel and with the cells still selected, press Delete to remove the data. Then with the cells still selected, go to the menus and choose Format-Cells and select the Text category. Then go back to Notepad and copy the data and paste back into the Excel cells. You can deal with at least 65,000 cells of short data this way – probably more – and I think you may even be able to operate on multiple columns – they copy and paste OK into and back. If you don't delete the data it doesn't work.
The data reads in correctly (or has every time for me). This works irrespective of whether the first cells are numeric and therefore it nulls all subsequent non-numeric data or where the first cells are alpha in which case it nulls the numerics.
cool, thanks for sharing this one!
I've already used this in anger. Works great!
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Excel is a bear for this. It reads the value and determines for format for each cell unless you have set the format prior to loading the data. The solution to delete the data and then format it and paste it back in probably works. That's essentially what I do programmatically using Excel macros.
Excel Macro: Format_Text
Dim xCell As Variant
Dim strTemp As String
Dim intPos As Integer
For Each xCell In Selection
If Left$(xCell.Value, 1) <> "'" Then
xCell.Value = "'" & Trim(xCell.Value)
'Debug.Print xCell.Value
End If
DoEvents
Next xCell
Excel Macro: Format_Numeric
Purpose: Format to numberic and replace
hard to find alpha characters
Dim varNumber As Variant
Dim xCell As Variant
On Error GoTo HandleErr
' NOTE: This does not reformat the selected data but removes the forced
' text and loads a zero value.
' First a range of cells must be selected by the user
' Then the code will perform the following for all selected cells.
For Each xCell In Selection
' If the cell is already forced to text using the "'" in the first column
' then remove the "'" but if it is not forced to text
' and there is no value (the length is 0) then
' put a zero in the cell.
If Len(xCell.Value) > 0 And Left$(xCell.Value, 1) = "'" Then
varNumber = Trim(Mid(xCell.Value, 2))
xCell.Value = Mid(xCell.Value, 2)
End If
If Len(xCell.Value) = 0 Then
xCell.NumberFormat = "0"
xCell.Value = 0
Else
varNumber = Trim(xCell.Value)
'Convert the characters for "L" and
'"O" and "I" to numeric values
' These character values are often
' confused for numerics on the screen
If Not IsNumeric(varNumber) Then
If Left$(varNumber, 1) = "'" Then
varNumber = Mid$(varNumber, 2)
End If
Select Case varNumber
Case "l", "L"
varNumber = 1
Case "o", "O"
varNumber = 0
Case "i", "I"
varNumber = 1
Case "x", "X"
varNumber = 1
End Select
End If
xCell.NumberFormat = "0"
xCell.Value = varNumber
If Len(xCell.Value) = 0 Then
xCell.Value = 0
End If
End If
Next xCell
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply