Importing .csv to MS Access. "AtEndOfStream" doesn't read last line

Question

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

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.

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

If arrVKBL_Stamm(0) = 0 Then
  Exit Do

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?

The code you posted would raise errors, not only because of the missing `End Sub`, but because you're trying to modify a closed recordset. Please show your actual code. — Ansgar Wiechers, Feb 02 '16 at 20:44
Added the rest, not sure if this clarifies things. But I hope so. :) — Tom K., Feb 03 '16 at 09:02
Your code still cannot work. You never open a file, you never add a new record in `objRecordSet`. [mcve] — Andre, Feb 03 '16 at 09:41
Stupid me, edited that part out, the code was a bit cluttered because of some error catching-blocks. It's in there now. Sorry. — Tom K., Feb 03 '16 at 09:52
Oh man.. sorry. I'm really not concentrated right now. It should (finally) be all in there. — Tom K., Feb 03 '16 at 11:59

score 4 · Accepted Answer · answered Feb 03 '16 at 13:11

I tried this code:

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

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

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

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

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

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

That did it! Thanks for your help especially with regard to the long process of asking the right question. (; — Tom K., Feb 03 '16 at 13:19

score 0 · Answer 2 · answered Oct 29 '18 at 20:53

SAP uses a standard database engine in the back end. You can set up linked tables that pull directly from SAP in to Access. No export\import. It's a live table.

SEE HERE:

How do I configure my Microsoft Access database to pull source data directly from SAP BW?

https://archive.sap.com/discussions/thread/1382889

Importing .csv to MS Access. "AtEndOfStream" doesn't read last line

2 Answers2

Linked