How to read CSV file contents into Objects in Cache

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)
    }
}

 

Answers

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.

Comments

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

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
 

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!

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

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

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!

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. 

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

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. laugh
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 InterSystemssmiley

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