Visual Basic Comment lire un fichier CSV et afficher les valeurs dans un datagrid?
j'utilise VB 2005, Comment puis-je ouvrir un fichier CSV et lire les colonnes/lignes et afficher les valeurs dans un datagrid?
exemple de fichier CSV: jsmith,jsmith@hotmail.com
je veux alors Effectuer une action sur chaque ligne c.-à-d. chaque utilisateur, comment ferais-je cela?
je suis un débutant comme vous pouvez le voir mais heureux d'apprendre.
Merci
4 réponses
utilisez le TextFieldParser that's intégré dans VB.NET. Google m'a trouvé cet exemple
Using MyReader As New Microsoft.VisualBasic.FileIO.TextFieldParser _
("C:\test\info.csv")
'Specify that reading from a comma-delimited file'
MyReader.TextFieldType = FileIO.FieldType.Delimited
MyReader.SetDelimiters(",")
Dim currentRow As String()
While Not MyReader.EndOfData
Try
currentRow = MyReader.ReadFields()
With Me.dgvReport.Rows
.Add(currentRow) 'Add new row to data grid view'
End With
Catch ex As Microsoft.VisualBasic.FileIO.MalformedLineException
MsgBox("Line " & ex.Message & _
"is not valid and will be skipped.")
End Try
End While
End Using
Voici une solution simple qui utilise ADO.Net 's ODBC text driver:
Dim csvFileFolder As String = "C:\YourFileFolder"
Dim csvFileName As String = "YourFile.csv"
'Note that the folder is specified in the connection string,
'not the file. That's specified in the SELECT query, later.
Dim connString As String = "Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=" _
& csvFileFolder & ";Extended Properties=""Text;HDR=No;FMT=Delimited"""
Dim conn As New Odbc.OdbcConnection(connString)
'Open a data adapter, specifying the file name to load
Dim da As New Odbc.OdbcDataAdapter("SELECT * FROM [" & csvFileName & "]", conn)
'Then fill a data table, which can be bound to a grid
Dim dt As New DataTable
da.Fill(dt)
grdCSVData.DataSource = dt
une fois rempli, vous pouvez évaluer les propriétés de la datable, comme ColumnName, pour faire utiliser toutes les puissances de la ADO.Net objets de données.
dans VS2008 vous pouvez utiliser Linq pour obtenir le même effet.
Utilisation:
Dim reader As New Common.CSVReader("C:\MyFile.txt")
reader.DisplayResults(dgvMyView)
Classe:
Imports System.Windows.Forms
Imports System.IO
Imports System.Text.RegularExpressions
Public Class CSVReader
Private Const ESCAPE_SPLIT_REGEX = "({1}[^{1}]*{1})*(?<Separator>{0})({1}[^{1}]*{1})*"
Private FieldNames As String()
Private Records As List(Of String())
Private ReadIndex As Integer
Public Sub New(File As String)
Records = New List(Of String())
Dim Record As String()
Dim Reader As New StreamReader(File)
Dim Index As Integer = 0
Dim BlankRecord As Boolean = True
FieldNames = GetEscapedSVs(Reader.ReadLine())
While Not Reader.EndOfStream
Record = GetEscapedSVs(Reader.ReadLine())
BlankRecord = True
For Index = 0 to Record.Length - 1
If Record(Index) <> "" Then BlankRecord = False
Next
If Not BlankRecord Then Records.Add(Record)
End While
ReadIndex = -1
Reader.Close()
End Sub
Private Function GetEscapedSVs(Data As String, Optional Separator As String = ",", Optional Escape As String = """") As String()
Dim Result As String()
Dim Index As Integer
Dim PriorMatchIndex As Integer = 0
Dim Matches As MatchCollection = _
Regex.Matches(Data, String.Format(ESCAPE_SPLIT_REGEX, Separator, Escape))
ReDim Result(Matches.Count)
For Index = 0 to Result.Length - 2
Result(Index) = Data.Substring(PriorMatchIndex, Matches.Item(Index).Groups("Separator").Index - PriorMatchIndex)
PriorMatchIndex = Matches.Item(Index).Groups("Separator").Index + Separator.Length
Next
Result(Result.Length - 1) = Data.Substring(PriorMatchIndex)
For Index = 0 To Result.Length - 1
If Regex.IsMatch(Result(Index), String.Format("^{0}[^{0}].*[^{0}]{0}$", Escape)) Then _
Result(Index) = Result(Index).Substring(1, Result(Index).Length - 2)
Result(Index) = Replace(Result(Index), Escape & Escape, Escape)
If Result(Index) Is Nothing Then Result(Index) = ""
Next
GetEscapedSVs = Result
End Function
Public ReadOnly Property FieldCount As Integer
Get
Return FieldNames.Length
End Get
End Property
Public Function GetString(Index As Integer) As String
Return Records(ReadIndex)(Index)
End Function
Public Function GetName(Index As Integer) As String
Return FieldNames(Index)
End Function
Public Function Read() As Boolean
ReadIndex = ReadIndex + 1
Return ReadIndex < Records.Count
End Function
Public Sub DisplayResults(DataView As DataGridView)
Dim col As DataGridViewColumn
Dim row As DataGridViewRow
Dim cell As DataGridViewCell
Dim header As DataGridViewColumnHeaderCell
Dim Index As Integer
ReadIndex = -1
DataView.Rows.Clear()
DataView.Columns.Clear()
For Index = 0 to FieldCount - 1
col = new DataGridViewColumn()
col.CellTemplate = new DataGridViewTextBoxCell()
header = new DataGridViewColumnHeaderCell()
header.Value = GetName(Index)
col.HeaderCell = header
DataView.Columns.Add(col)
Next
Do While Read()
row = new DataGridViewRow()
For Index = 0 to FieldCount - 1
cell = new DataGridViewTextBoxCell()
cell.Value = GetString(Index).ToString()
row.Cells.Add(cell)
Next
DataView.Rows.Add(row)
Loop
End Sub
End Class
utilisez une bibliothèque pour faire la lecture pour vous.
CsvHelper (une bibliothèque que je gère) est disponible via NuGet.
Je ne suis pas familier avec VB, donc si quelqu'un veut changer ceci en VB, s'il vous plaît faites. Cependant, c'est seulement quelques lignes de code.
var streamReader = // Create StreamReader to CSV file.
var csvReader = new CsvReader( streamReader );
var myObjects = csvReader.GetRecords<MyObject>();
// You can then databind the myObjects collection to a datagrid.
// If you don't want the whole file, you can read row by row.
var myObject = csvReader.GetRecord<MyObject>();