How to programmatically read a CSV file line by line

Article

Tomoko Furuzono · Aug 29 1m read

#ObjectScript #SQL #InterSystems IRIS #Caché

InterSystems FAQ rubric

This can be achieved by using the CSV() procedure of the %SQL.Util.Procedures class.
Below is an example of usage code. (Assuming that the file test.csv is in c:\temp.)

Set rowtype="Name VARCHAR(50),UID VARCHAR(50), PHONE VARCHAR(50)"
Set filename="c:\temp\test.csv"
Set result=##class(%SQL.Statement).%ExecDirect(,"call %SQL_Util.CSV(,?,?)",.rowtype,.filename)
Set rset =result.%NextResult()

// To display all results, use do rset.%Display()
While rset.%Next() {
Write "Name:",rset.%GetData(1)," UID:",rset.%GetData(2)," PHONE:",rset.%GetData(3),!
}

Set rset="",result=""
Quit

By executing the above, you can access each row and field as a result set. Example execution:

USER>do ^CSV
Name    UID     PHONE
aaa 0001    080-1111-1111
bbb     0003    090-2222-2222
ccc     0009    050-3333-3333
ddd     0010    0120-17-1972
4 Rows(s) Affected

Please also see the class reference for the %SQL.Util.Procedures class.
Class reference：%SQL.Util.Procedures.CSV()

Go to the original post written by @Tomoko Furuzono

Evgeny Shvarov · Aug 29

Very convenient!

Also if you want to read any CSV, create a class out of it and load the data give it a try to others two options:

1. csvgen - an objectscript module that lets you read, create class, and load data from an arbitrary CSV in one command.

2. csvgen-python - same as csvgen but written in embedded python.

1 0

David Satorres · Sep 2

Hi!

I can't see the benefits of calling a SP (!!!) instead of "normal" cache code:

Set file = ##class(%File).%New("c:\temp\test.csv")
Set sc = file.Open("R")
While 'file.AtEnd {
       Set line=file.ReadLine()
}

In any case, it's good to know other ways to get to the same place :)