Solved

How do I replace blank spaces with a value of 0 or null?

Posted on 2006-07-05
17
1,804 Views
Last Modified: 2008-02-01
I am writing a program to import information from an Excel spreadsheet and insert the data into a SQL Server database table.  The problem I am faced with here is that the column CAT has blank spaces in the Excel sheet.  How do I replace the blank space value with a 0 or null in VB.NET?  The first record that I tried worked fine because in the column CAT from the Excel spreadsheet, I put in a 0.  But how would I replace the blank values with a 0 or null if I was faced with a spreadsheet that gave me that type of problem using my Insert or doing something with the Insert string in my code?  Below is my code for the program.

Function ReadExcelsheet(ByVal Path As String, Optional ByVal Sheetvar As String = "Sheet1") As System.Data.DataSet

        Dim ds As New DataSet
        Dim da As New OleDbDataAdapter
        Dim conn_string As OleDbConnection
        Dim excel_cmd As New OleDbCommand
        Dim dt As New System.Data.DataTable

        Dim sql_ds As New DataSet
        Dim sql_dbadapter As New SqlDataAdapter
        Dim conn_sql As SqlConnection
        Dim sql_cmd As New SqlCommand
        Dim sql_dt As New System.Data.DataTable
        Dim cmdbuilder As New SqlCommandBuilder
        Dim tester As String





        'Make sure the text box has a string to find an Excel spreadsheet
        If TextBox1.Text = "" Or ComboBox1.Text = "" Then
            MsgBox("No spreadsheets have been loaded yet or no tables have been selected.")
            Exit Function
        Else
            'This is where the connection string is used

            Path = TextBox1.Text
            conn_string = New OleDbConnection( _
                     "Provider= Microsoft.Jet.OLEDB.4.0;" & _
                     "Data Source =" & Path & ";" & _
                     "Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";")

            'This is where the connection string reaches the SQL Server database
            conn_sql = New SqlConnection( _
    "Data Source=BRS-5C2ULHTC9ZV;Initial Catalog=NIPWork;Integrated Security=True")

            'The command connecting to a database
            excel_cmd.Connection = conn_string


            'The command string's command text
            excel_cmd.CommandText = "SELECT * FROM [" & Sheetvar & "]"

            sql_cmd.Connection = conn_sql

            'sql_cmd.CommandText = "SELECT * FROM jhirniak.NIP_Valuation"

            ' sql_cmd.CommandText = "SELECT * FROM test_nip_valuation_nonproduction"



            'da = New OleDbDataAdapter("SELECT * FROM [" & Sheet & "$]", conn_string)
            'Since the OleDbCommand stores the connection string and the command text, the line below
            'is the same as the line above.
            da.SelectCommand = excel_cmd

            'Open the connection string
            conn_string.Open()
            If conn_string.State = ConnectionState.Open Then
                MsgBox("The connection string has connected to the source.")
            Else
                MsgBox("Unable to connect")
            End If

            sql_dbadapter.SelectCommand = sql_cmd



            Try
                conn_sql.Open()
            Catch ex As Exception
                MsgBox("Unable to connect to the SQL Server database", MsgBoxStyle.Critical, "Error!")
                Exit Function
            End Try
            'Checks to see if the sql connection is open or not
            If conn_sql.State = ConnectionState.Open Then
                MsgBox("The SQL Connection is now Open")
            Else
                MsgBox("No SQL database connection was made.")
            End If



            'The data adapter fills the dataset with the table information from the database
            da.Fill(ds)
            ' da.Fill(ds, "Test_NIP_Valuation_nonproduction")
            da.Fill(dt)

            'The Sql server data adapter fills the dataset with the table information from the database
            ' sql_dbadapter.Fill(sql_ds)
            'The Sql server dataadapter fills the data table
            ' sql_dbadapter.Fill(sql_dt)





            Dim row As DataRow

            Dim retInt As Integer
            Dim count As Integer = 0
            For Each row In ds.Tables(0).Rows

                If Not row.Item("CAT").ToString = " " Then
                    row.Item("CAT").ToString.Insert(0, 0)
                End If

                tester = "insert into tblNIPIMPORT_Valuation (LineofBusiness, PolicyNO, OccuranceNO, PolOccNo, " & _
                   "PolComm, LossDay, CAT, RiskState, FirstNotice, ValnMo, AccYr, PdIndem, PdALAE, " & _
                  "OSIndem, OSALAE, IncdIndem, IncdALAE, IncdLandALAE, Insured, LossDescription) " & _
                 "values('" & row.Item("LOB").ToString & "', '" & row.Item("Pol #").ToString & "', '" & _
                row.Item("Occ #").ToString & "', '" & row.Item("Pol Occ #").ToString & "', '" & _
                 row.Item("Pol Comm").ToString & "', '" & row.Item("Loss Day").ToString & "', " & _
                row.Item("CAT").ToString & ", " & _
                row.Item("RiskState").ToString & ", '" & row.Item("First Notice").ToString & "', " & _
                row.Item("Valn Mo").ToString & ", " & row.Item("Acc Yr").ToString & ", " & _
                row.Item("PdIndem").ToString & ", " & row.Item("PdALAE").ToString & ", " & _
                row.Item("OS Indem").ToString & ", " & row.Item("OS ALAE").ToString & ", " & _
                row.Item("IncdIndem").ToString & ", " & row.Item("IncdALAE").ToString & ", " & _
                row.Item("Incd L&ALAE").ToString & ", '" & row.Item("Insured").ToString & "', '" & _
                row.Item("Loss Description") & "')"









                sql_cmd = New SqlCommand(tester, conn_sql)

                sql_cmd.ExecuteNonQuery()
                If retInt = 1 Then
                    count = count + 1
                    Continue For
                End If
                'build update
                MsgBox(tester)
                'row.SetAdded()
                count = count + 1
            Next



        End If


        ' sql_dbadapter.Update(sql_ds, tester)




        sql_dbadapter.Update(sql_ds)




        ReadExcelsheet = ds
        DataGridView1.DataSource = dt
        DataGridView2.DataSource = sql_dt
        ' UpdateSQLTable(ds)
        conn_string.Close()
    End Function
0
Comment
Question by:VBBRett
  • 6
  • 4
  • 3
  • +2
17 Comments
 
LVL 44

Expert Comment

by:bruintje
ID: 17048504
Hi VBBRett,
----------

not sure if i understand exactly what you mean but if you are faced with blanks or spaces in the CAT cell

instead of

If Not row.Item("CAT").ToString = " " Then
    row.Item("CAT").ToString.Insert(0, 0)
End If

you trim the cell and see if you're left with nothing then insert a 0

If row.Item("CAT").Trim().ToString = "" Then
    row.Item("CAT").ToString.Insert(0, 0)
End If

----------
bruintje
share what you know, learn what you don't
0
 

Author Comment

by:VBBRett
ID: 17048541
It appears that your code block and my code block of if statements don't even effect the Insert string that I have here..

tester = "insert into tblNIPIMPORT_Valuation (LineofBusiness, PolicyNO, OccuranceNO, PolOccNo, " & _
                   "PolComm, LossDay, CAT, RiskState, FirstNotice, ValnMo, AccYr, PdIndem, PdALAE, " & _
                  "OSIndem, OSALAE, IncdIndem, IncdALAE, IncdLandALAE, Insured, LossDescription) " & _
                 "values('" & row.Item("LOB").ToString & "', '" & row.Item("Pol #").ToString & "', '" & _
                row.Item("Occ #").ToString & "', '" & row.Item("Pol Occ #").ToString & "', '" & _
                 row.Item("Pol Comm").ToString & "', '" & row.Item("Loss Day").ToString & "', " & _
                row.Item("CAT").ToString & ", " & _
                row.Item("RiskState").ToString & ", '" & row.Item("First Notice").ToString & "', " & _
                row.Item("Valn Mo").ToString & ", " & row.Item("Acc Yr").ToString & ", " & _
                row.Item("PdIndem").ToString & ", " & row.Item("PdALAE").ToString & ", " & _
                row.Item("OS Indem").ToString & ", " & row.Item("OS ALAE").ToString & ", " & _
                row.Item("IncdIndem").ToString & ", " & row.Item("IncdALAE").ToString & ", " & _
                row.Item("Incd L&ALAE").ToString & ", '" & row.Item("Insured").ToString & "', '" & _
                row.Item("Loss Description") & "')"

So my question to you would be, how would I create a conditional statement to make row.Item("CAT").ToString equal to zero if there are no values from the spreadsheet for that particular cell?
0
 
LVL 44

Expert Comment

by:bruintje
ID: 17048568
i tried this

    Dim str As String = "  "
    If str.ToString().Trim() = "" Then
      str = 0
    End If

str became "0"

so you can try for the cell something like

If row.Item("CAT").Value.ToString().Trim() = "" Then
    row.Item("CAT").Value = 0
End If

if you step through it does it come by the line >row.Item("CAT").Value = 0
0
 

Author Comment

by:VBBRett
ID: 17048669
Hi, I'm using VB.NET 2005.  It still seems to not work.  As I step through, it does not replace the row.Item("CAT") with zero.  
0
 

Author Comment

by:VBBRett
ID: 17048673
By the way, I'm using Visual Studio 2005 and I'm sure that would make some kind of difference.
0
 

Author Comment

by:VBBRett
ID: 17048713
What is an alternative?  How would I insert 0s into the spreadsheet itself?  is there a way I can do that?
0
 
LVL 44

Expert Comment

by:bruintje
ID: 17048774
i thought you already where doing it directly into the sheet :) but pasting the code in my vb express showed that you tried to set the cell

before i try to read in an excel sheet myself can you try

                If Not row.Item("CAT").ToString.Trim = "" Then
                    row.Item("CAT").ToString.Replace(" ", "0")
                End If
0
 
LVL 19

Expert Comment

by:arif_eqbal
ID: 17048822
Try

            If Dr.Item("CAT").ToString.Trim.Length > 1 Then
 Or    
            If String.IsNullOrEmpty(Dr.Item("CAT").ToString.Trim) Then
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:VBBRett
ID: 17048870
Still nothing.  Wow, this is annoying..lol.  Should I post my code?  Do you think that would help?
0
 
LVL 44

Expert Comment

by:bruintje
ID: 17048878
sorry had to take the not out of the if line

                If ro   w.Item("CAT").ToString.Trim = "" Then
                    row.Item("CAT").ToString.Replace(" ", "0")
                End If

i would be not surprised if a simple replace will not work ;)
0
 

Author Comment

by:VBBRett
ID: 17048919
Still did not work.  I don't know what is going on here with my program, but this is more difficult then it should be.
0
 
LVL 34

Accepted Solution

by:
Sancler earned 500 total points
ID: 17049060
Your code would be much easier to read, and would perform much more efficiently, if you used parameters.  Rather than doing this extensive string concatenation for each row, you could code the commandtext, and the parameters, before you start to loop through the rows and just add the parameter values within the loop.  On these lines

   dim cmd As New SQLCommand
   cmd.Connection = con_SQL
   cmd.CommandText = "insert into tblNIPIMPORT_Valuation (LineofBusiness, PolicyNO, OccuranceNO, PolOccNo, " & _
                   "PolComm, LossDay, CAT, RiskState, FirstNotice, ValnMo, AccYr, PdIndem, PdALAE, " & _
                  "OSIndem, OSALAE, IncdIndem, IncdALAE, IncdLandALAE, Insured, LossDescription) " & _
                 "values(@LineofBusiness, @PolicyNO, @OccuranceNO, @PolOccNo, @PolComm, @LossDay, @CAT, @RiskState, @FirstNotice, @ValnMo, @AccYr, @PdIndem, @PdALAE, @OSIndem, @OSALAE, @IncdIndem, @IncdALAE, @IncdLandALAE, @Insured, @LossDescription)"
   cmd.Parameters.Add("@LineofBusiness", SqlDbType.NVarChar)
   'etc, for all other parameters

and then, within the loop

   cmd.Parameters("@LineofBusiness").Value = row.Item("LOB").ToString
   'etc, for all other parameters/values
   cmd.ExecuteNonQuery

That is a general comment, rather than addressing this specific problem.

But, in the context of that approach, the line for

   cmd.Parameters("@Cat") ...

would become something like

   If row.Item("CAT") = "" Then
       cmd.Parameters("@Cat").Value = "0"
   Else
       cmd.Parameters("@Cat").Value = row.Item("CAT")
   End If

I say "something like" because I am not sure what the value is in row.Item("CAT") that is producing the problem: i.e. whether it is an empty string, or a space, or Null, or what.  And I'm not sure whether you want to replace it with "0" or with Null.

Roger
0
 
LVL 24

Expert Comment

by:Jeff Certain
ID: 17050669
Let me throw my two cents worth in here, too....

System.DBNull is a tough beast to master. It doesn't cast nicely to any primitive, so you're really best to avoid it when you can.

If I recall correctly, .ToString doesn't work well on DBNull either.

Assuming you're using a T-SQL 92 compliant database engine (like SQL server), you can deal with the NULL at the database level... replace Cat in your query with ISNULL(Cat,0).
0
 
LVL 34

Expert Comment

by:Sancler
ID: 17050842
Hiya, Jeff

He's bringing in the data from Excel with an OleDb Jet Driver.

Rgoer
0
 
LVL 34

Expert Comment

by:Sancler
ID: 17050846
Or Roger, even ;-)
0
 
LVL 24

Expert Comment

by:Jeff Certain
ID: 17050936
Oh.. I knew that. LOL.

The dreaded Nz function then:

Public Shared Function Nz(value as Object, default as Integer) As Integer
  If value Is System.DBNull.Value Then
    Return default
  Else
    Return Integer.Parse(value)
  End If
End Function

Then replace row("Cat") with Nz(row("Cat"),0)

You'll need to create different overloads of this depending on the type you want to clean up. You may also want to do some validation, such as If IsNumeric(value).

0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

This tutorial demonstrates one way to create an application that runs without any Forms but still has a GUI presence via an Icon in the System Tray. The magic lies in Inheriting from the ApplicationContext Class and passing that to Application.Ru…
The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now