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

XelaIrodavlas

Registered User.
Local time
Today, 21:38
Joined
Oct 26, 2012
Messages
174
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

Code:
"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","TonyBridges@mymail.com","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","ClarkKenton@mymail.com","","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","SteveHarris@mymail.com","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
Either
dim s() as string
S=split(mystring,”,”)
Myvalue=s(2) ‘second value

or
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

Code:
dim b() as byte
dim i as integer

b=mystring

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))
    i=i+1
next i
 
A possible way as a dummy code:
Code:
dim TempCsvFile as String
dim rst as dao.Recordset

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

set rst = OpenRecordsetFromCsvFile(TempCsvFile)

Code:
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

Back
Top Bottom