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

[XelaIrodavlas]

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","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"

 

[CJ_London]

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

 

[XelaIrodavlas]

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

 

[Gasman]

Arrays start at 0?

 

[Josef P.]

Saving the string as a text file and then accessing it with normal DAO methods is not an option?

 

[XelaIrodavlas]

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

 

[CJ_London]

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

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

 

[Gasman]

umm yes - my bad

That was addressed to the O/P in case they were thinking the array started as 1, hence the subscript out of range?

 

[Josef P.]

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