IMPORTANT ANNOUNCEMENT

These forums will be permanently set to read-only mode on July 18, 2022. From that day on, no new posting or comment will be allowed on the site, but the historical content will remain intact and searchable.

A new location for posting questions about PlanetPress Suite is now available:

OL Learn - PlanetPress Classic (opens in new tab)

Topic Options
#58214 - 02/15/22 06:16 PM PlanetPress Database SQL to CSV thru VBscript
Tom@ECI Offline
OL Newbie

Registered: 04/20/10
Posts: 24
Loc: Fort Worth, Tx
I am stuck on the script that pulls orders to be delivered and writes them to a CSV file.

1. Create File
2. PlanetPress Database pulls the data using a SQL script.
3. Change emulation to database
4. Set Job variables from SQL which can be seen the deliveries in the debug message window.
5. Branch
6. Create Metadata using a generic PDF form
7. Change Elumation to CSV (is this needed?)
8. The VBscript below to dump the results to a CSV. It creates the headers but no detail from the captured variables is written to the CSV. I "borrowed" this script from elsewhere in this form and I confess I don't understand the MetaData relationship to this.

'this script creates a CSV file containing deliveries

Option Explicit
Dim MyMeta, x, MyCSVLine, FSO, ft

Set MyMeta = CreateObject("MetaDataLib.MetaFile")
MyMeta.LoadFromFile(Watch.GetMetadataFileName)

'create CSV
Set FSO = CreateObject("Scripting.FileSystemObject")

Set ft = FSO.OpenTextFile("C:\Users\ddms_user\Desktop\MNFEXC.CSV",2,True)

ft.WriteLine("Distribution_Center,Route,Delivery_#,PO_#,Type,Delivery_Date,Account,Customer_Name,Site_Address_1,Site_City,Site_State,Site_Zip,Product_Qty,Product_Name")

'for all documents in group 0
For x = 1 to MyMeta.Job.Group(0).Count
'add new CSV line
MyCSVLine = MyMeta.Job.Group(0).Document(x-1).FieldByName("%{Distribution_Center}") +","+ _
MyMeta.Job.Group(0).Document(x-1).FieldByName("%{Route}") +","+ _
MyMeta.Job.Group(0).Document(x-1).FieldByName("%{Delivery_}") +","+ _
MyMeta.Job.Group(0).Document(x-1).FieldByName("%{PO_}") +","+ _
MyMeta.Job.Group(0).Document(x-1).FieldByName("%{Type}") +","+ _
MyMeta.Job.Group(0).Document(x-1).FieldByName("%{Delivery_Date}") +","+ _
MyMeta.Job.Group(0).Document(x-1).FieldByName("%{Account}") +","+ _
MyMeta.Job.Group(0).Document(x-1).FieldByName("%{Customer_Name}") +","+ _
MyMeta.Job.Group(0).Document(x-1).FieldByName("%{Site_Address_1}") +","+ _
MyMeta.Job.Group(0).Document(x-1).FieldByName("%{Site_City}") +","+ _
MyMeta.Job.Group(0).Document(x-1).FieldByName("%{Site_State}") +","+ _
MyMeta.Job.Group(0).Document(x-1).FieldByName("%{Site_Zip}") +","+ _
MyMeta.Job.Group(0).Document(x-1).FieldByName("%{Product_Qty}") +","+ _
MyMeta.Job.Group(0).Document(x-1).FieldByName("%{Product_Name}") +","+ _
MyMeta.Job.Group(0).Document(x-1).AttributeByName("SelectedIndexInJob") + ";" + _
MyMeta.Job.Group(0).Document(x-1).AttributeByName("SelectedIndexInGroup")
ft.WriteLine(MyCSVLine)
Next

ft.Close
Set FSO = Nothing
Set MyMeta = Nothing

Top
#58215 - 02/16/22 08:27 AM Re: PlanetPress Database SQL to CSV thru VBscript [Re: Tom@ECI]
Jean-Cédric Offline
OL Expert

Registered: 10/03/16
Posts: 681
Loc: Québec, Canada
Why don't you output the data from the database directly into CSV, which is one of the output option of the database plugin?
_________________________
♪♫♪♫
99 frigging bugs in my code
99 frigging bugs
Take one down
Code around
127 frigging bugs in my code
♪♫♪♫

Top
#58216 - 02/16/22 09:32 AM Re: PlanetPress Database SQL to CSV thru VBscript [Re: Tom@ECI]
Tom@ECI Offline
OL Newbie

Registered: 04/20/10
Posts: 24
Loc: Fort Worth, Tx
Thanks, Jean-Cédric. I did not realize that was an option.

Top