将.csv导入MS Access。 “AtEndOfStream”不读最后一行 [英] Importing .csv to MS Access. "AtEndOfStream" doesn't read last line

问题描述

我要通过VBScript将.csv文件中的一些数据导入MS Access数据库。

I want to import some data from a .csv file into a MS Access database via VBScript.

Sub CSVImport
  connStr = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=P:\somedatabase.accdb"

  'Define object type
  Set objConn      = CreateObject("ADODB.Connection")
  Set objRecordSet = CreateObject("ADODB.Recordset")

  'Open Connection
  objConn.open connStr

  objRecordSet.Open "SELECT * FROM SomeTable", _
    objConn, adOpenStatic, adLockOptimistic

  Set objFSO = CreateObject("Scripting.FileSystemObject")

  Set objFile = objFSO.OpenTextFile("P:\someFile.csv")

  Do Until objFile.AtEndOfStream
    strVKBL_Stamm = objFile.ReadLine
    arrVKBL_Stamm = Split(strVKBL_Stamm, ";")
    objRecordSet.AddNew

    objRecordSet("FirstName") = arrVKBL_Stamm(0)
    objRecordSet("LastName")  = arrVKBL_Stamm(1)
    objRecordSet("Date")      = arrVKBL_Stamm(...)
    objRecordSet("Type")      = arrVKBL_Stamm(11)
  Loop

  Set objRecordSet = Nothing
  Set objFSO = Nothing
  Set objFile = Nothing
  Set objConn = Nothing
End Sub

此脚本获取所有数据从我的CSV文件，但它错过了最后一行。当我在.csv结尾处输入两次（一次不足）时，我的所有数据都会导入。

This script gets all the data out of my CSV file but it does miss the last line. When I hit return twice (once doesn't suffice) at the end of the .csv all of my data gets imported.

我的备份计划是更改 AtEndOfStream 位类似

My backup plan is to change the AtEndOfStream bit to something like

If arrVKBL_Stamm(0) = 0 Then
  Exit Do

并向文件添加零。问题是，.csv是一个报表，从SAP导出（这是 - afaik - 无法导出到MS Access本身），所以部分需要另一个脚本或手动完成。

and add a zero to the file. Problem is, the .csv is a report, exported from SAP (which is - afaik - unable to export to MS Access itself), so that part would need another script or has to be done by hand.

有没有人有线索或给我一个提示如何解决这个问题？

Does anyone have a clue or give me a hint how to solve this?

推荐答案

此代码：

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.OpenTextFile("D:\myfile.csv", 1)

Do Until objFile.AtEndOfStream
    S = objFile.ReadLine
    Debug.Print S
Loop
objFile.Close

，并且它在我的CSV文件中打印了所有行即使最后一行没有以CrLf结尾。

in VBA and it printed all lines of my CSV file, even if the last line doesn't end with a CrLf.

我很确定原因是： objRecordSet.AddNew 缺少一个 objRecordSet.Update 。

I'm pretty sure the reason is: your objRecordSet.AddNew is missing an objRecordSet.Update.

显然，如果调用 .AddNew 之前的 .AddNew 之后，将保存上一条记录。

Apparently, if you call .AddNew after the previous .AddNew, the previous record is saved nevertheless.

但 objRecordSet.AddNew 后跟 Set objRecordSet = Nothing 不保存最后一条记录。

But objRecordSet.AddNew followed by Set objRecordSet = Nothing doesn't save the last record.

解决方案：在循环中添加 objRecordSet.Update

Solution: add objRecordSet.Update as last command in the loop.

这篇关于将.csv导入MS Access。 “AtEndOfStream”不读最后一行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！