Easiest way to bulk add data to shared database

What is the easiest way to bulk add data to a shared database? I gather from the documentation that you can’t drop the server and then add, because this locks the database records. I finally figured this out after trying to do an ‘opentextfile’ with append. Is the only way to do it to take the database offline completely and then do a new generation through the new generation dialog?

This seems very cumbersome, as I need to enter new batches of records regularly, but if that’s the way it works, so be it.

If there’s a way to code some of this to make it a little less manual, that would be great.

My bulk imports are generally small. Could I get away with programatically adding records and then populating the fields individually with each tab delineated piece of data (array element), or would this be too slow? (10-30 records, generally)

Okay, I think I’ve answered my own question. I opted to programmatically loop to add records one at a time (insert below) and then enter each piece of data in its field, and it worked, albeit a bit slowly. With the use of noshow, I added thirteen records with 10 fields’ worth of data each in a couple of seconds (didn’t time it, but it was tolerable) without having to do a shared database generation. I can live with that.

There’s nothing special required, you can import text into a shared database just as you would with a single user database. Panorama takes care of uploading the text to the server for you, there are no special steps required. I’m not quite sure what you mean by “drop the server” but you definitely don’t want to disconnect – just as for any other database change, the server must be connected to modify the data.

The fundamental way to do this is with the importtext statement.

When using this statement, Panorama will import the text into the local database, then upload it to the server and add it there. Other users will see the text the next time they synchronize. I’m pretty sure I showed this in one of the later class sessions (quite possibly the last session).

All Panorama text import is ultimately based on the importtext statement, so using the opentextfile statement will also work, as will importing from the File menu.

I tried this, and I get the error message ‘Database field structure cannot be changed when a database is shared using Panorama Server.’

…and the very same data (in a variable), adds records correctly when I pick it apart line by line and field by field, as I described above.

Procedure code below. I’ve commented out the method used to pick through the data (toward the bottom of this code).

global newimportfolder,newimportfilelist,importsize,importblanks,importfilename,origfilename

local counter
newimportfolder="~/Desktop/Exported Split Income/"

newimportfilelist=listfiles(folder(newimportfolder),“TEXT???”)

importsize=arraysize(newimportfilelist,¶)
origfilename=info(“databasename”)
importfilename=""

if newimportfilelist=""
rtn
endif

counter=1
importblanks=0

local thisfile
global totalfilesarr
totalfilesarr=""
global gacctnumbers
gacctnumbers=acctsnumbers

loop
setwindow 1,1,1,1,""
importfilename=newimportfolder+array(newimportfilelist,counter,¶)
opentextfile importfilename
field B
formulafill B[1,6]
field V
formulafill array(array(gacctnumbers,arraysearch(gacctnumbers, “*”+«B», 1, cr()),cr()),1,"/")
arraybuild thisfile, cr(), “”, exportline()
if info(“records”)=0
FileTrash newimportfolder,array(newimportfilelist,counter,¶)
counter=counter+1
closefile
importblanks=importblanks+1
else
window “Mail Log 2:Log Entry”

    totalfilesarr=totalfilesarr+thisfile+cr()

    window "Untitled"
    fileglobal archivetext,archivefolder
    archivefolder=folder(folderpath(info("DesktopFolder"))+"Archived Split Income:")
    archivetext=""
    arraybuild archivetext,¶,"",exportline()
    FileSave archivefolder,array(newimportfilelist,counter,¶),"TEXTttxt",archivetext
    Closefile

    FileTrash newimportfolder,array(newimportfilelist,counter,¶)

   counter=counter+1

endif
until counter>importsize
totalfilesarr=totalfilesarr[1,-2]

window “Mail Log 2:Log Entry”
importtext totalfilesarr

global totallines
totallines=arraysize(totalfilesarr,cr())

/*
counter=1

window “Mail Log 2:Log Entry”

noshow
lastrecord

loop
insertbelow
«Date Received»=date(array(array(totalfilesarr,counter,cr()),1,tab()))
«AccountNumber»=array(array(totalfilesarr,counter,cr()),2,tab())
«Account»=ay(array(totalfilesarr,counter,cr()),22,tab())
«BillFrom»=array(array(totalfilesarr,counter,cr()),3,tab())
«Amount»=val(array(array(totalfilesarr,counter,cr()),4,tab()))
«Date Due»=date(array(array(totalfilesarr,counter,cr()),5,tab()))
«DistributedVia»=array(array(totalfilesarr,counter,cr()),6,tab())
«Description»=array(array(totalfilesarr,counter,cr()),18,tab())
«Job»=array(array(totalfilesarr,counter,cr()),23,tab())
«Department»=array(array(totalfilesarr,counter,cr()),24,tab())

counter=counter+1
until counter>totallines

endnoshow
showpage
*/

call .searchtotal

alertsheet “You have imported “+str(importsize)+” split income report”+?(importsize>1,“s”,"")+?(totallines>0 and totallines<>""," with “+str(totallines)+” records","")
+?(importblanks>0," and “+str(importblanks)+” blank income report"+?(importblanks>1,“s.”,""),".")

rtn

The problem is that by default, the importtext statement will automatically add fields as needed based on the imported text. This isn’t allowed in a shared database. I assume, however, that your text contains the same number of columns as the database. So to get this to work, you simply have to add a parameter to the importtext statement, like this:

importtext totalfilesarr,"AddFields","NO"

This requirement is mentioned on the importtext help page (in the ADDFIELDS section), but I can see how it would be easy to miss if you didn’t carefully read the entire page. So I’ve just now changed this statement so that when working with a shared database, the ADDFIELDS option now defaults to NO. So once that is released, your code above would work without modification. This change will also allow the opentextfile statement to work with shared databases. But for now, the quick change above will get you working immediately, and will still work fine when the new update comes out.

Thanks, Jim, that did the trick.

1 Like

Jim, is it possible to replace the data in a database with this method?

ImportText FileLoad(file), “ExistingData”, “Replace”, “AddFields”, “No”

I am getting an error saying I can’t replace data in a shared database. I have assumed that I must take the database offline in order to replace a block of data.

No, Panorama doesn’t allow bulk deletion of data in a shared database. I discussed the detailed reasons for this in one of the sessions earlier this year.

If this is really something you need to do, the way to do it is to start a new generation, then import the text while replacing all data, then finish the new generation (and in the process, uploading the new data).