使用Visual Basic（.net）将大型CSV文件导入MS Access [英] Import large CSV file into MS Access using Visual Basic (.net)

问题描述

嗨！我已经撕下了几乎所有的头发，试图将.CSV数据导入MS Access数据库（.accdb）



用于日常工作， CSV文件包含大量数据（100.000+行）。





我该怎么办？使用OleDBConnection还是别的什么？

将csv中的数据插入到accdb中的SQL语法怎么样？

（INSERT INTO表名（字段，字段等）从.csv？）



尝试使用谷歌搜索这个主题很多，但仍然无法使其正常工作。

选择文件（csv和accdb）虽然openfiledialog工作得很好。



***



这里有一些乱七八糟的我已通过：



Imports System.Data.OleDb



公共类Form1





Dim ImportFile As String =

Dim DBFile As String =

Dim CSVFileName As String =

Dim CSVFilePath As String =





Private Sub btnVelgCSV_Click（ sender As System.Object，e As System.EventArgs）处理btnVelgCSV.Click



Dim strFileNameAndPath As String = OpenFile（）

I f（strFileNameAndPath =）然后

MsgBox（你没有选择文件！）

Else

'MsgBox（你选中此文件：&strFileNameAndPath&vbCrLf&_

'文件名为：&System.IO.Path.GetFileName（strFileNameAndPath）&vbCrLf&_

' 位于：&System.IO.Path.GetDirectoryName（strFileNameAndPath）&vbCrLf&_

'它具有以下扩展名：&System.IO.Path.GetExtension（strFileNameAndPath）&vbCrLf &_

'文件创建于&System.IO.File.GetCreationTime（strFileNameAndPath）&vbCrLf&_

'该文件最后被写入&System.IO.File.GetLastWriteTime（strFileNameAndPath）_

'）





结束如果

End Sub

公共函数OpenFile（）As String

Dim strFileName =

D im fileDialogBox As New OpenFileDialog（）

fileDialogBox.Filter =Squirrel CSV Export（* .csv）| * .csv

fileDialogBox.InitialDirectory = Environment.GetFolderPath（Environment .SpecialFolder.Personal）

if（fileDialogBox.ShowDialog（）= DialogResult.OK）然后

strFileName = fileDialogBox.FileName

ImportFile = fileDialogBox .FileName

lblCSV.Text =Valgt CSV-fil：&fileDialogBox.FileName

结束如果

返回strFileName

结束函数



公共函数OpenFile2（）As String

Dim strFileName =

Dim fileDialogBox As New OpenFileDialog（）

fileDialogBox.Filter =Microsoft Access Database（* .accdb）| * .accdb

fileDialogBox.InitialDirectory = Environment.GetFolderPath（Environment。 SpecialFolder.Personal）

If（fileDialo gBox.ShowDialog（）= DialogResult.OK）然后

strFileName = fileDialogBox.FileName

DBFile = fileDialogBox.FileName

lblDB.Text = Valgt databasefil：&fileDialogBox.FileName

结束如果

返回strFileName

结束功能



Private Sub load_csv（）

'Dim myConnToCSV作为OleDbConnection

Dim myConnToAccess作为OleDbConnection

Dim mySQLCommand作为OleDbCommand

Dim mySQLStrg As String

Dim dt As New DataTable

'myConnToCSV = New OleDbConnection（Provider = Microsoft.Jet.OLEDB4​​.0; Data Source = &ImportFile&; Extended Properties = text; HDR = Yes; FMT = Delimited）

myConnToAccess =新OleDbConnection（Provider = Microsoft.ACE.OLEDB.12.0;&Data Source = &DBFile）

使用smooth As新OleDbDataAdapter（Select * from [&D] BFile&]，myConnToAccess）

smooth.Fill（dt）

结束使用



'myConnToCSV 。打开（）

'myConnToAccess.Open（）

'mySQLStrg =SELECT * INTO [MS Access; Database =&DBFile&]。[Import_fra_CSV] FROM [&ImportFile&]

'mySQLStrg =INSERT INTO Import_fra_CSV（KONTOSTRENG，OPPDRAG_GJELDER_ID，KODE_VENTESTATUS，BILAGS_REF，DATO_OVERFORES，DATO_BEREGNET，STATUS_DATO，DATO_PERIODE_FOM，DATO_PERIODE_TOM，SATS，BELOP，BELOP_FORMATERT，KODE_FAGGRUPPE， KODE_FAGOMRAADE）* FROM [Text; DATABASE =&CSVFilePath&; HDR = YES]。[&CSVFileName&]'，myConnToAccess

'和ImportFile

'mySQLCommand =新的OleDbCommand（mySQLStrg，myConnToAccess）

'尝试

'mySQLCommand.ExecuteNonQuery（）



'myConnToAccess.Close（）



'赶上例外情况

'MsgBox（ex.Message）

'结束尝试

'mySQLCommand =新的OleDbCommand（mySQLStrg，myConnToAccess）

'mySQLCommand.ExecuteNonQuery（）

'MsgBox（全部完成！）





MsgBox（全部完成？）< br $>


End Sub



Private Sub btnDB_Click（sender As System.Object，e As System.EventArgs）Handles btnDB.Click

Dim strFileNameAndPath As String = OpenFile2（）

If（strFileNameAndPath =）然后

MsgBox（你没有选择一个文件！）

Else

'MsgBox（你选择了这个文件：&strFileNameAndPath&vbCrLf&_

'文件名是：&System.IO.Path.GetFileName（strFileNameAndPath）&vbCrLf&_

'位于：&System.IO.Path.GetDirectoryName（strFileNameAndPath）&vbCrLf&_

'它有以下扩展名：&System.IO.Path.GetExtension（strFileNameAndPath）&vbCrLf&_

'该文件是在&System.IO.File.GetCreationTime（strFileNameAndPath）&vbCrLf&_上创建的

'文件最后被写入&System.IO.File.GetLastWriteTime（strFileNameAndPath）_

'）

CSVFileName = System.IO.Path.GetFileName（strFileNameAndPath）

CSVFilePath = System.IO.Path.GetDirectoryName（strFileNameAndPath）

结束如果

End Sub



Private Sub btnImport_Click（sender As System.Object，e As System.EventArgs）处理btnImport.Click

调用load_csv（）

结束次级

结束类

Hi! I have torn off nearly all hair on my head trying to import .CSV data into an MS Access database (.accdb)

It is for a routine operation at work, the CSV files contain much data (100.000+ rows).


How to I do this? Using OleDBConnection or something else?
How would the SQL syntax be, to insert data from the csv into the accdb ?
(INSERT INTO Tablename(fields,fields,etc) FROM .csv ?)

Tried googling this topic a LOT, but still cannot get it working.
Selecting files (csv and accdb) with the openfiledialog works fine, though.

***

Here is some of the jibberish I've been through:

Imports System.Data.OleDb

Public Class Form1


Dim ImportFile As String = ""
Dim DBFile As String = ""
Dim CSVFileName As String = ""
Dim CSVFilePath As String = ""


Private Sub btnVelgCSV_Click(sender As System.Object, e As System.EventArgs) Handles btnVelgCSV.Click

Dim strFileNameAndPath As String = OpenFile()
If (strFileNameAndPath = "") Then
MsgBox("You did not select a file!")
Else
'MsgBox("You selected this file: " & strFileNameAndPath & vbCrLf & _
'"The filename is: " & System.IO.Path.GetFileName(strFileNameAndPath) & vbCrLf & _
'"Located in: " & System.IO.Path.GetDirectoryName(strFileNameAndPath) & vbCrLf & _
'"It has the following extension: " & System.IO.Path.GetExtension(strFileNameAndPath) & vbCrLf & _
'"The file was created on " & System.IO.File.GetCreationTime(strFileNameAndPath) & vbCrLf & _
'"The file was last written to on " & System.IO.File.GetLastWriteTime(strFileNameAndPath) _
')


End If
End Sub
Public Function OpenFile() As String
Dim strFileName = ""
Dim fileDialogBox As New OpenFileDialog()
fileDialogBox.Filter = "Squirrel CSV Export (*.csv) |*.csv"
fileDialogBox.InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.Personal)
If (fileDialogBox.ShowDialog() = DialogResult.OK) Then
strFileName = fileDialogBox.FileName
ImportFile = fileDialogBox.FileName
lblCSV.Text = "Valgt CSV-fil: " & fileDialogBox.FileName
End If
Return strFileName
End Function

Public Function OpenFile2() As String
Dim strFileName = ""
Dim fileDialogBox As New OpenFileDialog()
fileDialogBox.Filter = "Microsoft Access Database (*.accdb) |*.accdb"
fileDialogBox.InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.Personal)
If (fileDialogBox.ShowDialog() = DialogResult.OK) Then
strFileName = fileDialogBox.FileName
DBFile = fileDialogBox.FileName
lblDB.Text = "Valgt databasefil: " & fileDialogBox.FileName
End If
Return strFileName
End Function

Private Sub load_csv()
'Dim myConnToCSV As OleDbConnection
Dim myConnToAccess As OleDbConnection
Dim mySQLCommand As OleDbCommand
Dim mySQLStrg As String
Dim dt As New DataTable
'myConnToCSV = New OleDbConnection("Provider=Microsoft.Jet.OLEDB4.0;Data Source=" & ImportFile & ";Extended Properties=text;HDR=Yes;FMT=Delimited")
myConnToAccess = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;" & "Data Source=" & DBFile)
Using smooth As New OleDbDataAdapter("Select * from [" & DBFile & "]", myConnToAccess)
smooth.Fill(dt)
End Using

'myConnToCSV.Open()
'myConnToAccess.Open()
'mySQLStrg = "SELECT * INTO [MS Access;Database=" & DBFile & "].[Import_fra_CSV] FROM [" & ImportFile & "]"
'mySQLStrg = "INSERT INTO Import_fra_CSV(KONTOSTRENG,OPPDRAG_GJELDER_ID,KODE_VENTESTATUS,BILAGS_REF,DATO_OVERFORES,DATO_BEREGNET,STATUS_DATO,DATO_PERIODE_FOM,DATO_PERIODE_TOM,SATS,BELOP,BELOP_FORMATERT,KODE_FAGGRUPPE,KODE_FAGOMRAADE) * FROM [Text;DATABASE=" & CSVFilePath & ";HDR=YES].[" & CSVFileName & "]" ', myConnToAccess
'& ImportFile
'mySQLCommand = New OleDbCommand(mySQLStrg, myConnToAccess)
'Try
'mySQLCommand.ExecuteNonQuery()

' myConnToAccess.Close()

'Catch ex As Exception
' MsgBox(ex.Message)
' End Try
'mySQLCommand = New OleDbCommand(mySQLStrg, myConnToAccess)
'mySQLCommand.ExecuteNonQuery()
'MsgBox("All done!")


MsgBox("All done?")

End Sub

Private Sub btnDB_Click(sender As System.Object, e As System.EventArgs) Handles btnDB.Click
Dim strFileNameAndPath As String = OpenFile2()
If (strFileNameAndPath = "") Then
MsgBox("You did not select a file!")
Else
'MsgBox("You selected this file: " & strFileNameAndPath & vbCrLf & _
'"The filename is: " & System.IO.Path.GetFileName(strFileNameAndPath) & vbCrLf & _
'"Located in: " & System.IO.Path.GetDirectoryName(strFileNameAndPath) & vbCrLf & _
'"It has the following extension: " & System.IO.Path.GetExtension(strFileNameAndPath) & vbCrLf & _
'"The file was created on " & System.IO.File.GetCreationTime(strFileNameAndPath) & vbCrLf & _
'"The file was last written to on " & System.IO.File.GetLastWriteTime(strFileNameAndPath) _
')
CSVFileName = System.IO.Path.GetFileName(strFileNameAndPath)
CSVFilePath = System.IO.Path.GetDirectoryName(strFileNameAndPath)
End If
End Sub

Private Sub btnImport_Click(sender As System.Object, e As System.EventArgs) Handles btnImport.Click
Call load_csv()
End Sub
End Class

推荐答案

我感谢您自己想要这样做。如果这是一个很好的学习练习，虽然我不会从其他人的代码开始，而是阅读 System.IO.File ， System.String ， System.Text.RegularExpressions ， System.Data.OleDbDataAdapter ，以及 System.Data.OldDbCommandBuilder ;查看每个示例，看看每个组件的工作原理。



项目有三个主要部分：

- CSV阅读器

- 访问编写器

- 一个控制台应用程序或（由于增加的复杂性而不推荐）一个简单的Windows窗体应用程序，作为上述容器。



这个网站上的每个步骤都有很棒的文章。



如果你只是想完成这个，我强烈建议将其发布在Freelancer.com上，其中第三世界国家的某个人很乐意为

I appreciate you wanting to do this yourself. If this is a learning exercise that's great, although I wouldn't start with someone else's code, but rather read up on System.IO.File, System.String, System.Text.RegularExpressions, System.Data.OleDbDataAdapter, and System.Data.OldDbCommandBuilder; look at each of the examples and see how each component works.

There three main parts to your project:
- the CSV reader
- the Access writer
- a console application or (not recommended due to added complexity) a simple Windows Forms application to act as a container for the above.

There are great articles on each of these steps on this website.

If you just want to get this done, I would strongly suggest posting it on Freelancer.com where someone in a third-world country will gladly do it for

25做这件事。它将很快完成，一个资历过高的程序员将为他/她的家人提供一周的服务。

25. It will get done quickly and an overqualified programmer will feed his/her family for a week.

我不确定你为什么使用.NET作为Access本身有内置的工具您可以保存导入并从代码运行已保存的导入，但如果.NET必须是解决方案，则可以使用Jet（Access）提供程序将CSV导入ADO.NET数据集。然后有很多方法可以将数据集复制到Access中。



您似乎正在自己阅读文件（那里有很多注释掉的代码！）。如果您逐行处理CSV并解析文本，则可以逐个记录地写入Access。确保您的数据类型/字段长度合适。如果对CSV的质量有任何疑问，逐条记录的方法将允许您验证和/或记录错误，同时仍然允许其余的导入。



最后，您可以尝试从.NET自动化Access本身。我从来没有试过这个。有一个PIA for Access，这应该很好用。我的方法是在Access中手动设置导入，保存导入定义并使用Interop程序集运行保存的导入。这种方法可能是最简单，最强大的技术。

I'm not sure why you are using .NET as Access itself has the tools built in the you can save the import and run a saved import from code, but if .NET has to be the solution, you can use the Jet (Access) provider to bring the CSV into an ADO.NET dataset. There are then many methods to copy form the dataset into Access.

You seem to be reading the file in yourself (there's a lot of commented out code there!). If you deal with the CSV line-by-line and parse the text, you can write to Access on a record-by-record basis. Make sure that your data types/field lengths are appropriate. If there's any doubt about the quality of the CSV, the record-by-record approach will allow you to validate and/or log errors while still allowing the rest of the import.

Finally, you could try automating Access itself from .NET. I've never tried this. There's a PIA for Access, this should work well. My approach would be to set up the import manually in Access, save the import definition and use the Interop assembly to run the saved import. Tis approach may well be the easiest and most robust technique.

这篇关于使用Visual Basic（.net）将大型CSV文件导入MS Access的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！