Solved

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

Posted on 2006-07-05
17
1,817 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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 

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
 

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

Is Your AD Toolbox Looking More Like a Toybox?

Managing Active Directory can get complicated.  Often, the native tools for managing AD are just not up to the task.  The largest Active Directory installations in the world have relied on one tool to manage their day-to-day administration tasks: Hyena. Start your trial today.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Help with Query not working in client's PC 1 37
C# Single Form 8 42
VB.net Move a class from Solution Items to a project 2 23
get row value in vb.net 4 14
Creating an analog clock UserControl seems fairly straight forward.  It is, after all, essentially just a circle with several lines in it!  Two common approaches for rendering an analog clock typically involve either manually calculating points with…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

803 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