How to read CSV file contents into Objects in Cache

Question

Thembelani Mlalazi · Oct 9, 2017

I have this code here;I am trying to read a csv file but when I run this code I get <LIST>%open+3^%stream.Object.1 would anyone have a look and tell me where I am going wrong .Record mapper is fine but I need a custom csv Importer

ClassMethod ImportCSV()
{
   set adapter =##class(%File).%New()
  
  set status=adapter.%Open("C:\In\in.csv")
  
  if $$$ISERR(status)
  {
   do $System.Status.DisplayError(status)
  }
  
  set counter =1
  
  while 'adapter.AtEnd
  {
   set line=adapter.ReadLine()
   
   set rec= ##class(CSVRecord).%New()
   
   set rec.ID=$piece(line,",",1)
   set rec.FName=$piece(line,",",2)
   set rec.SecName=$piece(line,",",3)
   set rec.Gender=$piece(line,",",4)
   set rec.Age=$piece(line,",",5)
   
     write rec.ID,
 
             rec.FName,

rec.Secname ,

rec.Gender,

rec.Age,! Set savestatus=rec.%Save() if $$$ISERR(savestatus) { do $System.Status.DisplayError(status) } } //if error found during processing,show it if $$$ISERR(status) { do $System.Status.DisplayError(status) } }

0 3,892

Sami Pietinen · Oct 9, 2017

Use a record map instead. The record map object can be easily used to read a CSV file line by line.

0 0

Thembelani Mlalazi · Oct 10, 2017

Thank you for your advice but it worked with %Stream

Eduard Lebedyuk · Oct 9, 2017

Here's how you can import CSVs into Caché without writing any code yourself. I recommend this approach.

In your example, replace:

set adapter = ##class(%File).%New()
set status = adapter.%Open("C:\In\in.csv")

with:

set adapter = ##class(%File).%New("C:\In\in.csv")
set status = adapter.Open("R")

Check out the documentation for %File class.

Yaniv Ben Malka · Oct 10, 2017

Use this method for reading the file:

ClassMethod ReadFile()
{
set stream=##class(%Stream.FileCharacter).%New()
set sc=stream.LinkToFile("c:\test.csv")
do stream.Rewind()
while 'stream.AtEnd {
set line=stream.ReadLine()
W line,!
}
}

***Make sure that the path of the file is on the computer that runs cache instance

Robert Cemper · Jun 11, 2018

You verified the error message of the original question. This doesn't work.

<LIST>%open+3^%stream.

You could try to apply the ACCEPTED ANSWER

Marco Blom · Jun 12, 2018

Hi Robert, you are right, but I forgot to mention I got the error after adding Eduard his recommendation.

This is the code:

ClassMethod ImportCSV()
{
  set adapter = ##class(%File).%New("C:\temp\in.csv")

  set status = adapter.%Open("R")

  if $$$ISERR(status)
  {
   do $System.Status.DisplayError(status)
  }

  set counter =1

  while 'adapter.AtEnd
  {
   set line=adapter.ReadLine()

   set rec= ##class(CSVRecord).%New()

   set rec.ID=$piece(line,";",1)
   set rec.FName=$piece(line,";",2)
   set rec.Secname=$piece(line,";",3)
   set rec.Gender=$piece(line,";",4)
   set rec.Age=$piece(line,";",5)

     write rec.ID,

             rec.FName,
             rec.Secname ,
             rec.Gender,
             rec.Age,!

         Set savestatus=rec.%Save()

      if $$$ISERR(savestatus)
    {
     do $System.Status.DisplayError(status)
    }

    }

    //if error found during processing,show it
    if $$$ISERR(status)
    {
     do $System.Status.DisplayError(status)
    }
}

I do notice this topic comes back in time in the Community, so people are interested -and struggling with it like myself, but if we look back the items leave quite unanswered.... as people do not share their final working code... and only tell that they have solved the question.

Thanks!

Robert Cemper · Jun 12, 2018

Marco you are RIGHT !

The example is just wrong! And never got fixed.

Instead of set status = adapter~~.%Open("R")~~

it should be set status = adapter.Open("R")

This are 2 differnet methods with total different incompatible parameters.

method Open(mode As %String = "", timeout As %Integer = 0) as %Status
final classmethod %Open(soid As %ObjectIdentity, concurrency As %Integer, ByRef sc As %Status = $$$OK) as %ObjectHandle

it is good practice to close the file after use by do adapter.Close()

Eduard Lebedyuk · Jun 12, 2018

My advice is not to write your own code, but to use this guide. You don't need to parse CSV manually.

1 0

I agree with you!

But you know,
reinventing the wheel is mostly much more fun than applying some existing tool and READING the user guide.
Especially in the software business.
It's like hunting a rabbit or going to the butcher or even to the restaurant or order by web.

HI Robert, I have got it working now, and yes, the harder you struggle the bigger the victory when you succeed. And it is the charm of InterSystems

Thanks Eduard, I will come back on you're recommendation for sure in short time, as I did not fully understood last time.

Stephan Gertsobbe · Dec 9, 2018

The problem with the CSV SQL procedure is that it doesn't take care of the masked delimiters.

if the file for the above sample would contain a line (if the masking character is the very common and usual ")

4711,"Peter, Paul and Mery","Sample",undecided,500

the 2. Part is only read as Peter (at least on Caché 2017.2 and IRIS 2018.1.2 so atm. the only chance when the data doesn't play nicely you have to implement the CSV reading yourself (which is much slower)

Eduard Lebedyuk · Dec 9, 2018

Are you talking about %SQL.Util.Procedures:CSVTOCLASS?

I've used it many times and it works with masked delimiters just fine.

Can you post an example?

Marco Blom · Jun 11, 2018

Hi there, tried the code sample above and got this:

Why?

Unfortunately the community & books are full with half code snippets rather than showing working full code samples>

Themelani, can you please share you're working code to the community? then the item is completed! Thanks!

How to read CSV file contents into Objects in Cache

Social networks

InterSystems resources