none
VB.net import some columns (not all) from csv in datagridview RRS feed

  • Question

  • Hi,

    I'm sorry, I'm not very expert to VB.NET.

    I'm trying to import only any columns from CSV to DataGridView using this code:

    Dim fName As String = ""
    OpenFileDialog1.InitialDirectory = "c:\desktop"   
    OpenFileDialog1.Filter = "CSV files(*.csv)|*.csv"        
    OpenFileDialog1.FilterIndex = 2        
    OpenFileDialog1.RestoreDirectory = True        
    If (OpenFileDialog1.ShowDialog() = Windows.Forms.DialogResult.OK) Then            
        fName = OpenFileDialog1.FileName        
    End If        
    
    txtpathfile.Text = fName        
    Dim TextLine As String = ""  
    Dim SplitLine() As String        
    If System.IO.File.Exists(fName) = True Then            
       Dim objReader As New 
       System.IO.StreamReader(txtpathfile.Text, Encoding.ASCII)            
       Me.DataGridView1.ColumnCount = 10            
       Me.DataGridView1.ColumnHeadersVisible = True            
       Me.DataGridView1.Columns(0).Name = "Username"            
       Me.DataGridView1.Columns(1).Name = "IP"            
       Me.DataGridView1.Columns(2).Name = "Location"            
       
       Do 
         While objReader.Peek() <> -1                
         TextLine = 
         objReader.ReadLine()                
         SplitLine = Split(TextLine, ",")                
         Me.DataGridView1.Rows.Add(SplitLine)            
       Loop        
    Else            
       MsgBox("File Does Not Exist")        
    End If


    But this code, import all columns from CSV and the first row is the column header as below:

    

    How can modify my code, to insert only columns 1, 2 and 5 for example, and the first row use as columns header?

    Thanks for the support.

    Marco

    Wednesday, March 11, 2020 10:02 AM

All replies

  • Hello Marco,

    Try this, only take elements of the string array you want by ordinal index so 1 will be 0, 2 is 1etc. If you know there are always enough elements then the Length check may be omitted.

    SplitLine = Split(TextLine, ",")
    If SplitLine.Length > 5 Then
        DataGridView1.Rows.Add(New Object() {SplitLine(0), SplitLine(1), SplitLine(4)})
    End If
    You will need to have three columns defined in the DataGridView first (which I assume you have done already). 


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Wednesday, March 11, 2020 10:48 AM
    Moderator
  • Hi Balacche4,

    This is my sample.

      Dim textFile As FileIO.TextFieldParser ' -- input file
      ' --- open input file
      textFile = New FileIO.TextFieldParser(fName)  ' -- fName should be given by OpenFileDialog 
      ' --- define delimiter
      textFile.TextFieldType = FileIO.FieldType.Delimited
      textFile.SetDelimiters(",")
      ' --- store each column of one line into DataGridView
      Dim currentRow As String() ' -- array
      Dim myRow As Integer = 0
      Dim myCol As Integer = 0
      ' --- Row Loop
      While Not textFile.EndOfData
        Me.DataGridView1.Rows.Add() ' -- add one Row in DataGridView
        currentRow = textFile.ReadFields() ' -- store one line to array
        Dim currentColumn As String
        ' --- Column Loop
        For Each currentColumn In currentRow
          Me.DataGridView1(myCol, myRow).Value = currentColumn
          myCol += 1
        Next
        myCol = 0
        myRow += 1
      End While
      ' --- close input file
      textFile.Close()
      ' ---
      Me.DataGridView1.Rows.RemoveAt(0) ' -- remove 1st row (remove header)

    Regards,


    Ashidacchi -- http://hokusosha.com

    Thursday, March 12, 2020 12:35 AM
  • Hi,

    Is the effect that you want?

    You can try the code I provided below:

    Imports System.Data.OleDb
    Imports System.IO
    
    Public Class Form1
    
        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
            Dim filename As String = ""
            OpenFileDialog1.InitialDirectory = "E:\"
            OpenFileDialog1.Filter = "CSV files(*.csv)|*.csv"
            OpenFileDialog1.FilterIndex = 2
            OpenFileDialog1.RestoreDirectory = True
            If (OpenFileDialog1.ShowDialog() = Windows.Forms.DialogResult.OK) Then
                filename = OpenFileDialog1.FileName
                txtpathfile.Text = filename
            Else
                Return
            End If
            Dim file As New FileInfo(filename)
            Dim connectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=Text;Data Source=" & file.DirectoryName
    
            Using conn As New OleDbConnection(connectionString)
                conn.Open()
                Dim cmd As New OleDbCommand("SELECT time,part,final FROM " & file.Name, conn)
                Dim adapter As New OleDbDataAdapter
                adapter.SelectCommand = cmd
                Dim dt As New DataTable
                adapter.Fill(dt)
                Dim dr As DataRow = Nothing
                If dt Is Nothing Then Return
    
                dt.Columns("time").ColumnName = "time1"
                dt.Columns("part").ColumnName = "part2"
                dt.Columns("final").ColumnName = "Final3"
    
                DataGridView1.DataSource = dt
            End Using
        End Sub
    End Class

    Use ADO.Net to connect to the CSV file and then use the adapter to get the data. Please specify which column of data you want in the SQL statement. Fill the data into the DataTable dt using the Fill method.

    Hope it be helpful.

    Best Regards,

    Julie


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Proposed as answer by Alex Li-MSFT Monday, March 16, 2020 7:40 AM
    Friday, March 13, 2020 10:49 AM
    Moderator