Get single number of a whole number

[zelrick]

Please help me, I'm not really good in programming.

I've done converting text file going to excel, and excel going to DataGridView.

Here's my problem:

I want to get the specific one or two numbers (maybe up to three).

example:

    98321 12345 67890 54321 09876 'this numbers in excel. (First row)
    89123 51234 06789 15432 60987 '(Second row)

    I want to get "98" in that first column going to DataGridView in 1 cell;
    and "321" in the 2nd cell. Then skip to second column which is "67890";
    same: I want to get "6" to 3rd cell then "789" in 4th cell.

    Then the second row will follow what happen in first row.

I really don't know if VB.Net can do this alone or need C# to this problem.

-Zelrick-

[ddanbe]

VB.Net can handle this just as C# can.
Your explanation is a bit unclear. On line 10 by "6",do you mean "67"?
The SubString method of the string class can help you out here.

[zelrick]

To: ddanbe

On line 10 sir, "6" was on 3rd column of the excel and also "789" will be on new cell. it skips the 2nd column.

refering with my example the result was this:

98 321 6 789 'first row
89 123 0 678 'second row

the spaces stands for a cell in DataGridView.

Please help me, or give me atleast a example codes for my basis.

[ddanbe]

If you are having trouble reading an excel document from vb.net, read this.

[Hardz]

Hi zelrick,

ddanbe provided you a great answer (link) to your problem.

string[] exdata = { "98321 12345 67890 54321 09876", "89123 51234 06789 15432 60987" };
            foreach (string value in exdata)
            {
                dataGridView1.Rows.Add(value.Substring(0, value.Length - 27),
                        value.Substring(2, value.Length - 26),
                        value.Substring(12, value.Length - 28),
                        value.Substring(13, value.Length - 26));
            }

Hardz

[Hardz]

Sorry, I thought I'm in a c# thread.. :)

Dim exdata As String() = {"98321 12345 67890 54321 09876", "89123 51234 06789 15432 60987"}
        For Each value As String In exdata
            DataGridView1.Rows.Add(value.Substring(0, value.Length - 27), value.Substring(2, value.Length - 26), value.Substring(12, value.Length - 28), value.Substring(13, value.Length - 26))
        Next

Hardz

[ddanbe]

@Hardz: At least you proved that it can work in both languages. :)

[zelrick]

Thank you very much Sir ddanbe and sir Hardz;

Few more questions sir,

Dim exdata As String() = {"98321 12345 67890 54321 09876", "89123 51234 06789 15432 60987"}

'can I use this code if I base on a Excel file?
'I'm exporting an Excel file to DataGridView.
'can help me how?

[ddanbe]

I would rather process one excel cell, instead of first reading in a whole excel row and concatenating the cell values as string an then start to process the string. But that depends upon you, the programmer.

[Hardz]

Hi Zelrick,

Yes, ddanbe's suggestion is right: "I would rather process one excel cell, instead of first reading in a whole excel row and concatenating the cell values as string an then start to process the string". Anyway, Please see this link on how to import excel file to dgv: import data from excel to Dgv. If the code above is combined with this one may result with the following code:

Imports System.Data.OleDb
Imports System.IO

Public Class Form1

    Private Sub BtnExport_Click(sender As System.Object, e As System.EventArgs) Handles BtnExport.Click
        Dim dta As OleDbDataAdapter
        Dim dts As DataSet
        Dim excel As String
        Dim OpenFileDialog As New OpenFileDialog
        Dim value As String

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

        If (OpenFileDialog.ShowDialog(Me) = System.Windows.Forms.DialogResult.OK) Then

            Dim fi As New FileInfo(OpenFileDialog.FileName)
            Dim FileName As String = OpenFileDialog.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
                    value = row("ColumnName").ToString() 'column name on excel table
                    'assuming there are five columns in a table including the original value
                    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))
                Next
            End Using
        End If
    End Sub
End Class

Hardz

[zelrick]

Sorry for the late reply Sir ddanbe and Sir Hardz,

Will try the code that you give Sir Hardz, and thank you for the link. I was gonna ask that too soon, coz once I save a Excel file the Sheet1 also change so thumbs up to you Sir.

Will gonna give you a feedback soon; Trying the code;

[zelrick]

Sir Hardz,

I'm having an error with the ("ColumnName") so I change it to (0), then the next error goes in #31 to #35 of your code. Please check mine what is wrong.

Here is the code:

Imports System.Data.OleDb
Imports System.IO
Imports Excel = Microsoft.Office.Interop.Excel

Public Class Form2

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 value 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
                    value = row(0).ToString()
                    'column name on excel table.
                    'assuming there are five columns in a table including the original value.
                    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))

                Next
                conn.Close()

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

Am I wrong to make the ("ColumnName") to (0)?

-Zelrick

[Hardz]

Hi,

Am I wrong to make the ("ColumnName") to (0)?

changing it to 0 is a right way, knowing that row 1 at you excel file has no direct column name but a data instead. By the way, just add 5 columns to your DGV, since the code sample consists of 5, to see what the result is.

[zelrick]

Sir Hardz,

I do add 5 column in DGV, naming Column1, Column2,.. Column5.

still error is "Length cannot be less than zero Parameter name:length

PS: this Column1 up to Column5 is the Column header if I'm not mistaken.

[Hardz]

Hi,

are you sure that all of your data in excel have this kind of format:
'xxxxx xxxxx xxxxx xxxxx xxxxx'.
Because if not that error will occur.

[zelrick]

Sir Hardz,

Yes Sir, I copied my example to a excel format is .xlsx format.

xxxxx xxxxx xxxxx xxxxx xxxxx
xxxxx xxxxx xxxxx xxxxx xxxxx

[Hardz]

Hi,

I have no problem running this code without error. Maybe you can try this sample data and save it on new excel file, then run and let's see if there's still an error:

Number Format
98321 12345 67890 54321 09876
89123 51234 06789 15432 60987
78910 51234 47896 15432 60987
88995 51234 55663 15432 60987
12345 51234 67891 15432 60987

[zelrick]

Sir Hardz,

Still error Sir, check this image Sir.

[ddanbe]

The error comes probably from the Substring methods on line 32 of the most recent code.(Length of data value -27 while data has length 5) This still seems code from when the row was a whole string. Also remember that DGV has a datasource property too.

[zelrick]

Sir ddanbe,

But it seems working fine to Sir Hardz,

For Each row As DataRow In dts.Tables("[Sheet1$]").Rows
                    'value = row(0).ToString()
                    'column name on excel table.
                    'assuming there are five columns in a table including the original value.
                    '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))

                    DataGridView1.DataSource = dts.Tables(0)

                    Me.DataGridView1.Rows(1).Selected = False

I comment first the substring portion but my previous code is just transfering the excel to DGV. This Substring is a pain. But now atleast I got an idea how this numbers will be seprated. Only thing now is How to construct it.

[ddanbe]

What I usually do in cases likes this is:

range = xlWorkSheet.UsedRange

        For rCnt = 1 To range.Rows.Count
            For cCnt = 1 To range.Columns.Count
                Obj = CType(range.Cells(rCnt, cCnt), Excel.Range)
                MsgBox(Obj.value)
                'do something here with substring or whatever in DGV
            Next
        Next

Look here for the full code.

[Reverend Jim]

Just a thought - I think you would be better off parsing the numbers as integers rather than strings. To get the two "parts" of a number you can do

num Mod 1000   'for num = 98765 this results in 765
num \ 1000     'for num = 98765 this results in 98

This works for any positive integer and you don't have to worry about substring errors.

[ddanbe]

@Jim: Although I find this a great thought, a DGV, by default consists of TextBoxColumn. So some kind of conversion has to happen anyway.

[Reverend Jim]

I realize that but

Dim num As Integer = CInt(some string)
Dim pt1 As Integer = num Mod 1000
Dim pt2 As Integer = num \ 1000

is clearer than substringing since it's a numerical problem, and you don't have to account for varying numbers of digits (and calculating string lengths and offsets). The code could be put into a Function which would also check to make sure the string is an integer value.

[Hardz]

Hi,

Sorry, I misunderstood the problem, an apology to you guys especially for zelrick. :). I never thought that the given data in excel is per cell, I just thought that it is a one line numerical characters. :)

Anyway, substing method could still do that, so by just modifying the code before, here is the result:

Dim cell1, cell3 As String
                    For Each row As DataRow In dts.Tables("[Sheet1$]").Rows
                        cell1 = row(0).ToString()
                        cell3 = row(2).ToString()
                        '4 columns only
                        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))
                    Next

Thanks,

Hardz

[ddanbe]

@Jim:This would indeed make things clearer in this case.

[zelrick]

Thank you Sir Hardz, Sir ddanbe, Sir Reverend Jim

my question is answered. Sir Hardz the code works. It solves the mystery.

I hope you guys help me more in future.

Thanks again, and God Bless us all.

[zelrick]

Sir Hardz, Sir Reverend Jim, Sir ddanbe;

Hello Good day,

I'm sorry for interupt again on this topic, just now I encounter error that I don't know how to solve it.

I got the error again about length cannot be zero.

So I figure what error I get;

My Excel File (.xlsx) give gap in row, example:

*This is a Excel File.*
    1     2     3     4     5
A 98321 12345 67890 54321 29876
B 89123 51234 26789 15432 60987
C
D 12345 12345 12345 12345 12345
E 54321 54321 54321 54321 54321
F
G 98321 12345       54321 29876
H 98321 12345 67890 54321 29876

The Error is about the gap of the row. The row C & F is blank and row G have blank cell(s).

This gap not just one it can be many? Please help me again.

I don't know how to ignore blank cell(s) or can be the whole row.

(updated)
PS: blank row/blank column/blank cell(s)

-Zelrick

[Hardz]

Hi Zelrick,

I just thought that the data that you had provided is in a standard form that consists of 5 digits numerical number. Yes of course an error would occur if there's a blank or the cell length is less than the specified length, because of its calculation to determine the location of the character. But by using a conditional statement, we can do it this way:

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

The only problem with this code if the cell length is less than or more than 5. But I think it is another problem and out of scope for this discussion that needs for creation to a new thread, so that others can help and contribute.

Thanks,

Hardz

[zelrick]

Hello Sir Hardz,

Sorry for being out of the topic. Will be creating a new thread for alternative solution to the problem.

Thank you Sir Hardz.