Save a Report to Excel

[candyperry]

Re: Save a Report to Excel

Thanks! It's all working perfectly now!

[MoGrace]

Re: Save a Report to Excel

Hi Candy,
In the beginning of the script, it seems you are trying to set the proc_num variable before you have fetched the first record in the table:

out_fil = file.create("S:\Web Order Download\NewOrders.csv",FILE_RW_EXCLUSIVE)

tbl = table.open("tpi-ready.dbf")
tbl.order("Num")
[COLOR=#FF0000]proc_num = tbl.num[/COLOR]
tbl.fetch_first()
while .not. tbl.fetch_eof()
    while .not. tbl.fetch_eof() .and. tbl.num = proc_num
...

add a msgbox() and see if that variable is blank and if it is then try moving that line after the tbl.fetch_first() and see if that makes the difference or not.

Also in this section:

   tbl.fetch_next()
		  if tbl.num <> proc_num
		     if ths_tax_amt<>0
		      out_fil.write_line(ths_num+","+ths_type+","+ths_date+","+ths_class+","+ths_shipdate+","+ths_billphone+","+ths_billemail+","+ths_billfirst+","+ths_billlast+","+ths_billaddr1+","+ths_billaddr2+","+ths_billcity+","+ths_billstate+","+ths_billzip+","+ths_shipfirst+","+ths_shiplast+","+ths_shipaddr1+","+ths_shipaddr2+","+ths_shipcity+","+ths_shipstate+","+ths_shipzip+","+ths_shipphone+","+ths_Tax_Sku+","+ths_Tax_Descr+","+"1,"+ths_tax_amt+","+ths_cc+","+ths_salesrep)
	        end if

You seem to be testing for when the proc_num does not match the tbl.num then you want to add data for when the ths_tax_amt is NOT 0. If so then, you have not added the 9 variables that would define that. I don't see those 9 variables included in any of the strings at all which may be why the csv ends at the ths_salesrep.

ths_shippingline = tbl.shippingitemnew
ths_taxcode = tbl.sales_tax_code
ths_tax_amt = tbl.tax_amt
ths_DiscSku = tbl.discount_sku
ths_DiscDesc = tbl.discount_descrip
ths_ShipSku = tbl.shipping_sku
ths_ShipDesc = tbl.shipping_descr
ths_Tax_Sku = tbl.tax_sku
ths_Tax_descr = tbl.tax_descr

[candyperry]

Re: Save a Report to Excel

This worked for a few years! Now I am having another issue which I'm hoping someone can help with!

The last line of each record is the shipping. I need to have a sales tax line that is written to the "NewOrders.csv" just like the shipping line is written. When I run the code as attached, I get the results as in the sample file attached. Everything works fine except the very last records. The shipping line does not get written to NewOrders.csv at all.

Any suggestions?

[Stan Mathews]

Re: Save a Report to Excel

I just had to add all of my multitude of other fields.

[Applause] I'm proud of you, seriously.

[candyperry]

Re: Save a Report to Excel

[QUOTE=Stan Mathews;616769]Try this on your table. Rename the table (in red) to your actual table name. Examine the output file created in the c:\temp directory. (Create that directory first if you don't have one.)

Wow! Thanks so much, it seems to work, I just had to add all of my multitude of other fields. So far so good!

[candyperry]

Re: Save a Report to Excel

Great! Thanks, I'll see what I can do with this and report back!

[Stan Mathews]

Re: Save a Report to Excel

Try this on your table. Rename the table (in red) to your actual table name. Examine the output file created in the c:\temp directory. (Create that directory first if you don't have one.)

out_fil = file.create("C:\temp\candy_orders.csv",FILE_RW_EXCLUSIVE)

tbl = table.open("[COLOR="#FF0000"][SIZE=5]candy[/SIZE][/COLOR]")
tbl.order("Num")
proc_num = tbl.num
tbl.fetch_first()
while .not. tbl.fetch_eof()
	while .not. tbl.fetch_eof() .and. tbl.num = proc_num
	ths_num = tbl.NUM
	ths_name = alltrim(tbl.ship_name_first)+" "+ alltrim(tbl.ship_name_last)
	ths_addr = alltrim(tbl.ship_name_address_1)
	ths_item = alltrim(tbl.item_sku)
	ths_itmprc = alltrim(str(tbl.item_price,10,2))
	ths_disccd = alltrim(tbl.discount_code)
	ths_shpamt = alltrim(str(tbl.ship_amt,10,2))
	out_fil.write_line(ths_num+","+ths_name+","+ths_addr+","+ths_item+","+ths_itmprc)
	out_fil.write_line(ths_num+","+ths_name+","+ths_addr+","+ths_disccd)
	tbl.fetch_next()
	if tbl.num <> proc_num
		out_fil.write_line(ths_num+","+ths_name+","+ths_addr+","+ths_shpamt)
	end if
	end while
        proc_num = tbl.NUM
end while
out_fil.write_line(ths_num+","+ths_name+","+ths_addr+","+ths_shpamt)
out_fil.flush()
out_fil.close()
tbl.close()

[candyperry]

Re: Save a Report to Excel

Thanks, Al. I have the file.

Candy,

I'll need a bit more information about your desired csv output format.

[Row 1:] Num, Name, Address, Item, Item Price... [more but not needed for this example]
[Row 2:] Num, Name, Address, Discount Code, [more]
[Row 3:] Num, Name Address, Item[2] Item Price...
[Row 4:] Num, Name Address, Discount Code...
[Last Row for each "Num"]: Num, Name, Address, Shipping Amount

Looking at your table (as imported) the first num is SFH21985 which has one item. You want the csv file to have three lines, one for the item, one for the discount code, one for the shipping amount. I need to know what you want on lines 1 and 2 where you have [more]. I'm assuming the [Row 1:] portion of each line is not to be included, it is only there for clarity.

Some customers purchase more than one item. If there are two items purchased with a discount code, I would need
Row 1: Item 1 with all other columns (num, name, etc)
Row 2: The item column will now have the discount code instead of an item description and then the row will have all other columns (num, name, etc) the same
Row 3: Item 2 with all columns
Row 4: Discount code in the item column
Row 5: Item column will now be shipping information instead of item description plus all the other columns the same

The last line always has to be the shipping amount. Hope it makes sense. I have to have it in this format because that's the way QB calculates correctly.

[Stan Mathews]

Re: Save a Report to Excel

Thanks, Al. I have the file.

Candy,

I'll need a bit more information about your desired csv output format.

[Row 1:] Num, Name, Address, Item, Item Price... [more but not needed for this example]
[Row 2:] Num, Name, Address, Discount Code, [more]
[Row 3:] Num, Name Address, Item[2] Item Price...
[Row 4:] Num, Name Address, Discount Code...
[Last Row for each "Num"]: Num, Name, Address, Shipping Amount

Looking at your table (as imported) the first num is SFH21985 which has one item. You want the csv file to have three lines, one for the item, one for the discount code, one for the shipping amount. I need to know what you want on lines 1 and 2 where you have [more]. I'm assuming the [Row 1:] portion of each line is not to be included, it is only there for clarity.

[candyperry]

Re: Save a Report to Excel

Here's the table in txt format. Thanks for any help!

Candy


____
From: Al Buchholz
Too much personal information in the file.

I removed the attachment and will send directly to Stan...

[ATTACH=CONFIG]32719[/ATTACH]

Oops I was in a hurry to get up and didn't think about that!

[candyperry]

Re: Save a Report to Excel

Here's the table in txt format. Thanks for any help!

Candy


____
From: Al Buchholz
Too much personal information in the file.

I removed the attachment and will send directly to Stan...

blank.PNG

[Stan Mathews]

Re: Save a Report to Excel

A sample table would be helpful if you want assistance writing that xbasic.

[Al Buchholz]

Re: Save a Report to Excel

You should write some xbasic code to build the csv the way you need it.

[candyperry]

Re: Save a Report to Excel

I'm not sure the driver is what I need. What I'm trying to do is import sales data from our website directly into QB so we don't have to manually enter every order. We're able to export certain data from our website, then I'm using Alpha 5 to manipulate the data into a format that will place all of the information into each person's sales receipt in QB. We have another program, Transaction Pro Importer that takes a csv file and puts all the information into its proper spot in QB. I have manually made a csv file to test and find that I need the formatting as above. Doing the report in Alpha gives me the data in the order I need it and at one point it exported into excel in the proper way, but once I add too many fields to the report, I can't get it into Excel.

Any other ideas?

Thanks!

[Al Buchholz]

Re: Save a Report to Excel

Writing into QuickBooks can be accomplish directly with a read/write QODBC driver.

http://www.qodbc.com