将分隔文件（.cvs）中的行导入MS

编程入门行业动态更新时间:2024-10-28 06:30:06

将分隔文件（.cvs）中的行导入MS-Access表(Import rows from delimited file (.cvs) into MS-Access table)

系统背景：我在Access 2010中使用用户界面和存储数据库的Microsoft SQL Server 2008后端。

问题背景：我实验室中的机器以.csv文件的形式输出结果。这些文件放在服务器上的文件夹中。目前，结果手动输入数据库。目标是让一个访问程序（VBA）逐行读入文件中的数据，并将文件的每一行插入数据库中的特定表。我被告知我们ADO对象来实现这一目标。

进展：我的问题在于尝试读取分隔文件的每一行。我得到了一个参考网站http://msdn.microsoft.com/en-us/library/ms974559.aspx但当我尝试在副标题下实现示例时如何使用ADO查询文本文件？ 我得到了一些错误，坚持在连接时没有找到方法。打开。我想获取每行的值并将它们存储到临时变量中以传递给存储过程。已创建存储过程，并且已创建插入新记录的过程。

代码旁注：代码是较低级别的函数，这意味着它一次不会读取文件夹中的所有.csv文件。该函数由更高级别的函数给出文件名，该函数将从该文件名中读取指定文件的每一行，并将其存储在表“tblICPMS”中。以下是在excel中打开时.cvs文件的样子

这是我的代码：

Public Function ImportICPMS(ThisFileName As String, ThisQueueID As Long, BatchID
As Long, InstrumentName As String, TechId As Long)
On Error GoTo HandleError

Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adCmdText = &H0001

Dim obj_fso As Object
Dim objconnection As connection
Dim objRecordset As Recordset
Dim strpathtotextfile As String

'test if file exists (newpath is a public path to folder)'
Set obj_fso = CreateObject("Scripting.FileSystemObject")
If obj_fso.FileExists(NewPath & "\" & ThisFileName) Then
Else
    Err.Raise vbObjectError + 1000, "MyProjectName.MyObjectName", "file " & ThisFileName & " for " & InstrumentName & " not found" 'if false error is raised
End If

Set objconnection = CreateObject("ADODB.connection") 'create ADO objects'
Set objRecordset = CreateObject("ADODB.recordset")

strpathtotextfile = NewPath & "\" 'path to folder where file resides'

objconnection.Open "Provider=SQLOLEDB.1;Data Source=strpathtotextfile;Extended Properties=;HDR = YES;FMT = Delimited"

objrecordset.Open "SELECT * FROM " & ThisFileName,objconnection, adOpenStatic, adLockOptimistic, adCmdText

Do Until objRecordset.EOF

'I do not know what Wscript.Echo means but I want to save the acquired values into
 temp variables to pass them to a stored procedure that inserts them into the table'

Wscript.Echo objRecordset.Fields.Item("Sample Name")
Wscript.Echo objRecordset.Fields.Item("Date and Time Acquired")
Wscript.Echo objRecordset.Fields.Item("Element Full Name")
Wscript.Echo objRecordset.Fields.Item("Concentration")
Wscript.Echo objRecordset.Fields.Item("Units")

'code to insert rows into table would probably go here'
'code to clear out local objects would go here'

objRecordset.MoveNext

Loop
objRecordset.Close
objconnection.Close
 
 我没有包含其余代码或存储过程，但如果有人想看到它们，我可以。 我想把我插入的代码集中在我正在处理的部分上。 
System background: I am working in Access 2010 with user interface and a Microsoft SQL Server 2008 back-end where the database is stored. 
Problem background: A machine in my lab outputs results in the form of .csv files. These files are placed in a folder on the server. Currently, results are entered manually into the database. The goal is to have a program in access (VBA) that reads in the data in the files row by row and inserts each row of the file into a specific table in the database. I was instructed to us ADO objects to accomplish this. 
Progress: My issue lies with trying to read in each row of the the delimited file. I was given a reference website http://msdn.microsoft.com/en-us/library/ms974559.aspx but when I tried implementing the example under the subtitle So How Do I Use ADO to Query a Text File? and I get quite a few errors insisting methods were not found specifically at connection.Open. I want to obtain the values at each row and store them into temporary variables to pass to a stored procedure. the stored procedure is already created and the process to insert the new record is already created.  
Sidenote on code: The code is a lower level function meaning it does not read in all the .csv files in the folder at one time. The function is given a file name by a higher level function and from that file name the function will read in each row from the specified file and store it in the table "tblICPMS". Here is a sample of what the .cvs file looks like when opened in excel  
Here is my code: 
Public Function ImportICPMS(ThisFileName As String, ThisQueueID As Long, BatchID
As Long, InstrumentName As String, TechId As Long)
On Error GoTo HandleError

Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adCmdText = &H0001

Dim obj_fso As Object
Dim objconnection As connection
Dim objRecordset As Recordset
Dim strpathtotextfile As String

'test if file exists (newpath is a public path to folder)'
Set obj_fso = CreateObject("Scripting.FileSystemObject")
If obj_fso.FileExists(NewPath & "\" & ThisFileName) Then
Else
    Err.Raise vbObjectError + 1000, "MyProjectName.MyObjectName", "file " & ThisFileName & " for " & InstrumentName & " not found" 'if false error is raised
End If

Set objconnection = CreateObject("ADODB.connection") 'create ADO objects'
Set objRecordset = CreateObject("ADODB.recordset")

strpathtotextfile = NewPath & "\" 'path to folder where file resides'

objconnection.Open "Provider=SQLOLEDB.1;Data Source=strpathtotextfile;Extended Properties=;HDR = YES;FMT = Delimited"

objrecordset.Open "SELECT * FROM " & ThisFileName,objconnection, adOpenStatic, adLockOptimistic, adCmdText

Do Until objRecordset.EOF

'I do not know what Wscript.Echo means but I want to save the acquired values into
 temp variables to pass them to a stored procedure that inserts them into the table'

Wscript.Echo objRecordset.Fields.Item("Sample Name")
Wscript.Echo objRecordset.Fields.Item("Date and Time Acquired")
Wscript.Echo objRecordset.Fields.Item("Element Full Name")
Wscript.Echo objRecordset.Fields.Item("Concentration")
Wscript.Echo objRecordset.Fields.Item("Units")

'code to insert rows into table would probably go here'
'code to clear out local objects would go here'

objRecordset.MoveNext

Loop
objRecordset.Close
objconnection.Close
 
I did not include the rest of the code nor the stored procedure but I can if you anyone wants to see them. I wanted to just focus my inserted code on the part I was working on. 
                最满意答案
                
                    
                         您不存在方法的问题可能是它没有使用该提供程序正确打开CSV。 据我所知，SQLOLEDB.1没有这个文本阅读器功能。  
 试试这个，改变：  
objconnection.Open "Provider=SQLOLEDB.1;Data Source=strpathtotextfile;Extended Properties=;HDR = YES;FMT = Delimited"
 
 对此，  
objconnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=strpathtotextfile;Extended Properties=""Text;HDR=YES;FMT=Delimited"""
 
 更新：注意到声明问题：  
Dim objconnection As connection
Dim objRecordset As Recordset
 
 应该工作，  
Dim objconnection As Object
Dim objRecordset As Object
Your issue with methods not existing may be that it's not properly opening the CSV using that provider. To my knowledge, SQLOLEDB.1 doesn't have this text reader functionality. 
Try this, change: 
objconnection.Open "Provider=SQLOLEDB.1;Data Source=strpathtotextfile;Extended Properties=;HDR = YES;FMT = Delimited"
 
To this, 
objconnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=strpathtotextfile;Extended Properties=""Text;HDR=YES;FMT=Delimited"""
 
Update: noticed an issue with declarations: 
Dim objconnection As connection
Dim objRecordset As Recordset
 
Should work as, 
Dim objconnection As Object
Dim objRecordset As Object