How to programmatically read a CSV file line by line
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 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:
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()
Comments
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.
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()
}Thanks Tomoko!
This is super helpful. Thank you!
This is actually super useful for my LSCUCA app that us interns are working on in LS.
Very helpful
I have a question: Could be possible to read a csv file with Header (field names) using this function?
Thanks
Hi @Luis Petkovicz!
Consider to try csvgen also?
it will be:
USER>zpm "install csvgen"
to install the package, and here is the usage:
USER>do ##class(community.csvgen).Generate("/folder/filename.csv")
to generate class and import the data into it from an arbitrary csv.
Found this method very interesting, thank you very much!!!