[WD12] Creating a set of CSV files from corresponding HF files...

Hi guys,

Some people may find this useful...? I posted it as a result of this post; [forum.mysnip.de]

In my app, I have a requirement to extract Products (and related info, such as Categories and Catergory usage), into 3 separate CSV files then FTP them upto a web server in order to update the web site. This example shows only the CSV creation part of the requirement.

You will also see various bits of code referencing window elements - this is because this code sits behind a push button on a window and also updates 3 progress bars (one for each CSV file), on the same window.

It may be more complex than you need, but hopefully you can glean the information you need - Enjoy! :spos:

// Get counts of the 3 tables I'm sending data from to use with progress bars
HExecuteQuery(QRY_CountActiveCategories)
glocaliCatCount is int = HNbRec(QRY_CountActiveCategories)
HExecuteQuery(QRY_CountActiveWebProducts)
glocaliProdCount is int = HNbRec(QRY_CountActiveWebProducts)
HExecuteQuery(QRY_CountProducts2Categories)
glocaliPrd2CatCount is int = HNbRec(QRY_CountProducts2Categories)

// Set-up progress bars
WPBAR_Products..MinValue = 0
WPBAR_Products..MaxValue = glocaliProdCount
WPBAR_PageNodes..MinValue = 0
WPBAR_PageNodes..MaxValue = glocaliCatCount
WPBAR_PageProducts..MinValue = 0
WPBAR_PageProducts..MaxValue = glocaliPrd2CatCount
//Set up actual counts
glocaliPBarProdCount,glocaliPBarCatCount,glocaliPBarP2CCount is int = 0

// Set-up variables
glocaliTempCount is int
glocalsSep is string = WEDIT_TypeOfSeparator
glocalsQuotes is string = WEDIT_TypeOfQuotes
glocalsSampleData is string = ""
nFileID1,nFileID2,nFileID3 is int
sMyString is string

// Decide if quotes are required around the data
IF WCB_IncludeQuotes = False THEN
	glocalsQuotes = ""
END

// Create the blank files in the "ftp" folder
nFileID1 = fOpen(WEDIT_CSVFielname1, foCreate+foReadWrite)
nFileID2 = fOpen(WEDIT_CSVFielname2, foCreate+foReadWrite)
nFileID3 = fOpen(WEDIT_CSVFielname3, foCreate+foReadWrite)

// Did any of the files fail to open correctly?
IF nFileID1 = -1 OR nFileID2 = -1 OR nFileID3 = -1 THEN

	// Error trying to open the Product CSV file in read/write mode
	IF nFileID1 = -1 THEN
		Error("Error: Unable to create the Product CSV file!", ErrorInfo())
		RETURN
	END

	// Error trying to open the PageNodes CSV file in read/write mode
	IF nFileID2 = -1 THEN
		Error("Error: Unable to create the PageNodes CSV file!", ErrorInfo())
		RETURN
	END

	// Error trying to open the PageProducts CSV file in read/write mode
	IF nFileID3 = -1 THEN
		Error("Error: Unable to create the PageProducts CSV file!", ErrorInfo())
		RETURN
	END

ELSE

	// Reset counter
	glocaliTempCount = 0
	
	// Tax Rate control var
	glocaliTempSTDID is int
	glocaliTempZEROID is int
	glocalsTempTaxCode is string
			
	// Read the STD Tax Rate to get ID
	HReadSeek(Tax_Rates,cCode,"STD")
	IF HFound() THEN
		// Set depending on what
		glocaliTempSTDID = Tax_Rates.ID
	ELSE
		// Error getting tax info...
		Error("Error: Unable to find the STD VAT tax record!" + CR + "There should be a VAT tax record with a code of STD, and I can't find it on the database." + CR + "Please check via the VAT Maintenance option and try this option again.", ErrorInfo())
		RETURN
	END
	
	// Read the ZERO Tax Rate to get ID
	HReadSeek(Tax_Rates,cCode,"ZERO")
	IF HFound() THEN
		// Set depending on what
		glocaliTempZEROID = Tax_Rates.ID
	ELSE
		// Error getting tax info...
		Error("Error: Unable to find the ZERO VAT tax record!" + CR + "There should be a VAT tax record with a code of ZERO, and I can't find it on the database." + CR + "Please check via the VAT Maintenance option and try this option again.", ErrorInfo())
		RETURN
	END

	// Loop through each product
	FOR EACH Product_Variants
		
		// Only consider products that are flagged as Web products and are also flagged as Active
		IF Product_Variants.bWeb = True AND Product_Variants.bActive = True THEN

			// Fill the string to write in the file
			
			// prod_id = ID
			sMyString = glocalsQuotes + Product_Variants.ID + glocalsQuotes + glocalsSep
			//sMyString = glocalsQuotes + Product_Variants.cCode_External + glocalsQuotes + glocalsSep
			
			// product_name = cTitle
			sMyString = sMyString + Charact(34) + Product_Variants.cTitle + Charact(34) + glocalsSep
			
			// unitprice = cyRetail
			sMyString = sMyString + glocalsQuotes + Product_Variants.cyRetail + glocalsQuotes + glocalsSep
			
			// rrp_proce = cySRP
			sMyString = sMyString + glocalsQuotes + Product_Variants.cySRP + glocalsQuotes + glocalsSep
			
			// weight = nWeight
			sMyString = sMyString + glocalsQuotes + Product_Variants.nWeight + glocalsQuotes + glocalsSep
			
			// taxcode = See code...
			IF Product_Variants.FK_Tax = glocaliTempSTDID THEN
				
				glocalsTempTaxCode = "0"
				
			ELSE IF Product_Variants.FK_Tax = glocaliTempZEROID THEN
				
				glocalsTempTaxCode = "2"
				
			END
						
			sMyString = sMyString + glocalsQuotes + glocalsTempTaxCode + glocalsQuotes + glocalsSep
			
			// displayoff = "1"
			sMyString = sMyString + glocalsQuotes + "1" + glocalsQuotes + glocalsSep
			
			// options = ???
			sMyString = sMyString + glocalsQuotes + "" + glocalsQuotes + glocalsSep
			
			// description = cDescription
			sMyString = sMyString + Charact(34) + Product_Variants.cDescription + Charact(34) + glocalsSep

			// long_description = <various> see code...
			IF Product_Variants.cWebDesc <> "" THEN
				
				// First use the Web description if it's not empty
				sMyString = sMyString + Charact(34) + Product_Variants.cWebDesc + Charact(34)
				
			ELSE IF Product_Variants.cB2BDesc <> "" THEN
				
				// If Web description is blank, then use B2B description
				sMyString = sMyString + Charact(34) + Product_Variants.cB2BDesc + Charact(34)
				
			ELSE
				
				// If both the above are empty then put something in there
				sMyString = sMyString + Charact(34) + Product_Variants.cDescription + Charact(34)				
				
			END
			

			// Write to the Products CSV file
			fWriteLine(nFileID1, sMyString)	
			
			// Create Preview string for output later
			IF glocaliTempCount < 100 THEN
				// Add this row to the preview
				IF glocaliTempCount = 1 THEN 
					glocalsSampleData = glocalsSampleData + sMyString
				ELSE
					glocalsSampleData = glocalsSampleData + CR + sMyString	
				END	
			END
			
			// Now write to the the PageProducts CSV file
			sMyString = glocalsQuotes + Product_Variants.FK_Category + glocalsQuotes + glocalsSep
			sMyString = sMyString + glocalsQuotes + Product_Variants.ID + glocalsQuotes + glocalsSep
			sMyString = sMyString + glocalsQuotes + "0" + glocalsQuotes +glocalsSep
			// Just for me - not required for Get Trolleyed system
			sMyString = sMyString + glocalsQuotes + Product_Variants.cTitle + glocalsQuotes
			
			// Write to the PageProducts CSV file based on the current product
			fWriteLine(nFileID3, sMyString)	
			
			// Keep track of the number of records in the file
			glocaliTempCount ++
			glocaliPBarP2CCount ++
			glocaliPBarProdCount ++
			WPBAR_Products = glocaliPBarProdCount
			WPBAR_PageProducts = glocaliPBarP2CCount
			
		END
	END
END

// Write all the PageNodes to a CSV file
FOR EACH Categories 
	
	// Now write to the the PageProducts CSV file
	sMyString = glocalsQuotes + Categories.ID + glocalsQuotes + glocalsSep
	sMyString = sMyString + glocalsQuotes + Categories.FK_Parent + glocalsQuotes + glocalsSep
	sMyString = sMyString + glocalsQuotes + Categories.cDescription + glocalsQuotes + glocalsSep
	sMyString = sMyString + glocalsQuotes + "" + glocalsQuotes + glocalsSep
	sMyString = sMyString + glocalsQuotes + Categories.cTreeBranch + glocalsQuotes + glocalsSep
	sMyString = sMyString + glocalsQuotes + "" + glocalsQuotes + glocalsSep
	sMyString = sMyString + glocalsQuotes + "" + glocalsQuotes + glocalsSep
	sMyString = sMyString + glocalsQuotes + "" + glocalsQuotes + glocalsSep
	sMyString = sMyString + glocalsQuotes + "0" + glocalsQuotes
	
	// Write into the PageProducts CSV file based on the current product
	fWriteLine(nFileID2, sMyString)	
	
	glocaliPBarCatCount ++
	WPBAR_PageNodes = glocaliPBarCatCount
	
END

// Now close the CSV files
fClose(nFileID1)
fClose(nFileID3)
fClose(nFileID2)

// Set the on screen preview field with the preview data...
WEDIT_Preview = glocalsSampleData

IF glocaliTempCount > 0 THEN

	// Show the user a message
	NextTitle("File Created")
	Info("Product Data Feed files were successfully created!" + CR + "The required Data Feed files have been created containing " + glocaliTempCount + " products." + CR + "Don't forget to press the Send Data Feed Files button to actually send the files to your FTP server.")

ELSE
	
	// Show the user a message
	NextTitle("File Create Error!")
	Info("Sorry, I couldn't find any products to put into the Product Data Feed file!" + CR + "Remember: Only products which have the 'Web' check box set on the Product Maintenance screen will be selected for processing.")
	
END

:spos: