Export CSV

Question

Question

sansa stark · Oct 17, 2016

#Beginner #CSV #Object Data Model #Caché

Hi All,

We created CSV file in cache.

There was one problem, how we insert "," comma in values.

Thanks,

sansa.

Discussion (8)0

Log in or sign up to continue

Steve Riddle · Oct 17, 2016

Hi Sansa,

Its not neccessarily the prettiest way to do it but this wil work :-

U file w $select(name[",":$c(34)_name_$c(34),1:name)_","_$select(details[",":$c(34)_details_$c(34),1:details),!

Personally I would use $c(13,10) as an end of line marker rather than "!" as using "!" can have issues with certain software/operating systems.

Steve

0 0

John Peck · Oct 19, 2016

set Header=$$Line("Sequence\HeatNo\Calalogue\First\Surname\Bisca No\LeftHand\Flexible\Country\Exhibitor No")

set NewFileName=$p(Target_"\"_$$$NewDocument(PageTitle),".",1)_".csv"
        set File=##class(abc.file.FlatFile).%New(),xcount=0
        do File.WriteLine(Header)

set Seq=Seq+1,Line=Seq_"\"_$lg(ExportData,12)_"\"_CatNo_"\"_$lg(ExportData,3)_"\"_$lg(ExportData,4)_"\"_$lg(ExportData,16)_"\"_$lg(ExportData,18)_"\"_$lg(ExportData,17)_"\"_$lg(ExportData,15)_"\"_$lg(ExportData,10)
        set Data=$$Line(Line) do File.WriteLine(Data)

do File.SaveFile(NewFileName)
    kill File            
    Q

Line(str)
    set ln=$l(str,"\"),New=""
    for lp=1:1:ln set New=New_$c(34)_$p(str,"\",lp)_$c(34,44)
    quit $e(New,1,$l(New)-1)
    q str

Procedure

build the Taget folder and make sure that the file Name is legal and ends in.csv

I built my row (line) data with a delimitor "\"

then I pass this through the $$Line(Line)

1) open the file and add a header to identify the columns

2) loop down the data build the Line of data and file the 'do file.WriteLine(Line)' for each row

3) Close the file and kill the file object

1 0

score 0 · Answer 1 · 2016-10-17T06:57:45-04:00

There are lots of ways how to export data in CSV format in Caché. Add here your or code, or some more information. How you did it, and after that we may help.

score 0 · Answer 2 · 2016-10-17T07:40:00-04:00

S file="C:\Export.CSV"
O file:("RSN"):2
U file w name_","_details,! ->we get the detlis (name,details) from table via result set.
C file

score 0 · Answer 3 · 2016-10-17T08:06:46-04:00

sansa stark · Oct 17, 2016

thanks steve ,it is working.

i need one more clarification how can i create xls file in cache?

0 0

score 0 · Answer 4 · 2016-10-17T08:13:27-04:00

XLS, files much more difficult that plain CSV. And depends on which version you want. XLS - it is a binary format, but XLSX already bunch of XML files but compressed in one file.

You can try to use ZEN reports, which supports XLS as one of output formats.

score 0 · Answer 5 · 2016-10-17T10:47:17-04:00

Eduard Lebedyuk · Oct 17, 2016

SQL to Excel

0 0

score 0 · Answer 6 · 2016-10-20T07:00:47-04:00

Typically this is a very common issue I have observed many times.

If I have understood this correctly, you have comma(,) in values. You want to export that values into a comma(,) separated CSV file. So, the comma(,) in values are also being considered as delimiter.

Easy solution would be enclosed it with double quote. by default if you open the csv file into EXCEL, commas in values will not be considered as delimiter if its enclosed in double quote.

So, with reference to your code:

S file="C:\Export.CSV"
O file:("RSN"):2
U file w """"_name_""","""_details_"""",!
C file

Two double quote will represent one double quote finally in the output ("")->(").

Please let me know if you tried this, and this resolved your problem.

Thanks,

Tirthankar