How to import a CSV "String" into Access Recordset? (1 Viewer)


Registered User.
Local time
Today, 21:38
Oct 26, 2012
Hi all,

I have an API Get Request which returns a CSV string, what is the best way to interrogate that string? I'm looking to extract specific columns, or convert it into an access table? Sample string below.

From googling I found the TransferText method, however this only appears to work for saved csv files, not a string. Unless I've missed something. Can anyone point me in the right direction?

Many thanks :)

Sample String

"Employee ID","Badge","Username","First Name","Surname","Employee Status","In Payroll","Locked","Primary E-mail","Work Phone","Mobile Phone","Home Phone","Security Profile","Holiday Profile","Timesheet Profile","Pay Period Profile","Labour Level(1)"
"000602","602","Toby.Bridges","Tony","Bridges","Active","Yes","No","","01234 567891","","09876 543211","Company Administrator **FULL ACCESS**","England & Wales","Timesheet: MONTHLY Employees (Days)","Monthly","Finance Team"
"000769","769","Clark.Kenton","Clark","Kenton","Active","Yes","No","","","01234 567892","09876 543212","Company Administrator **FULL ACCESS**","England & Wales","Timesheet: MONTHLY Employees (Days)","Monthly","Commercial & Procurement Team"
"000814","814","Steve.Harris","Steve","Harris","Active","Yes","No","","01234 567893","09876 543213","01204 507090","Company Administrator **FULL ACCESS**","England & Wales","Timesheet: MONTHLY Employees (Days)","Monthly","Human Resources"
Use the split function to get the individual values
dim s() as string
Myvalue=s(2) ‘second value

Myvalue=split(mystring, “,”)(2)

alternatively save the string to a .csv file and use transfertext or sql to populate your table
Thanks CJ I can see what you're getting at with the Split function. Unfortunately I'm getting a "Subscript out of Range" error :( something to do with their being multiple rows and line breaks (i think) time to hit up google again I think :D
Saving the string as a text file and then accessing it with normal DAO methods is not an option?
Saving the string as a text file and then accessing it with normal DAO methods is not an option?
It could be... I would need to do it programatically and bearing in mind this could be on any number of Users PC's, it seemed like the more onerous option, but I am open to all ideas, this is new to me :)
Arrays start at 0?
umm yes - my bad:(

something to do with their being multiple rows and line breaks (i think)
in that case probably better/easier to save as textfile and use transfertext or sql. Particularly if this is to populate a number of records

If you want to stick with the array option, do two splits

first split on the linebreak char/s - you'll need to view the ascii codes to determine whether it is a linefeed, new line or both (usually chars 10 ,13 or 10 and 13)

then iterate through these elements for the specific columns

to iterate through the characters use something like

dim b() as byte
dim i as integer


for i=0 to 100 ' change 100 to whatever number gets you past the first row or use ubound(b)-1 for the whole string
    debug.print i; asc(b(i)); chr(b(i))
next i
A possible way as a dummy code:
dim TempCsvFile as String
dim rst as dao.Recordset

TempCsvFile = GetTempFileFullPath(Extension:=".csv")
LoadCsvDataToTextFile TempCsvFile

set rst = OpenRecordsetFromCsvFile(TempCsvFile)

private sub LoadCsvDataToTextFile(byval TempCsvFile as string)
' Your Code to get CSV data ... maybe instead of the string return a text file download can be done right away?
' if not:
     SaveStringToTextFile TempCsvFile, GetCsvDataStringFromRequest()
end sub

private sub SaveStringToTextFile(byval TempCsvFile as string, byval CsvDataString as String)
    ' create textfile with Scripting.FileSystemObject or with Open ... For Output
end sub

private function GetCsvDataStringFromRequest() as String
'     Your code to get the csv string
     GetCsvDataFromRequest = ....
end function

private function OpenRecordsetFromCsvFile(byval CsvFile as string) as DAO.Recordset

    dim SelectSql as String
    dim CsvFileFolderPath as String
    dim CsvFileName as String
    dim PathFileNameCutPos as long

    PathFileNameCutPos = instrrev(CsvFile, "\")
    CsvFileFolderPath = left(CsvFile, PathFileNameCutPos - 1)
    CsvFileName = replace(mid(CsvFile, PathFileNameCutPos + 1), ".csv", "#csv"
    SelectSql = "select * from [Text;DSN=...;FMT=Delimited;HDR=YES;IMEX=2;CharacterSet=850;DATABASE=" & CsvFileFolderPath & "].[" & CsvFileName & "] AS T"
    '                           ^- I think it is true, but I'm not sure.

    set OpenRecordsetFromCsvFile = currentdb.Openrecordset(SelectSql)

end function
Last edited:

Users who are viewing this thread

Top Bottom