[RESOLVED] Excel and null data - my ongoing saga
This is my previous thread where this topic was discussed. I am really understanding this a lot better, but I have a question.
The spreadsheet I am now reading has a SSN column. We get this Excel file from our client so they are in control of what they give us, and it is a bit screwy. Sometimes the record is for a trust, sometimes it is for person. When it is a trust, the SSN does not have colons separating it, that is, 123456789. When it is a person, it does: 123:45:6789. This column is type General in Excel. So I am figuring that when I use OLE DB to select the records from this spreadsheet, it returns null data to me for the trust SSN's, as they look like pure numerics. I understand that is how it works (from jmcilhinney's explanation in my previous thread).
So shouldn't I be able to modify the Excel spreadsheet before my program reads it and set the format of the column? I changed it to Text, but still had the problem. Then I typed over the SSN. I typed 123456789 into the cell already containing this value, and it put that little green triangle up in the corner to signify it was Text. But this doesn't happen unless I retype. Additionally, if I click on the cell I get a yellow caution symbol and when I hover over it, it says "The number in this cell is formatted as text or preceeded by an apostrophe." Fine, that is what I want, but why didn't it do it to the existing data as well?
I know this is a boring problem, but I only have until Friday left to my contract here, and I'd hate to leave some unknown issue in my code for someone else to figure out. I don't want to give up on Excel and OLE DB (like I did in the previous thread, but that's because often the data were null so I thought I had no choice) but I will if it's a lost cause.
Thanks!
Re: Excel and null data - my ongoing saga
I tried both with and without colons using the code below and both worked. Try mimicing my connection string.
Code:
Private Sub ReadRange()
Dim cnRange As String = "provider=Microsoft.Jet.OLEDB.4.0; " & _
"data source='test.xls';" & _
"Extended Properties=""Excel 8.0; IMEX=1; HDR=No;"""
Using MyConnection As New System.Data.OleDb.OleDbConnection(cnRange)
MyConnection.Open()
' without colon
Dim cmd As OleDbCommand = New OleDbCommand( _
"SELECT * FROM [Sheet1$A21:D23]", MyConnection)
' with colon
'Dim cmd As OleDbCommand = New OleDbCommand( _
' "SELECT * FROM [Sheet1$A16:D19]", MyConnection)
Dim dr As System.Data.IDataReader = cmd.ExecuteReader
Dim dt As New DataTable
dt.Load(dr)
DataGridView2.DataSource = dt
End Using
End Sub
Re: Excel and null data - my ongoing saga
vbforums must not like me when I am posting Excel questions. I just lost everything I typed again!
Anyway, what I was saying - I tried IMEX=1, it did not make a difference. What does that mean?
In your image, you have the green triangle in the top left corner of your cell. When my cell is like that also, it works. But that is the cell I retyped. Other cells that do not have the colons and are pure numerics and also do not have the triangle marking them as text - are read as nulls. Any other ideas?
P.S. Did you notice your posts = 666? If I were you, I'd hurry up and post more! :)
Re: Excel and null data - my ongoing saga
Next, I reformatted the column to Special, Social Security Number. It changed ALL the straight nine numerics to 3 digits, hypen, 2 digits, hypen, four digits. So now all the SSN's are either separated by hyphens or colons. Still, it is giving me back the hyphenated SSNs as nulls. I think I am going to give up...
Re: Excel and null data - my ongoing saga
Okay I am on this 666 thing.
The image below was done by manually typing in the first cell value 12345678 then 12345679 below it. Upon leaving each cell the values showed up as shown. The empty cell below immediately displayed 0 so it would appear Excel is targetting the column as numeric.
What I can not repeat from yesterday is using a lower version of Excel which seemed to behave differently when entering numerics as I jump between three different computers depending where I am at a moment in time. Any ways best of luck with this.
IMEX
http://support.microsoft.com/kb/194124
http://www.codeproject.com/KB/vb/Exc...nectivity.aspx
http://www.sqldts.com/254.aspx
Excel 2007 using code I supplied
Re: Excel and null data - my ongoing saga
I am using Excel 2003. I guess that is important to have told you.
The next thing I tried was opening the original spreadsheet and globally changing 0: to 0, 1: to 1, 2: to 2, etc, and that worked - I never was returned any null data because I have consistent format in every cell. However, I do not like that "solution" because it is requiring the user to manipulate the file before she runs it through my program. And it's a SSN. What if she makes a typo and changes 1: to 2?
Thank you for the wish of good luck and thanks for trying to help.
Re: Excel and null data - my ongoing saga
Quote:
Originally Posted by
MMock
Next, I reformatted the column to Special, Social Security Number. It changed ALL the straight nine numerics to 3 digits, hypen, 2 digits, hypen, four digits. So now all the SSN's are either separated by hyphens or colons. Still, it is giving me back the hyphenated SSNs as nulls. I think I am going to give up...
I tried this with the same data and got back the numbers
Re: Excel and null data - my ongoing saga
Quote:
Originally Posted by
MMock
I am using Excel 2003. I guess that is important to have told you.
The next thing I tried was opening the original spreadsheet and globally changing 0: to 0, 1: to 1, 2: to 2, etc, and that worked - I never was returned any null data because I have consistent format in every cell. However, I do not like that "solution" because it is requiring the user to manipulate the file before she runs it through my program. And it's a SSN. What if she makes a typo and changes 1: to 2?
Thank you for the wish of good luck and thanks for trying to help.
I saved the spreadsheet as 2003 format and format the cells as various numeric types, still got the expected results back.
Only difference in code is the file name and file format.
Code:
Dim cnRange As String = "provider=Microsoft.Jet.OLEDB.4.0; " & _
"data source='C:\Data\Book1_2003.xls';" & _
"Extended Properties=""Excel 8.0; IMEX=1; HDR=No;"""
Using MyConnection As New System.Data.OleDb.OleDbConnection(cnRange)
MyConnection.Open()
Dim cmd As OleDbCommand = New OleDbCommand( _
"SELECT * FROM [Sheet1$A16:F17]", MyConnection)
Dim dr As System.Data.IDataReader = cmd.ExecuteReader
Dim dt As New DataTable
dt.Load(dr)
DataGridView2.DataSource = dt
Re: Excel and null data - my ongoing saga
Okay, I am going to shelve this until tomorrow and finish up another client's data that promises to be much easeir. Then whatever time I have left I will play with this some more. Thanks for you suggestions - I will put them to the test soon.
Re: Excel and null data - my ongoing saga
666, I am getting somewhere with your code, I think. I am using your code to select a range - A1:CF15. I also created a quick dgv on my form so I could bind it to the data, as you did. And I see both kinds of SSNs - the colon ones and the non-colon ones! I will forge on and do with data what I need to (not display a grid but build an output file) and see how that goes. I am leaving in 45 minutes, and today is my last day in the office. Tomorrow I am working from home. I just hope they don't yank my access away from me while I am in the middle of solving this! Thanks so much.
Re: Excel and null data - my ongoing saga
Quote:
Originally Posted by
MMock
666, I am getting somewhere with your code, I think. I am using your code to select a range - A1:CF15. I also created a quick dgv on my form so I could bind it to the data, as you did. And I see both kinds of SSNs - the colon ones and the non-colon ones! I will forge on and do with data what I need to (not display a grid but build an output file) and see how that goes. I am leaving in 45 minutes, and today is my last day in the office. Tomorrow I am working from home. I just hope they don't yank my access away from me while I am in the middle of solving this! Thanks so much.
Good to hear this is working for you :)
Re: Excel and null data - my ongoing saga
One major difference is I was using a data adapter. You are using a reader (and now so am I). Maybe that was key.
I am leaving now (going to see the new Narnia movie) and I will pick this up tomorrow and nail it.
Re: Excel and null data - my ongoing saga
Quote:
Originally Posted by
MMock
One major difference is I was using a data adapter. You are using a reader (and now so am I). Maybe that was key.
I am leaving now (going to see the new Narnia movie) and I will pick this up tomorrow and nail it.
Rarely will I use a DataAdapter.
Enjoy the movie.
Re: Excel and null data - my ongoing saga
Rarely I will use a data adapter myself now, too!!!
And I no longer have an ongoing saga.
This is my beautiful code. I love it. Sorry it has so much extra stuff, but I am pasting the whole block (and I like to document, which of course is important when one is a contractor):
Code:
' I set IMEX=1 in the connection string because of the reason and fix cited here:
' http://support.microsoft.com/kb/194124
' However, it didn't work when I was using a data adapter. Then when I used a data reader instead, it worked.
' It may also work without IMEX=1, but I don't think it's worth removing and testing from the beginning again
' If anything weird or screwy starts happening, think about removing it at that time. But I think you are
' good keeping it.
' Also, there is a header, but I said HDR=No because the header isn't the first row, like it "should be".
' There is a blank first row. So I skip the blank row and the header row manually.
Dim cnConnectionString As String = _
String.Format("provider=Microsoft.Jet.OLEDB.4.0; data source={0};Extended Properties=""Excel 8.0; IMEX=1; HDR=No;""", _
dlgInputFile.FileName)
Using MyConnection As New System.Data.OleDb.OleDbConnection(cnConnectionString)
MyConnection.Open()
' Get the worksheet names from the schema
Dim i As Integer ' Index that loops through the worksheets looking for the one containing our annuity data
Dim cmd As OleDb.OleDbCommand = New OleDb.OleDbCommand
Dim ExcelTables As DataTable = Nothing
' Instead of hardcoding the worksheet name, which is today <classifiedworksheetname> but may perhaps change someday
' (?), I am getting all the worksheets and picking the one that has 84 columns, as that is our annuity
' data, today. I realize that someday the number of columns could also change, at which time you
' would have to change 84 to the new number of columns. And if another worksheet besides your annuity
' data ever has the same columns as <classifiedworksheetname>, you would need a different way of determining
' the worksheet you want to process. Let's hope for nothing changing!
ExcelTables = MyConnection.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, _
New Object() {Nothing, Nothing, Nothing, "TABLE"})
cmd.Connection = MyConnection
Dim dr As System.Data.OleDb.OleDbDataReader = Nothing
Dim bFoundAnnuityWorksheet As Boolean = False
For i = 0 To ExcelTables.Rows.Count - 1
cmd.CommandText = "SELECT * FROM [" & ExcelTables.Rows(i)!TABLE_NAME.ToString & "] ORDER BY " & CStr(POLICY_ORDINAL)
dr = cmd.ExecuteReader
If dr.FieldCount = 84 Then
bFoundAnnuityWorksheet = True ' We found the annuity data worksheet. We're done with this loop.
Exit For
End If
dr.Close()
Next i
If Not bFoundAnnuityWorksheet Then
MessageBox.Show("Could not find the annuity data worksheet. Cannot continue", _
"File Preparation Failure", _
MessageBoxButtons.OK)
Exit Function
End If
cmd.CommandText = String.Format("SELECT * FROM [{0}] ORDER BY {1}", ExcelTables.Rows(i)!TABLE_NAME.ToString, CStr(POLICY_ORDINAL))
If Not dr.IsClosed Then dr.Close()
dr = cmd.ExecuteReader
While dr.Read
' Skip blank row(s). Since dr.Item(1) is the policy which is the key, it will never be blank.
If dr.Item(1) Is DBNull.Value Then Continue While
' I am skipping the header row here. I am counting on the header row calling the policy column
' "POLICY" but if <classifiedclientname> ever changes it, it will need to change here.
If CStr(dr.Item(1)) = "POLICY" Then Continue While
For iCols As Integer = 0 To 83 ' Columns out to CF in Excel = 84
Select Case iCols
Case 1
sbOutputRecord.Append(AppendDelimiter(dr.Item(iCols)))
' Column 1 (offset is 0) is our policy number. Save it, to know that we processed
' this policy so that when we are doing funds we'll look up and match this policy.
arrPolicies.Add(CStr(dr.Item(iCols)))
Case 0, 4 To 13, 22 To 24, 27 To 34, 47 To 61, 64, 65, 68, 69, 70, _
72 To 74, 76 To 80
sbOutputRecord.Append(AppendDelimiter(dr.Item(iCols)))
Case 2, 3, 46, 71, 75
sbOutputRecord.Append(AppendDelimiter(dr.Item(iCols), "MM/dd/yyyy"))
Case 14 To 21, 25, 35 To 45, 62, 63, 66, 67
sbOutputRecord.Append(AppendDelimiter(dr.Item(iCols), "F2"))
Case 81 To 83 ' I added this case when I compared my prepared output to the manually-produced
' output. The last three fields, even though numeric, were enclosed in double
' quotes. Then when I compared again, there were a handful (maybe 12) records
' that did not have quotes on the very last field, even though all the other
' records did have quotes on this field - in the manual file. So I went ahead
' and loaded, and it was fine despite this difference in representation. So
' the quotes may not have to be put on at all, but seem to be causing no harm.
sbOutputRecord.Append(AppendDelimiter(dr.Item(iCols), "quotes"))
Case 26 ' This is MORT_RATE and can have up to 7 (or more?) significant digits
' Pass it to AppendDelimiter with no formatting and it will retain its original
' value.
sbOutputRecord.Append(AppendDelimiter(dr.Item(iCols)))
Case Else
Dim sbNoCaseMessage As New StringBuilder(32)
sbNoCaseMessage.Append("Processing a column for which there was no Case written.")
sbNoCaseMessage.Append(vbCrLf)
sbNoCaseMessage.Append(vbCrLf)
sbNoCaseMessage.Append(String.Format("Column is {0}", iCols))
sbNoCaseMessage.Append(vbCrLf)
sbNoCaseMessage.Append(vbCrLf)
sbNoCaseMessage.Append("You should never see this message.")
sbNoCaseMessage.Append(vbCrLf)
sbNoCaseMessage.Append("If you do, you need to maintain CPrepare<clientnameclassified>.vb.")
MessageBox.Show(sbNoCaseMessage.ToString)
Return False
End Select
Next
' Remove last delimiter. We don't need it.
sbOutputRecord.Remove(sbOutputRecord.Length - 1, 1)
flOutAnnuityFile.WriteData(sbOutputRecord.ToString)
lRecordCount += 1
sbOutputRecord.Remove(0, sbOutputRecord.Length)
End While
End Using
Thank you so much for your help. Happy New Year!
Re: [RESOLVED] Excel and null data - my ongoing saga
The movie was good. I missed the beginning. I was stuck in traffic in a spot where there never is traffic, because of an accident, which cost me about 20 minutes. I love 3D! I couldn't believe it when my husband said tickets for the four of us were $45! Then I spent another $65 on dinner at TGIFridays! But a night out once in a while is nice and my daughter is a Narnia-nut.
Re: [RESOLVED] Excel and null data - my ongoing saga
Quote:
Originally Posted by
MMock
The movie was good. I missed the beginning. I was stuck in traffic in a spot where there never is traffic, because of an accident, which cost me about 20 minutes. I love 3D! I couldn't believe it when my husband said tickets for the four of us were $45! Then I spent another $65 on dinner at TGIFridays! But a night out once in a while is nice and my daughter is a Narnia-nut.
Good to hear you and your family enjoyed the movie except for missing the start of the movie. Have not seen any movies in 3D yet, guess I need too. Any ways good to hear you have things working now.
