import csv file into datagridview with specific column and row in vb.net

Question

Please help me guys, its my first time to try importing excel file to datagridview in vb.net. The scenario is that my excel file have so many data and i only need to import the value starting in column B to column I, and start rows must be in column B with the word "START" and ends in the row with the word "END". So far this was my code:

 Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
    OpenFileDialog1.InitialDirectory = My.Computer.FileSystem.SpecialDirectories.Desktop
    OpenFileDialog1.Filter = "CSV Files (*.csv)|*.csv"

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

        Try
            Dim fi As New FileInfo(OpenFileDialog1.FileName)
            Dim sConnectionStringz As String = "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=Text;Data Source=" & fi.DirectoryName
            Dim objConn As New OleDbConnection(sConnectionStringz)
            objConn.Open()
            'DataGridView1.TabIndex = 1
            Dim objCmdSelect As New OleDbCommand("SELECT * FROM " & fi.Name, objConn)
            Dim objAdapter1 As New OleDbDataAdapter
            objAdapter1.SelectCommand = objCmdSelect
            Dim objDataset1 As New DataSet
            objAdapter1.Fill(objDataset1)
            '--objAdapter1.Update(objDataset1) '--updating
            DataGridView1.DataSource = objDataset1.Tables(0).DefaultView

            objConn.Close()
        Finally


        End Try


    End If
End Sub

And this code imports/shows all the data in datagridview. I already search everywhere but nothing seems to fit. Thanks anyways..

This was the sample file:

CLIENT-RD/OT HC 8   7   8   10  11  12  10
C-RD/OT 6:00    0   0   0   0   0   0   0
C-RD/OT 7:30    0   0   0   0   0   0   0
C-RD/OT 8:00    0   0   0   0   0   0   0
C-RD/OT 9:00    8   7   8   10  11  12  10
C-RD/OT 10:00   0   0   0   0   0   0   0
C-RD/OT 12:00   0   0   0   0   0   0   0
C-RD/OT 1:30    0   0   0   0   0   0   0

ML      0   0   0   0   0   0   0
VL/SIL  2   4   1   1   1   2   2
SL         1    0   1   1   1   0   0

START                   20-Jan  21-Jan  22-Jan  23-Jan  24-Jan  25-Jan  26-Jan
CEBU                     MON    TUE      WED    THU       FRI       SAT   SUN
Team Edna - Esca                            
Adarayan, Dianne          RD    RD  6:00 AM 6:00 AM 6:00 AM 6:00 AM 1:30 PM
Bacalla, Catherine        VL    VL  RD/OT 7:30  7:30 AM 7:30 AM 7:30 AM RD
Dela Cruz, Mary Ann       6:00 AM   7:30 AM RD  RD  7:30 AM 7:30 AM 6:00 AM
Dumana, John Carlos       1:30 PM   1:30 PM 1:30 PM RD  RD  1:30 PM 1:30 PM
Macabodbod, Evelyn        9:00 AM   9:00 AM 9:00 AM 9:00 AM RD  RD/OT 9:00  9:00 AM
Mariano, Kristine         1:30 PM   RD/OT 1:30  1:30 PM 1:30 PM 1:30 PM RD  7:30 AM
Santiago, Gretchen        RD/OT 7:30    6:00 AM 7:30 AM 7:30 AM 1:30 PM 1:30 PM RD
END

first, dont do SELECT * specify the columns you want, namely those that relate to Columns B thru column I. that takes care of the column filtering. For the rows, you might have to preprocess the CSV to move the lines between START and END to a new temp file that is loaded. Does START AND END represent something else like a data range? Edit your Post to add a LITTLE of the CSV as it is near to impossible to do data analysis with no data. — Ňɏssa Pøngjǣrdenlarp
– Ňɏssa Pøngjǣrdenlarp, Commented Jan 23, 2014 at 1:07
if this is coming from Excel, why not extract and export the exact range you need rather than doing it in code? Also, that does not look like a CSV - there are no Cs. You might have to open it as text and extract the rows you want to a temp file. — Ňɏssa Pøngjǣrdenlarp
– Ňɏssa Pøngjǣrdenlarp, Commented Jan 23, 2014 at 1:45
The schedules are in .xls type file and the data i showed above is just a piece of all the data's in that file. My boss don't want to extract the data or columns/ rows anymore, rather she just wanted to upload the .xls file and let the system do it. — Aljie
– Aljie, Commented Jan 23, 2014 at 2:27

rayncorg · Accepted Answer · 2014-01-23 05:05:10Z

1

try this:

Dim objCmdSelect As New OleDbCommand("SELECT * FROM [Sheet1$B14:I24]", objConn)

lets assume B14 is your starting ROW and I24 is your last column/row record.

UPDATED:

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
    OpenFileDialog1.InitialDirectory = My.Computer.FileSystem.SpecialDirectories.Desktop
    OpenFileDialog1.Filter = "CSV Files (*.csv)|*.csv"

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

        Try
            Dim fi As New FileInfo(OpenFileDialog1.FileName)
            Dim sConnectionStringz As String = "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=Text;Data Source=" & fi.DirectoryName & ";" & "Extended Properties=""Excel 8.0;HDR=NO;IMEX=1"""
            Dim objConn As New OleDbConnection(sConnectionStringz)
            objConn.Open()

            Dim objAdapter1 As New OleDbDataAdapter("SELECT * FROM [Sheet1$B14:I24]", objConn)

            Dim objDataset1 As New DataSet
            objAdapter1.Fill(objDataset1)

            DataGridView1.DataSource = objDataset1.Tables(0).DefaultView

            objConn.Close()
        Finally


        End Try


    End If
End Sub

edited Jan 23, 2014 at 5:05

answered Jan 23, 2014 at 3:56

rayncorg

9934 gold badges17 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

11 Comments

Aljie Over a year ago

Sorry but i got this error: "The Microsoft Jet database engine could not find the object 'Sheet1$B14_I24.txt'. Make sure the object exists and that you spell its name and the path name correctly." The csv file is coming from the openDialogbox.

rayncorg Over a year ago

will you please use this... Dim objAdapter1 As New OleDbDataAdapter("SELECT * FROM [Sheet1$B14:I24]", objConn) skip OledbCommand instead directly use OledbDataAdapter just a try..

rayncorg Over a year ago

@Aljie Please see my updated post.. i add some properties in your connection string.

Aljie Over a year ago

Thank you for your effort sir, i use your code and got this error message: "The Microsoft Jet database engine cannot open the file 'C:\Documents and Settings\Administrator\Desktop'. It is already opened exclusively by another user, or you need permission to view its data."

rayncorg Over a year ago

open task manager>services then end process all 'EXCEL' then run your app.

|

Collectives™ on Stack Overflow

import csv file into datagridview with specific column and row in vb.net

1 Answer 1

11 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

11 Comments

Your Answer

Sign up or log in

Post as a guest

Related