CSV文件与DataTable互转-优快云博客

本文介绍了一种在Visual Basic中实现CSV文件与DataTable相互转换的方法。包括将DataTable数据导出到CSV文件、从CSV文件读取数据并转换为DataTable等实用功能。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

Imports System.IO
Imports System.Text
Imports System.Configuration
Imports System.Data

Public Class CSVOper
''' <summary>
''' DataTableデータをCSVファイルに出力する
''' </summary>
''' <param name="sourceTable">DBデータ</param>
''' <param name="writer">StreamWriter</param>
''' <remarks></remarks>
Public Shared Sub ConvertDataTableDataToCSVFile(ByVal sourceTable As System.Data.DataTable, _
ByVal writer As TextWriter)
For index As Integer = 0 To sourceTable.Rows.Count - 1
Dim lineData As New StringBuilder
For index1 As Integer = 0 To 13
If index1 = 0 Then
lineData.Append(sourceTable.Rows(index).Item(index1).ToString)
Else
If sourceTable.Rows(index).Item(index1).ToString <> "" Then
lineData.Append("," + sourceTable.Rows(index).Item(index1).ToString)
Else
lineData.Append(",")
End If
End If
Next
writer.WriteLine(lineData.ToString)
Next

writer.Flush()
End Sub

    ''' <summary>
    ''' CSVファイル第一行データの取得
    ''' </summary>
    ''' <param name="csvFileAllPath">CSVファイル全パス(ファイル名を含める)</param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Shared Function GetCSVFileFirstLineData(ByVal csvFileAllPath As String) As String()
        Dim sr As IO.StreamReader
        Dim tmpstr() As String
        sr = IO.File.OpenText(csvFileAllPath)
        tmpstr = sr.ReadLine().Split(",")
        sr.Close()
        Return tmpstr
    End Function

''' <summary>
''' CSVデータをString()に出力する
''' </summary>
''' <param name="TxtFn">CSVファイルパス(c:\xxx\aaa.csv)</param>
''' <returns></returns>
''' <remarks></remarks>
Public Shared Function GetTxtFile(ByVal TxtFn As String) As String()
Dim sr As IO.StreamReader
Dim tmpstr As String
Dim R() As String
Dim Rn As Long
sr = IO.File.OpenText(TxtFn)
tmpstr = sr.ReadLine()
Rn = 0
While Not tmpstr Is Nothing
ReDim Preserve R(Rn)
R(Rn) = tmpstr
tmpstr = sr.ReadLine()
Rn += 1
End While
sr.Close()

Return R
End Function

''' <summary>
''' CSVデータをDatatableに出力する
''' </summary>
''' <param name="TxtFn">CSVファイルパス(c:\xxx\aaa.csv)</param>
''' <returns></returns>
''' <remarks></remarks>
Public Shared Function ConvertCSVFileToDataTable(ByVal TxtFn As String) As System.Data.DataTable
Dim dt As New System.Data.DataTable("mydata")
For i As Integer = 0 To 13
dt.Columns.Add(i.ToString)
Next
Dim sr As New IO.StreamReader(TxtFn, System.Text.Encoding.Default)
Dim tmpstr As String
'sr = IO.File.OpenText(TxtFn)
tmpstr = sr.ReadLine()
While Not tmpstr Is Nothing
Dim dr As DataRow = dt.NewRow()
Dim aa As String() = tmpstr.Split(",")
For index As Integer = 0 To 13
dr(index) = aa(index).ToString
Next
dt.Rows.Add(dr)
tmpstr = sr.ReadLine()
End While
sr.Close()
Return dt
End Function

End Class

''' <summary>
''' "","bbb ","ccc,ddd"," eee"
''' </summary>
''' <param name="csvFileName"></param>
''' <returns></returns>
Public Shared Function ConvertCSVFileToDataTable(ByVal csvFileName As String) As System.Data.DataTable

'Shift JISで読み込む
Dim tfp As New FileIO.TextFieldParser(csvFileName,
System.Text.Encoding.GetEncoding(932))
'フィールドが文字で区切られているとする
'デフォルトでDelimitedなので、必要なし
tfp.TextFieldType = FileIO.FieldType.Delimited
'区切り文字を,とする
tfp.Delimiters = New String() {","}
'フィールドを"で囲み、改行文字、区切り文字を含めることができるか
'デフォルトでtrueなので、必要なし
tfp.HasFieldsEnclosedInQuotes = True
'フィールドの前後からスペースを削除する
'デフォルトでtrueなので、必要なし
tfp.TrimWhiteSpace = False
Dim isNeedCreatdDt As Boolean = True
Dim dt As New System.Data.DataTable("mydata")
While Not tfp.EndOfData
'フィールドを読み込む
Dim fields As String() = tfp.ReadFields()
If isNeedCreatdDt Then
For i As Integer = 0 To fields.Count - 1
dt.Columns.Add()
Next
isNeedCreatdDt = False
End If

Dim dr As DataRow = dt.NewRow()
'保存
For index As Integer = 0 To fields.Count - 1
dr(index) = fields(index).ToString
Next
dt.Rows.Add(dr)
End While
tfp.Close()
Return dt
End Function

------------------------- 调用 -------------------------

''' <summary>
''' DBデータを取得、CSVファイルを作成
''' </summary>
''' <remarks></remarks>
Private Sub SetCSVDataByDatatable()
Dim sourceTable As New System.Data.DataTable
Dim conn As SqlConnection
Dim da As SqlDataAdapter
Dim ds As DataSet
Dim cnStr As String = ConfigurationManager.AppSettings("DBConnect").ToString()
conn = New SqlConnection(cnStr)
Dim strSql As New StringBuilder
strSql.Append(" SELECT ")
strSql.Append(" * ")
strSql.Append(" FROM ")
strSql.Append("TableName")
da = New SqlDataAdapter(strSql.ToString(), conn)
ds = New DataSet()
da.Fill(ds, "v_Citizens")
sourceTable = ds.Tables("v_Citizens")
conn.Close()

Using writer As StreamWriter = New StreamWriter(CSVFileAllPath)
CSVOper.ConvertDataTableDataToCSVFile(sourceTable, writer)
End Using
End Sub

------------------------------------------------------------------------------------------------------------

Dim csvData As System.Data.DataTable = CSVOper.ConvertCSVFileToDataTable(fnCSV)

------------------------------------------------------------------------------------------------------------

Dim arr As New ArrayList
Private Function ReadCSV() As Integer
Dim csvData As String() = CSVOper.GetTxtFile(fnCSV)
arr.Clear()
For index As Integer = 0 To csvData.Count - 1
Dim lineData As String() = csvData(index).Split(",")
arr.Add(lineData(8) + "," + lineData(6) + "," + lineData(4) )
Next
Return csvData.Count
End Function

CSV操作について