Question
Thembelani Mlalazi · Oct 9, 2017

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

0
0 3,892
Discussion (13)5
Log in or sign up to continue

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

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.

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
 

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

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

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.

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)

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?

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!