CODEWITHSUNDEEP
vbacsvms-accessvbscript
Tom K.
Tom K.

Reputation: 1042

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

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?

Upvotes: 1

Views: 482

Answers (2)

HackSlash
HackSlash

Reputation: 5803

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

Upvotes: 0

Andre
Andre

Reputation: 27634

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.

Upvotes: 4

Related Questions