通过VB.net 2010快速导入csv文件到访问数据库 [英] Fast import of csv file into access database via VB.net 2010

问题描述

我在将大约30,000行数据从Tab分隔文件导入到Access数据库中时遇到了问题。文件导入是从我编写的VB.net 2010程序管理的。

程序当前正在循环遍历每一行并为每一行发出一个SQL insert语句。但是，这需要大约2个小时才能完成。如果我手动导入同一个文件直接进入访问使用获取外部数据，大约需要30秒。

我以为我可能能够创建一个datagridview链接到MS访问，并以某种方式运行一个native .net命令将文件导入到datagridview，然后快速写入数据访问，但我不知道如果这是可能的。

手动导入不是一个选项，它需要通过vb.net应用程序的各种原因。

$

感谢
Simon

div class =h2_lin>解决方案

您可以针对您的MS Access连接运行SQL语句以导入text / csv。要导入到新表中：

  cmd.CommandText = 
SELECT F1 AS id，F2 AS firstname& 
INTO MyNewTable& 
FROM [Text; FMT = Delimited; HDR = No; CharacterSet = 850; DATABASE = C：\__tmp] .table1.csv; 
  

要导入到现有表格中：

  cmd.CommandText = 
INSERT INTO MyExistingTable& 
SELECT F1 AS id，F2 AS firstname& 
FROM [Text; FMT = Delimited; HDR = No; CharacterSet = 850; DATABASE = C：\__tmp] .table1.csv; 
  

您还可以将schema.ini文件用于非标准分隔符。 （将数据导出到预格式化文件，从访问创建逗号分隔文件（csv） - 预定每日从窗口）

I am having problems getting the speed that I want in importing approx 30,000 lines of data from Tab separated files in to an Access database. The File import is managed from a VB.net 2010 program that I have written.

The program is currently looping through each line and issuing a SQL insert statement for each one. However this is taking about 2 hours to complete. If I manually import in the same file directly into access using Get External Data, it takes about 30 seconds.

I was thinking that I might be able to create a datagridview linked to MS access and somehow run a native .net command to import the file into the datagridview and then quickly write the data to access but i'm not sure if this is possible.

By the way doing a manual import is not an option, it needs to go through the vb.net application for various reasons.

Can any one provide any suggestions on how I might speed things up?

Thanks Simon

解决方案

You can run an SQL statement against your MS Access connection to import text/csv. To import into a new table:

cmd.CommandText =
    "SELECT F1 AS id, F2 AS firstname " &
    "INTO MyNewTable " &
    "FROM [Text;FMT=Delimited;HDR=No;CharacterSet=850;DATABASE=C:\__tmp].table1.csv;"

To import into an existing table:

cmd.CommandText =
    "INSERT INTO MyExistingTable " &
    "SELECT F1 AS id, F2 AS firstname " &
    "FROM [Text;FMT=Delimited;HDR=No;CharacterSet=850;DATABASE=C:\__tmp].table1.csv;"

You can also use a schema.ini file for non-standard delimiters. ( Exporting data into a preformatted file , Create comma separated file (csv) from access - scheduled daily from windows)

这篇关于通过VB.net 2010快速导入csv文件到访问数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！