How to get row count of csv using ObjectScript

Primary tabs

Hi,

     I have written a procedure with the help of object scripting to export data to a csv file. There is more data than  the csv limit.

Can anyone please tell me how to get the row count of csv file using object scripting, so that I can write an if condition and write to a second csv file.

Please find the code writing to the csv given below.

while (ind '= ""){
set row = ^CacheTemp(repid,"MAIN",ind)
use filemain write row,!
; Get next row index for MAIN report
set ind = $order(^CacheTemp(repid,"MAIN",ind))
} close filemain
}

 

 

Thanks

Jude

 

  • 0
  • 0
  • 266
  • 9
  • 5

Answers

Here are a couple of ways for getting information about a file:

Get file size:

set file=##class(%Stream.FileBinary).%New()
write file.LinkToPath("C:\temp\test.csv")
write file.Size

Get number of lines:

set file=##class(%Stream.FileBinary).%New()
write file.LinkToPath("C:\temp\test.csv")
while 'file.AtEnd {
    do file.ReadLine()
    set linecount=$i(linecount)
}
write linecount

 

For example:

set maxrows = 1000
set currentrow = 0
while (ind '= ""){
    set row = ^CacheTemp(repid,"MAIN",ind)
    if currentrow>maxrows  {
        set currentrow = 0
       // swap files
    }
    set currentrow  = currentrow  + 1
    use filemain write row,!
    ; Get next row index for MAIN report
    set ind = $order(^CacheTemp(repid,"MAIN",ind))
}
close filemain

Are you by chance exporting SQL queries to CSV? If so it can be done automatically:

do ##class(%SQL.Statement).%ExecDirect(,"select * from ...")).%DisplayFormatted(100, filename)

Hi Eduard,

 I am writing a routine to extract data from database and scheduled for a particular day. Recently I came across that the data is going beyond the csv limit.

Thanks

Jude

I don't think it's a CSV limit.

 

Device's limit : yes; CSV's limit : no.

This will do exactly what you want any problems give us shout

ClassMethod ImpCSV(fileName As %String = "C:\filepath where csv file is")
{
	;;Refresh globals data at the begin of proecess
	;;globals hold the file being written to data and the count of lines each file has 
	k ^ufile,^ufile2,^counter,^counter2
	set Reader =##class(%Stream.FileCharacter).%New() 
	
	;;link to the file to read	 
	set status=Reader.LinkToFile(fileName)
		
   if $$$ISERR(status)
	{
		do $System.Status.DisplayError(status)
	}
	;;initialise the counter for the file lines to write to one file	 
	set ^counter =0
	set ^counter2 =0	
	set ^ufile=""
	set ^ufile2=""
    
    ;;the count for the lines read in
	set lineCount=0
	;;start process the lines
	while 'Reader.AtEnd
	{
		;;read the incoming file
		set line=Reader.ReadLine()
		;;increment the line count by one as you read the next line
		set lineCount=lineCount+1		
	
	    ;;piece the column to check
	    ;;one is for the first column and so on
	    ;;string at after the equal sign is the value to check for 
		if ($piece(line,",",1)="Religion")
		{
			;;increment count for this file
			 set ^counter=^counter+1
			  ;;the counter is at one to assign the file path to write to
			   if (^counter=1)
				{
					;;set the file name to write to the global
					set ^ufile=..writeFileOut("fileone",".csv",line,,^counter)
				}
				else
				{
					;;check the count has no reached the limit for the number of lines to write to
					if (^counter=51)
					{
						;;reset the count if limit reach
						set ^counter=1
					    ;;new file
					    set ^ufile =..writeFileOut("fileone",".csv",line,,^counter)
					    }
					    else
					    {
						    ;;use the current file
						    do ..writeFileOut(,,line,,^counter,"REG")
						 }
					}			
		  }
		  ;;second check for the next set of data if you have more than 
		  ;;two to check for copy the below and change accordingly to accommodate the next check
		  ;;same as above
		  if ($piece(line,",",1)="description")
		  {
			  ;;set attributes for the second file to write to
			   set ^counter2=^counter2+1
			   if (^counter2=1)
			   {
					set ^ufile2=..writeFileOut("fileTwo",".csv",line,,^counter2)
				}else
				{
					if (^counter2=51)
					{
						set ^counter2=1
						;;new file
						set ^ufile2= ..writeFileOut("fileTwo",".csv",line,,^counter2)
					}else
					{
						do ..writeFileOut(,,line,,^counter2,"DESC")
					}
				}				 			
	}
		  
}

 kill ^ufile,^ufile2
}

ClassMethod writeFileOut(filename, fileext As %String, line As %String, directory = "C:\directory to store files", linecount As %Integer, FileToUse)
{
	set oLF = ##class(%Library.File).%New()
	if (linecount=1)
	{
		
	    Set filenam=directory_filename_$i(^timmmy)_fileext
	    set oLF.Name=filenam
	    if (oLF.Open("wns"))
	    {
		    set linecount=linecount+1
		    do oLF.WriteLine(line)
		    do oLF.Close()
		    ;;w oLF.Name
		    quit oLF.Name	
	   }
	}else
	{
		if (FileToUse="DESC")
		{
		     if (^ufile2'="")
		     {
		         set oLF.Name=^ufile2
			}
		}elseif(FileToUse="REG")
		{
		     if (^ufile'="")
			 {
			     set oLF.Name=^ufile
			  }
		}
	    If (##class(%File).Exists(oLF.Name))
	     {
		     
		     Do oLF.Open("EWA")
		     if (linecount'=51)
		     {
			     set linecount=linecount+1
		         do oLF.WriteLine(line)
		         do oLF.Close()
		          quit oLF.Name
		     }
		 }
	}
}

A very simple approach  just in case... but  if ind is numeric you can try to get the first and  last ones with:

 

set first $order(^CacheTemp(repid,"MAIN","" ))

set last $order(^CacheTemp(repid,"MAIN","", - 1))

 

and then do the math to know how many entries (at most) there can be. 

 

Comments

Is there any way we can check a particular column data before writing to the file ? Based on the data I can write the data to a different files.

Can you show your code and explain what are you trying to achieve?

Hi,

       I need to write to different csv files based on a column data. So I need to check the column data before writing to file.

Here is the code to write the data to file.

while (ind '= ""){
set row = ^CacheTemp(repid,"MAIN",ind)
use filemain write row,!
; Get next row index for MAIN report
set ind $order(^CacheTemp(repid,"MAIN",ind))
} close filemain
}

Thanks

Jude

Something like this?

while (ind '= ""){
    set row = ^CacheTemp(repid,"MAIN",ind)
    if row [ "keyword" {
        use filemain write row,!
    } else {
        use filemain2 write row,!
    }
    ; Get next row index for MAIN report
    set ind = $order(^CacheTemp(repid,"MAIN",ind))
}
close filemain

I am looking something similar like that but based on a particular table column data.

thanks

Show examples of the row variable. What condition do you want to check?