Excel to DGV: Ignore blank cell/row & ignore not start in 98

[zelrick]

Hello VB.net Programmer; Good day Daniweb'ers;

I have a problem to my Excel Data going to DGV; Only Selected items in excel will be put in the DGV;

I don't have a database for this problem;

So here it is; This example from excel file:

    1     2     3     4     5     6
A 98232 31560 80706 8357/ 33302 56999
B 98521 32470 22701 10230 20208 3993/
C 98545 31460 30701 10247 5//// 8422/
D 18630 32470 83205 20245 222// 2//03
E v=sms=sh=globe=2150
F 98653 11458 30075 20242 40127 82817
G     METAR RPVD 31202
H 98618 32570 60000 PVR   AQ    20202
I 98431 V=CEL=PLDT=YA

Things to be done:

1. Like I said before I don't have database.
2. This code get the first two numbers in 1st column ("98")

    For Each row As DataRow In dts.Tables("[Sheet1$]").Rows
    cell1 = row(0).ToString()
    DataGridView1.Rows.Add(cell1.Substring(0, cell1.Length - 3), cell1.Substring(2, cell1.Length - 2))

3. Now I want to get the first two numbers in the 1st column which is ("98") but I want to ignore other row
    that is not started in ("98") and ignore some blank cell.

4. Blank cell get a text "Blank" in the DGV output.

Note: 98 is like a primary key but I don't know was it possible to happen?

The code of Sir Hardz (also a Daniweb Forum Member) help me to get the first 2 numbers in 1st column and what number I need to get other number in other column;

This is the Code:

Dim cell1, cell3, col1, col2, col3, col4 As String
                For Each row As DataRow In dts.Tables("[Sheet1$]").Rows
                    cell1 = row(0).ToString()
                    cell3 = row(2).ToString()
                    col1 = ""
                    col2 = ""
                    col3 = ""
                    col4 = ""
                    If cell1.Length = 5 Then
                        col1 = cell1.Substring(0, cell1.Length - 3)
                        col2 = cell1.Substring(2, cell1.Length - 2)
                    End If
                    If cell3.Length = 5 Then
                        col3 = cell3.Substring(0, cell3.Length - 4)
                        col4 = cell3.Substring(1, cell3.Length - 2)
                    End If
                    DataGridView1.Rows.Add(col1, col2, col3, col4)
                Next

Thank you and God Bless

[Begginnerdev]

Have you tried making use of IsNumeric?

For example:

  For Each row As DataRow In dts.Tables("[Sheet1$]").Rows
        If IsNumeric(row(0)) Then
            If row(0).ToString.StartsWith("98") Then
                Dim cell1, cell3 As String
                Dim col1, col2, col3, col4 As String

                col1 = String.Empty
                col2 = String.Empty
                col3 = String.Empty
                col4 = String.Empty

                cell1 = row(0).ToString
                cell3 = row(2).ToString

                If cell1.Length = 5 Then
                    col1 = cell1.Substring(0, cell1.Length - 3)
                    col2 = cell1.Substring(2, cell1.Length - 2)
                End If

                If cell3.Length = 5 Then
                    col3 = cell3.Substring(0, cell3.Length - 4)
                    col3 = cell3.Substring(1, cell3.Length - 2)
                End If

                DataGridView1.Rows.Add(col1, col2, col3, col4)
            End If
        End If
    Next

[zelrick]

Hello Begginerdev;

Thank you for the code it fix the problem about start with ("98");

can help me to solve about the blank cell?

example:

    1     2     3     4     5     6
A 98123 3//// 80706 8357/ 33302 56999
B 98521       22701 10230 20208 3993/
C 98521 32470 22701 10230 20208 3993/
C 98521 32    22701 10230 20208 3993/

case#1
on the cell(A2) it has string indicated which is "///" can make it the same at it is? on DGV cell it will show "///" in column4

case#2
on the cell(B2) it is blank cell, an error show "length cannot be zero" but instead living it blank I want to put a word inside the cell "Blank" is it possible?

PS:

Case#3
I set a 5digit per cell, what if there is only 3 digit on that cell, I want to put "\" for how many missing digit. It can be done also?

Thank you again.

-Zelrick

[Begginnerdev]

Case #1:
I am not sure that I understand this question completely. By using the code above and the sample data given the 4th column should contain 07 when using

 98123 3//// 80706 8357/ 33302 56999

Is this correct or am I misunderstanding you?

Case #2:
For an empty cell, test using String.IsNullOrWhiteSpace.

For example:

'row(1) will be the second column from the DataSet
If String.IsNullOrWhiteSpace(row(1)) Then
    row(1)="Blank"
End If

Case #3:
You will need to get the total length of the string and then append the backslashes accordingly.

For example:

If cell1.Length < 5 Then
    For i = cell1.Length + 1 To 5
        cell1 &= "\"
    Next
End If

[zelrick]

Hello Begginnerdev;

Sorry for misunderstanding my var; cell3 stands for the second cell (B2) in excel. The 3//// on example will be result in 4th column of DGV is like this "///". In this case, what I need to call is a string but string was not counted in array. (Sorry for this hard explanation). Was trying to say is numerical nor string will be inside of the cell will be like this:

example:
this is excel (A1):

98abc

this is DGV:

Col1   Col2
98     ---

Even string must be called. or can be change for "---". The number of minus (-) depends on the string. (This will be done in all string Case#1, Case#2, Case#3)
String will be change to minus (-) if not numeric; including blank rows/blank cell result be like (-----); including numeric that doesn't reach 5 length result be like (From 3\\) (To 3----);

Thank you for understanding my bad explanation. I'm not good in english nor doing explanation. Sorry about that.

God Bless

-Zelrick

[zelrick]

Hello Begginnerdev;

I've try your code from case#2 to the problem case#1.

'row(1) will be the second column from the DataSet
If String.IsNullOrWhiteSpace(row(1)) Then
row(1)="Blank"
End If

The thing is it add the minus (-) in the left side like this:

my excel is = 8157/

what happen using that code is = -8157

So basically it is very close, thing is I want to replace the string to minus(-)
Example:

1a2b3

Result:

1-2-3

It replace where the string is and if it is not equal to 5 length. The minus(-) will be put on the right side of the number.
Example:

1234

Result:

1234-

wrong result:

-1234

Sorry for the double post. and Thanks again for the code above. It works but a little twist to finish. Will give you a feedback soon.

Thank you again and Godbless.

-Zelrick

[zelrick]

Hello daniwebers;

Giving a feedback; I was wrong about the result of the code is -1234. The result exactly was "-----" it change all to minus. Sorry for my mistake.

Anyone know how to solve the problem;

my excel file is

8157\

the result must be

8157-

Problem in future is:

8b5n\

the result must be

8-5--

All non numerical change to minus(-)

Thanks in advance; God bless; up thread

PS: currently this is my code I'm working on:

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
    Try
        Dim dta As OleDbDataAdapter
        Dim dts As DataSet
        Dim excel As String
        Dim OpenFileDialog1 As New OpenFileDialog

        Dim cell1, cell3, cell8, cell9, col1, col2, col3, col4, col5, col6 As String

        OpenFileDialog1.InitialDirectory = My.Computer.FileSystem.SpecialDirectories.MyDocuments
        OpenFileDialog1.Filter = "All Files (*.*)|*.*|Excel files (*.xlsx)|*.xlsx|CSV Files (*.csv)|*.csv|XLS Files (*.xls)|*xls"

        If (OpenFileDialog1.ShowDialog(Me) = System.Windows.Forms.DialogResult.OK) Then
            Dim fi As New FileInfo(OpenFileDialog1.FileName)
            Dim FileName As String = OpenFileDialog1.FileName

            excel = fi.FullName
            Using conn As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + excel + "; Extended Properties=Excel 12.0;")
                conn.Open()
                dta = New OleDbDataAdapter("Select * from [Sheet1$]", conn)
                dts = New DataSet
                dta.Fill(dts, "[Sheet1$]")


                For Each row As DataRow In dts.Tables("[Sheet1$]").Rows
                    'column name on excel table.
                    'DataGridView1.Rows.Add(value, value.Substring(0, value.Length - 27), value.Substring(2, value.Length - 26), value.Substring(12, value.Length - 28), value.Substring(13, value.Length - 26))

                    'If IsNumeric(row(0)) Then

                    If row(0).ToString.StartsWith("98") Then

                        col1 = ""
                        col2 = ""
                        col3 = ""
                        col4 = ""
                        col5 = ""
                        col6 = ""

                        cell1 = row(0).ToString()
                        cell3 = row(1).ToString()
                        cell8 = row(7).ToString()
                        cell9 = row(8).ToString()
                        '4 columns only

                        If cell1.Length = 5 Then
                            col1 = cell1.Substring(0, cell3.Length - 3)
                            col2 = cell1.Substring(2, cell1.Length - 2)
                        End If

                        If cell3.Length = 5 Then
                            col3 = cell3.Substring(0, cell3.Length - 4)
                            col4 = cell3.Substring(0, cell3.Length - 2)
                        End If

                        If cell8.Length = 5 Then
                            col5 = cell8.Substring(0, cell8.Length - 4)
                        End If

                        If cell9.Length = 5 Then
                            col6 = cell9.Substring(0, cell9.Length - 0)
                        Else
                            Dim i = 0
                            If cell9.Length <> 5 Then
                                For i = cell9.Length + 1 To 5
                                    'Microsoft.VisualBasic.Right(i, 5)
                                    'col6 = cell9.Substring(0, cell9.Length - 0)
                                    'cell9.EndsWith("-")
                                    cell9 &= "-"
                                Next
                            End If

                            End If

                        DataGridView1.Rows.Add(cell1.Substring(0, cell1.Length - 3), cell1.Substring(2, cell1.Length - 2), cell3.Substring(0, cell3.Length - 4), cell3.Substring(1, cell3.Length - 2), cell8.Substring(0, cell8.Length - 4), cell9.Substring(0, cell9.Length - 0))
                    End If
        'End If
                Next
                conn.Close()

            End Using
        End If
    Catch ex As Exception
        MsgBox(ex.Message.ToString)
    End Try

[Begginnerdev]

This function should return the values that you are wishing to see:

Private Function FormatString(ByVal Input As String) As String
    Try
        Dim sTemp As String = Input

        For Each c As Char In sTemp
            If Not IsNumeric(c) Then
                sTemp = sTemp.Replace(c, "-")
            End If
        Next

        If sTemp.Length < 5 Then
            For i = sTemp.Length + 1 To 5
                sTemp &= "-"
            Next
        End If

        Return sTemp
    Catch ex As Exception
        MsgBox(ex.Message, MsgBoxStyle.Critical, "Error in FormatString!")
        Return Input
    End Try
End Function

You can test this function with the following:

Dim sInput As String = "8157\" 'Should be 8157-
Dim sInput2 As String = "8b5n\" 'Should be 8-5--
Dim sInput3 As String = "6n16"  'Should be 6-16-

MsgBox(FormatString(sInput))
MsgBox(FormatString(sInput2))
MsgBox(FormatString(sInput3))

[zelrick]

Hello Sir Begginerdev;

I have an error in Dim sTemp As String = Input; It only show InputString;

Am I doing it right? This is what I do:

If cell8.Length = 5 Then
                                col5 = cell8.Substring(0, cell8.Length - 4)
                            End If

                            If cell9.Length = 5 Then
                                col6 = cell9.Substring(0, cell9.Length - 0)
                            Else
                                Dim i = 0
                                Dim sTemp As String = InputString("", "")

                                'If cell9.Length < 5 Then
                                For Each c As Char In sTemp
                                    If Not IsNumeric(c) Then
                                        sTemp = sTemp.Replace(c, "-")
                                    End If
                                Next

                                If sTemp.Length < 5 Then
                                    For i = cell9.Length + 1 To 5
                                        sTemp &= "-"
                                    Next
                                End If

                                End If

In col9 I insert your code, but everytime I change something the result is blank or error will appear. Please guide me; Thank you; God Bless

[Begginnerdev]

Is the error occuring in the code or is an exception being thrown to you via message box?

If a message box appears, can you please post an image of the error?

Thank you!

[zelrick]

Hello Sir Begginerdev;

the error is in the code sir;

on line9 of my previous code.

Dim sTemp As String = Input 
'this code is error so I try this:

Dim sTemp As String = InputString("", "")
'the String = Input is not applicable. so I try using InputString but the values of the InputString is unknown so I try to make some experiment still the result is blank space in cell of DGV.

Thank you; God bless;

-Zelrick

[zelrick]

PS: Sorry doublepost again (Edit post is not applicable)

In my code InputString("", "")

'This error msgbox:
Conversion from string "" to type 'Integer' is not valid.

Added Question:

At first, I set this numbers as String, why the "\" not counted on the array? 1234\ is 5 strings right? I'm just curious.

Thank you; God Bless;

-Zelrick

[Begginnerdev]

You are receiving the error due to InputString expecting an integer as a parameter.

I may be confusing you here with this function, but if you paste the whole function into your code - you can skip all of the other loops for the data.

This function can be used in your code as follows:

  For Each row As DataRow In dts.Tables("[Sheet1$]").Rows
        If IsNumeric(row(0)) Then
            If row(0).ToString.StartsWith("98") Then
                Dim cell1, cell3 As String
                Dim col1, col2, col3, col4 As String
                col1 = String.Empty
                col2 = String.Empty
                col3 = String.Empty
                col4 = String.Empty
                cell1 = row(0).ToString
                cell3 = row(2).ToString
                If cell1.Length = 5 Then
                    col1 = cell1.Substring(0, cell1.Length - 3)
                    col2 = cell1.Substring(2, cell1.Length - 2)
                End If
                If cell3.Length = 5 Then
                    col3 = cell3.Substring(0, cell3.Length - 4)
                    col3 = cell3.Substring(1, cell3.Length - 2)
                End If

                'If you need to format col1 then.
                col1 = FormatString(col1)
                'For column 2
                col2 = FormatString(col2)

                DataGridView1.Rows.Add(col1, col2, col3, col4)
            End If
        End If
    Next



Private Function FormatString(ByVal Input As String) As String
    Try
        Dim sTemp As String = Input
        For Each c As Char In sTemp
            If Not IsNumeric(c) Then
                sTemp = sTemp.Replace(c, "-")
            End If
        Next
        If sTemp.Length < 5 Then
            For i = sTemp.Length + 1 To 5
                sTemp &= "-"
            Next
        End If
        Return sTemp
    Catch ex As Exception
        MsgBox(ex.Message, MsgBoxStyle.Critical, "Error in FormatString!")
        Return Input
    End Try
End Function