Replace function fails with more than 1 character in 3rd parameter

Panorama "Classic"
1

I have the following statement:

db = arrayfilter(db, chr(10), {replace(import(),chr(9), ",")})

which works as expected.

However, if I change the 3rd parameter to replace so that it has more than one character, I get a failure.

For example:

db = arrayfilter(db, chr(10), {replace(import(),chr(9), "<,>")})

I really would like to replace the tab character with ‘","’ (quotes around a comma), but cannot come up with the correct incantation to make that work.

Any thoughts from you Panorama experts and veterans?

Thanks!

– Mark

2

You need to use an alternative quoting character, if you want to include quotes in the quoted text. One possibility is to use pipes.

db = arrayfilter(db, chr(10), {replace(import(),chr(9), ||","||)})

It’s also possible to put quotes within quotes by doubling them up, but that gets messy.

db = arrayfilter(db, chr(10), {replace(import(),chr(9), """,""")})

Dave

3

Hi Dave,

Thanks for the hints!

But, I have tried those already.

Here’s another hint: if I manually subdivide database down into smaller “chunks”, then it works as expected. So, I suspect the size of the array is the problem.

A text file length of “2,374,201” seems to be too long. :slight_smile:

Question: Given an array containing the contents of a database created with arrayselectbuild() using exportlinenotabs(), is there an easy way to write “chunks” of the array to a file using file append()?

Thanks,

– Mark

P.S. here’s more of the whole script that might shed some light. The goal is to write out a CSV file with quotes around each field. Additionally, it strips out potential chr(13) and chr(10) characters embedded within fields.

// write out field header names first, as a quoted, comma-delimited string

db = sandwich('"',replace(replace(dbinfo("fields", ""),'"','""'),chr(13),chr(9)),'"')+chr(10)
filesave myfolder, myfile, "", db

// build a tab-delimited array, with chr(13) and chr(10) characters stripped out of field data
db = arrayselectedbuild(chr(10), "", {'"'+replace(replace(replace(exportlinenotabs(),chr(13),""),chr(10),""),'"','""') + '"' +chr(10)})

// convert tab, chr(9), characters to quoted commas
// *on a database with 7447 records, this "blows up" with a "Runtime error in call( procedure."*
db = arrayfilter(db, chr(10), {replace(import(),chr(9), ||","||)})

fileappend myfolder, myfile, "", db
4

I think I got it. I appreciate your help! :slight_smile:

In case it’s helpful to others, here’s what I got:

// write out field header names first, as a quoted, comma-delimited string
db = sandwich('"',replace(replace(dbinfo("fields", ""),'"','""'),chr(13),chr(9)),'"')+chr(10)
db = arrayfilter(db, chr(10), {replace(import(),chr(9), ||","||)})
filesave myfolder, myfile, "", db

// build a tab-delimited array, with chr(13) and chr(10) characters stripped out of field data
db = arrayselectedbuild(chr(10), "", {'"'+replace(replace(replace(exportlinenotabs(),chr(13),""),chr(10),""),'"','""') + '"'})

// convert tab, chr(9), characters to quoted commas
// *on a database with 7447 records, this "blows up" with a "Runtime error in call( procedure."*
// db = arrayfilter(db, chr(10), {replace(import(),chr(9), ||","||)})

itemIndex = 1
lastItemIndex = arraysize(db, chr(10))
itemChunkSize = 5000
loop
    // grab a "chunk" of the array
    dbChunk = arrayrange(db, itemIndex, min(itemIndex+itemChunkSize, lastItemIndex), chr(10))

    // convert tab, chr(9), characters to quoted commas
    dbChunk = arrayfilter(dbChunk, chr(10), {replace(import(),chr(9), ||","||)})

    fileappend myfolder, myfile, "", dbChunk
    itemIndex = itemIndex + itemChunkSize
while itemIndex < lastItemIndex
5

I think you ran afoul of the default limit on expression stack size. 2,374,201 was really close, and replacing every tab with 3 characters probably pushed you over the top. Thankfully, expression stack size is a thing of the past in Panorama X.

Dave