Posts by 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

Hi,

The above sample code is not safe and prone to sql injection. By using a parameterized query:

Dim idate As String = "07/03/2015"
        Dim query As String = "SELECT DISTINCT * FROM workStartTable WHERE StartBy Between @ And @ + ' 23:59:59' "

'your code initialization for connection string, Oledbcommand, etc.
'
'
'assuming that cmd = oledbcommand so:
cmd.Parameters.AddWithValue("StartBy", idate)

Hi,

I think the better way to query a date from MS access database is to use a range or between operator.

Dim idate As String = "07/03/2015"
        'using range
        Dim query As String = "SELECT DISTINCT * FROM workStartTable WHERE StartBy Between #" & idate & "# And #" & idate & " 23:59:59" & "# "

Or you can also use this query:

Dim query As String = "SELECT DISTINCT * FROM workStartTable WHERE StartBy >= #" & idate & "# And StartBy <= #" & idate & " 23:59:59" & "# "

Hardz

Hi,

Just apply the new code to your button click event and remove/comment the old one and see what happened. Because just as I told a while ago, that the old sample indicates a separate path for text file and excel file (but don't worry it is working).

Hi,

I don't know if this code would help, but process.start won't work against delimeted text. Unless, if you use this method: Workbooks.OpenText.

Dim ExcelApp As New Excel.Application
        ExcelApp.Visible = True
        ExcelApp.Workbooks.OpenText(Filename:=lblPath.Text,
            ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False, Comma:=False _
            , Space:=True, Other:=False)
        ExcelApp = Nothing
        GC.Collect()

As Reverend Jim said, it is better to Dispose orphaned excel.exe to avoid memory leak.

Hi,

The sample code above indicates a separate path for text file and excel file. Where in the text file was located based on your sample at C:\Users\abcde\Desktop\anothertest.txt, while I created a .xlsx file and put its value to lblPath or Label2. Maybe you can try this procedure to see if it is working. And I will try your requirements later on.

try remove the $ sign, and create a new excel file, use "sheet1" as default sheet name.

A little tweak using your code, which resulted on the following:

Dim APP As New Excel.Application
    Dim worksheet As Excel.Worksheet
    Dim workbook As Excel.Workbook
    Private Sub Button2_Click(sender As System.Object, e As System.EventArgs) Handles Button2.Click
        workbook = APP.Workbooks.Open(lblPath.Text)
        worksheet = workbook.Worksheets("sheet1")

        Dim i As Integer = 1
        Using MyReader As New Microsoft.VisualBasic.FileIO.TextFieldParser("C:\Users\abcde\Desktop\anothertest.txt")
            MyReader.TextFieldType = FileIO.FieldType.Delimited
            MyReader.SetDelimiters(" ")
            Dim row As String()
            While Not MyReader.EndOfData
                Try
                    row = MyReader.ReadFields()
                    Dim field As String
                    For Each field In row
                        'MsgBox(field)
                        worksheet.Cells(1, i).Value = field
                        i = i + 1
                    Next
                Catch ex As Microsoft.VisualBasic.FileIO.MalformedLineException
                    MsgBox("line " & ex.Message & "is not valid and will be skipped.")
                End Try
            End While
        End Using

        MessageBox.Show("successful...")
        workbook.Save()
        workbook.Close()
        APP.Quit()
        Process.Start("Excel", lblPath.Text)
    End Sub

Private Sub Button2_Click(sender As System.Object, e As System.EventArgs) Handles Button2.Click
        workbook = APP.Workbooks.Open(lblPath.Text) 'path of excel file
        worksheet = workbook.Worksheets("sheet1")

        Dim i As Integer = 1
        ' Split string based on spaces.
        ' using textbox1 for your text input for example, just put this character to textbox1: 12345 12345 abcde abcde 12345
        Dim words As String() = TextBox1.Text.Split(New Char() {" "})
        ' Use For Each loop over words and display them.
        Dim word As String
        For Each word In words
            worksheet.Cells(1, i).Value = word
            i = i + 1
        Next
        MessageBox.Show("successful...")
        workbook.Save()
        workbook.Close()
        APP.Quit()
        Process.Start("Excel", lblPath.Text)
    End Sub

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

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

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.

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.

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

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

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