Read a CSV file into a DataGridView
'12/4/2011
'Code to read CSV file with headers using
'ACE.OLEDB.12.0 Connection
'The following is a Sampling of <EXTENSION.csv>
'The CSV file contains all the Extension on my Desktop Computer
'The full file has 923 Rows with Headers:
'EXTENSION,DESCRIPTION,EXECUTABLE_PATH
'.mde,Microsoft Office Access MDE Database,C:\Program Files\Microsoft Office\Office12\MSACCESS.EXE
'.mdf,None,NO DEFAULT PROGRAM SET
'.mdmp,Crash Dump File,C:\Program Files\Microsoft Visual Studio 9.0\Common7\IDE\devenv.exe
'.mdn,Microsoft Office Access Blank Database Template,C:\Program Files\Microsoft Office\Office12\MSACCESS.EXE
'.mdp,VC++ 5 Workspace,C:\Program Files\Microsoft Visual Studio 9.0\Common7\IDE\devenv.exe
'.mds,None,NO DEFAULT PROGRAM SET
'.mdt,Microsoft Office Access Add-in Data,C:\Program Files\Microsoft Office\Office12\MSACCESS.EXE
'.mdw,Microsoft Office Access Workgroup Information,C:\Program Files\Microsoft Office\Office12\MSACCESS.EXE
'.mdx,None,C:\Program Files\DAEMON Tools Lite\DTLite.exe
'.mdz,Protracker Compressed Module,NO DEFAULT PROGRAM SET
'.meo,None,NO DEFAULT PROGRAM SET
'.mfp,Macromedia Flash Paper,C:\Program Files\Internet Explorer\IEXPLORE.EXE
'.mgc,Media Catalog File,NO DEFAULT PROGRAM SET
'.mht,MHTML Document,C:\Program Files\Internet Explorer\IEXPLORE.EXE
'.mhtml,MHTML Document,C:\Program Files\Internet Explorer\IEXPLORE.EXE
'.mid,MIDI File Format,NO DEFAULT PROGRAM SET
'.midi,MIDI File Format,NO DEFAULT PROGRAM SET
'.MIZ,Compressed MIDI File,NO DEFAULT PROGRAM SET
'.mjd,Job Definition Format,C:\Program Files\Adobe\Acrobat 10.0\Acrobat\Acrobat.exe
'.mk,Makefile,C:\Program Files\Microsoft Visual Studio 9.0\Common7\IDE\devenv.exe
'.mka,Matroska Audio file,C:\Program Files\Combined Community Codec Pack\MPC\mpc-hc.exe
'.mkv,Matroska Media file,C:\Program Files\Combined Community Codec Pack\MPC\mpc-hc.exe
'.mmf,None,NO DEFAULT PROGRAM SET
'.mml,Media Catalog File,NO DEFAULT PROGRAM SET
'.mmm,Media Clip,mplay32.exe /play /close
'.mmw,Media Catalog File,NO DEFAULT PROGRAM SET
'.mobi,None,C:\Program Files\FBReader\FBReader.exe
'.mod,None,NO DEFAULT PROGRAM SET
'.moh,None,NO DEFAULT PROGRAM SET
'.MOV,Video,C:\Program Files\JetAudio\jetaudio.exe
'.movie,None,NO DEFAULT PROGRAM SET
'.MP1,MPEG Layer 1 Audio File,NO DEFAULT PROGRAM SET
'.mp2,MPEG Layer 2 Audio,C:\Program Files\iTunes\iTunes.exe
'.mp2v,MPEG Media file,C:\Program Files\Combined Community Codec Pack\MPC\mpc-hc.exe
'.MP3,Music,C:\Program Files\JetAudio\jetaudio.exe
'.MP4,Video,C:\Program Files\JetAudio\jetaudio.exe
'.xlsm,Microsoft Office Excel Macro-Enabled Worksheet,C:\Program Files\Microsoft Office\Office12\EXCEL.EXE
'.xlsmhtml,None,NO DEFAULT PROGRAM SET
'.xlsx,Microsoft Office Excel Worksheet,C:\Program Files\Microsoft Office\Office12\EXCEL.EXE
'.xlt,Microsoft Office Excel Template,C:\Program Files\Microsoft Office\Office12\EXCEL.EXE
'.xlthtml,Microsoft Office Excel HTML Template,C:\Program Files\Microsoft Office\Office12\EXCEL.EXE
'.xltm,Microsoft Office Excel Macro-Enabled Template,C:\Program Files\Microsoft Office\Office12\EXCEL.EXE
'.xltx,Microsoft Office Excel Template,C:\Program Files\Microsoft Office\Office12\EXCEL.EXE
'.xlw,Microsoft Office Excel Workspace,C:\Program Files\Microsoft Office\Office12\EXCEL.EXE
'.xlxml,Microsoft Office Excel XML Worksheet,NO DEFAULT PROGRAM SET
'.xm,Fasttracker 2 Module,NO DEFAULT PROGRAM SET
'.xml,XML Document,C:\Program Files\Internet Explorer\IEXPLORE.EXE
'.xmta,XMTA Document,C:\Program Files\Microsoft Visual Studio 9.0\Common7\IDE\devenv.exe
'.xmz,Fasttracker 2 Compressed Module,NO DEFAULT PROGRAM SET
'.XOML,Windows Workflow File,C:\Program Files\Microsoft Visual Studio 9.0\Common7\IDE\devenv.exe
'.xpp,SQL Server Replication Snapshot Extended Properties Script,NO DEFAULT PROGRAM SET
'.xps,XPS document,NO DEFAULT PROGRAM SET
'.xsc,Visual Studio Dataset Internal Info File,NO DEFAULT PROGRAM SET
'.xsd,XML Schema File,C:\Program Files\Microsoft Visual Studio 9.0\Common7\IDE\devenv.exe
'Controls:
' Form - Form1
'DataGridView - dgv1
'Button - btnGo
Imports System.IO
Imports System.Data
imports System.Data.OleDb
Public Class Form1
Dim ds As New DataSet
'csv file resides in the debug Folder of the project
Dim csvFile As String = Environment.CurrentDirectory & "\EXTENSION.csv"
Private Sub btnGo_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) _
Handles btnGo.Click
'For text file connection
'The Directory that the file resides can be thought of as the database
'The File Name xxx.csv,xx.txt can be thought of as the table name
Dim csvFileName As String = Path.GetFileName(csvFile)
Dim csvPath As String = Path.GetDirectoryName(csvFile)
'Connection String Office 2007/2010
'For some reason extra Double Quotes Chr(34) are
'needed around the Properties in the Connection String
Dim conStr As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & _
csvPath & ";Extended Properties=" & _
Chr(34) & "text;HDR=yes;FMT=Delimited;" & Chr(34)
'Then use sql query to get the result set you want
Dim sql As String = "Select * From " & csvFileName & ";"
'OLEDB Connection
Dim con As New OleDbConnection(conStr)
'Create a new command and set its properties
Dim cmd As New OleDbCommand
With cmd
.CommandType = CommandType.Text
.CommandText = sql
.Connection = con
End With
'Create a DataAdapter, set its properties, and Fill
Dim da As New OleDbDataAdapter
da.SelectCommand = cmd
da.Fill(ds)
'Set the DataGridView's DataSource
dgv1.DataSource = ds.Tables(0)
End Sub
End Class