Complex CSV export

[Barry Kahn]

Hi John,

With a lot of help from this list and off list from Rob Campbell in  
Canada, I've put together various procedures that allow us to get  
data out of Panorama and into text files that we can upload to our  
shopping cart. So my guess is that using arraybuild and other  
statements, it will be possible to assemble some procedures to get  
your data into the correct format.

I don't know about trying to do it via the export wizard but I think  
the procedure route would be easier. I'd need a clearer picture of  
what you need to do before I could see which bits of our code might  
work for you, but here's one example which outputs header names along  
with data:


local theExportText,theFieldNames

theFieldNames=dbinfo("fields","")
theFieldNames=replace(theFieldNames,¶,",")

arraybuild theExportText,¶,"",crtovtab(exportline())

theExportText=replace(theExportText,¬,",")


theExportText=theFieldNames+¶+theExportText     /*Puts the current  
field names across the top with the data in tab separated fields  
below.*/

This bit saves it to the server with the correct file name:

FileSave "", "Dell_Shared:clonboxesrev.txt","",theExportText


I'm not a programmer but with enough hard objects and nails, I can  
eventually build something, and with enough help from the Panorama  
experts I can eventually accomplish small database miracles. The  
above code extracts the field names from all the fields in the  
current database, then grabs all the data, and exports it all as a  
neat text file with the field names at the top and the data aligned  
below. It is possible to do this either with tabs or commas  
separating the fields. (And if anything I just said is wrong, let me  
know, folks!)

Another super-useful command is the arrayselectedbuild statement  
which instead of arraybuild which grabs everything, works just with  
the selected data.

It is also possible--and from your description I think you'll need  
it--to specify which fields you want to put in the array. That would  
look like this:


/* array export - selected fields - save dialog */

local theExportText
arrayselectedbuild theExportText, ¶ ,"",Field1+ ¬ +Field2  /*putting  
selected fields only into the array with tabs to delimit.*/

with as few or as many fields included as you need. One of the  
obvious advantages of this is that fields can be arranged in any order:

Field9+ ¬ +Field2  etc.

Or fields can have text appended to them, adding a $ sign to a price  
field for example:

ItemNum+ ¬ +"$"+str(TubePrice)+ ¬ +

Or the field output can be modified. Say I need to output prices for  
half packs of some items:   str(Price)/2+ ¬ +

I use tabs between the output fields but you could use commas:   +,+

And if you want to combine field output, use a + sign and no field  
delimiter:

Field1+" "Field2 -- If Field1 is "Good" and Field2 is "dog", then  
you'll get Good dog as output.


Just think what can be done by people who actually know what they're  
doing!

So my non-programmer take on your question is that it can probably be  
done via a well-crafted procedure.  If you want to give more specific  
examples, I'm sure the list experts will be happy to help.

bk





On Jul 20, 2008, at 7:38 AM, John Scott wrote:

> My shopping cart is designed to interface with QuickBooks, but I am  
> a die-hard Panorama user (since OverVue).  I need to export a  
> complex CSV file to import into my shopping cart.  The file format  
> has several sections, each with header info on multiple lines, and  
> then data, sometimes combining info from several records onto a  
> single line.
>
> My understanding of the export wizard is somewhat limited (I am an  
> old-school forms kinda guy), but the multiple-line headers, and  
> combining data from a non-fixed number of records onto a single  
> line both seem incompatible with this route.  Generally, I would  
> prefer to solve this using scripts and forms.
>
> I can produce the CSV lines readily using multiple forms, but am  
> unable to output them into a single file with appropriate header  
> info before each section.
>
> 1) Is there any way to append to a single text file when I use save  
> as text?
>
> 2) How can I write multiple lines of header info to a text file  
> (the text from a header tile is ignored when I save as text)?
>
> I considered exporting into separate files, bringing them back into  
> a single Panorama file (I could then script in the header info  
> between each import), but since these are csv files Panorama wants  
> to break each line imported into multiple fields.
>
> Thanks...
>
> -John
> _______________________________________________
> Qna mailing list
> Qna at provue.com
> http://provue.com/mailman/listinfo/qna