/* COPYDATA.PRG Author: Ken Mayer Modified: Ruth Bromer 6/4/2019 A simple program to copy the data to the Competitor table from Ed Despard's registration data. USAGE: Copy this to the directory that the tables were created in (i.e., C:Documents/BOK/ARDF dBase 2019 ) for the tutorial. Double-click the icon in the navigator. That's all you should have to do, unless an error occurs ... Before running this program download data into wordpad from Ed's program. Delete the first row (heading row). Select a tab. Go into EDIT/REPLACE and put "," into the REPLACE field , and REPLACE ALL Save file as a .csv *********************************************** DEPENDENCIES: BFILE.CC, DBF7FILE.CC these files must be in the same directory as this program and the tables!! *********************************************** */ close database clear // -------------------------------------------------------- // append the data : ? "Copying competitor data" d = _app.databases[1] d.emptyTable( "competitor" ) use competitor exclusive append from competitor.csv delimited reindex /* Convert phone number to 123-456-7890 format */ q = new query() q.sql = "select phone,country from competitor" q.active = true q.rowset.first() do while not q.rowset.endofset cOldPhoneNo = q.rowset.fields["phone"].value.leftTrim().rightTrim() cNewPhoneNo = "" for n = 1 to len(cOldPhoneNo) if substr(cOldPhoneNo,n,1) >= "0" and substr(cOldPhoneNo,n,1) <= "9" cNewPhoneNo += substr(cOldPhoneNo,n,1) endif next nLen = len(cNewPhoneNo) cNewPhoneNo =stuff(cNewPhoneNo,nLen-3,0,"-") cNewPhoneNo =stuff(cNewPhoneNo,nLen-6,0,"-") if nLen > 10 cNewPhoneNo =stuff(cNewPhoneNo,nLen-9,0,"-") endif if q.rowset.fields["country"].value.toUpperCase() <> "UNITED STATES OF AMERICA" and; q.rowset.fields["country"].value.toUpperCase() <> "CANADA" cNewPhoneNo = "+" + cNewPhoneNo endif q.rowset.fields["phone"].value = cNewphoneNo q.rowset.next() enddo /* Create sort order for Classs */ q = new query() q.sql = "select M2Class, M2ClassNum, M80Class, M80ClassNum, FoxClass, FoxClassNum, SprintClass, SprintClassNum from Competitor" q.active = true qf = q.rowset.fields q.rowset.first() do while not q.rowset.endofset if qf["M2Class"].value = "M-21" qf["M2ClassNum"].value = " 1" elseif qf["M2Class"].value = "W-21" qf["M2ClassNum"].value = " 2" elseif qf["M2Class"].value = "M-19" or qf["M2Class"].value = "M-40" qf["M2ClassNum"].value = " 3" elseif qf["M2Class"].value = "W-19" or qf["M2Class"].value = "W-35" or qf["M2Class"].value = "M-50" qf["M2ClassNum"].value = " 4" elseif qf["M2Class"].value = "W-50" or qf["M2Class"].value = "M-60" qf["M2ClassNum"].value = " 5" elseif qf["M2Class"].value = "W-60" or qf["M2Class"].value = "M-70" qf["M2ClassNum"].value = " 6" elseif qf["M2Class"].value = "M-12" or qf["M2Class"].value = "W-12" qf["M2ClassNum"].value = " 7" elseif qf["M2Class"].value = "M-14" or qf["M2Class"].value = "W-14" qf["M2ClassNum"].value = " 8" elseif qf["M2Class"].value = "M-16" or qf["M2Class"].value = "W-16" qf["M2ClassNum"].value = " 9" endif if qf["M80Class"].value = "M-21" qf["M80ClassNum"].value = " 1" elseif qf["M80Class"].value = "W-21" qf["M80ClassNum"].value = " 2" elseif qf["M80Class"].value = "M-19" or qf["M80Class"].value = "M-40" qf["M80ClassNum"].value = " 3" elseif qf["M80Class"].value = "W-19" or qf["M80Class"].value = "W-35" or qf["M80Class"].value = "M-50" qf["M80ClassNum"].value = " 4" elseif qf["M80Class"].value = "W-50" or qf["M80Class"].value = "M-60" qf["M80ClassNum"].value = " 5" elseif qf["M80Class"].value = "W-60" or qf["M80Class"].value = "M-70" qf["M80ClassNum"].value = " 6" elseif qf["M80Class"].value = "M-12" or qf["M80Class"].value = "M-12" qf["M80ClassNum"].value = " 7" elseif qf["M80Class"].value = "M-14" or qf["M80Class"].value = "M-14" qf["M80ClassNum"].value = " 8" elseif qf["M80Class"].value = "M-16" or qf["M80Class"].value = "M-16" qf["M80ClassNum"].value = " 9" endif if qf["FoxClass"].value = "M-21" qf["FoxClassNum"].value = " 1" elseif qf["FoxClass"].value = "M-19" or qf["FoxClass"].value = "M-40" qf["FoxClassNum"].value = " 2" elseif qf["FoxClass"].value = "W-19" or qf["FoxClass"].value = "W-35" or qf["FoxClass"].value = "M-60" qf["FoxClassNum"].value = " 3" elseif qf["FoxClass"].value = "W-21" or qf["FoxClass"].value = "M-50" qf["FoxClassNum"].value = " 4" elseif qf["FoxClass"].value = "W-50" or qf["FoxClass"].value = "M-70" qf["FoxClassNum"].value = " 5" elseif qf["FoxClass"].value = "W-60" qf["FoxClassNum"].value = " 6" elseif qf["FoxClass"].value = "M-12" or qf["FoxClass"].value = "M-12" qf["FoxClassNum"].value = " 7" elseif qf["FoxClass"].value = "M-14" or qf["FoxClass"].value = "M-14" qf["FoxClassNum"].value = " 8" elseif qf["FoxClass"].value = "M-16" or qf["FoxClass"].value = "M-16" qf["FoxClassNum"].value = " 9" endif if qf["SprintClass"].value = "M-21" qf["SprintClassNum"].value = " 1" elseif qf["SprintClass"].value = "M-19" or qf["SprintClass"].value = "M-40" or qf["SprintClass"].value = "W-21" qf["SprintClassNum"].value = " 2" elseif qf["SprintClass"].value = "W-19" or qf["SprintClass"].value = "W-35" or qf["SprintClass"].value = "M-50" qf["SprintClassNum"].value = " 3" elseif qf["SprintClass"].value = "W-50" or qf["SprintClass"].value = "M-60" qf["SprintClassNum"].value = " 4" elseif qf["SprintClass"].value = "W-60" or qf["SprintClass"].value = "M-70" qf["SprintClassNum"].value = " 5" elseif qf["SprintClass"].value = "M-12" or qf["SprintClass"].value = "M-12" qf["SprintClassNum"].value = " 6" elseif qf["SprintClass"].value = "M-14" or qf["SprintClass"].value = "M-14" qf["SprintClassNum"].value = " 7" elseif qf["SprintClass"].value = "M-16" or qf["SprintClass"].value = "M-16" qf["SprintClassNum"].value = " 8" endif q.rowset.next() enddo /* Compute total amount paid for t-shirts. */ /* q = new query() q.sql = "select TShirtBlueWS, TShirtSlateWS, TShirtFuschiaWS,; TShirtYellowWS, TShirtRedWS, TShirtGreenWS,; TShirtBlueWM, TShirtSlateWM, TShirtFuschiaWM,; TShirtYellowWM, TShirtRedWM, TShirtGreenWM,; TShirtBlueWL, TShirtSlateWL, TShirtFuschiaWL,; TShirtYellowWL, TShirtRedWL, TShirtGreenWL,; TShirtBlueWXL, TShirtSlateWXL, TShirtFuschiaWXL,; TShirtYellowWXL, TShirtRedWXL, TShirtGreenWXL,; TShirtBlueXXS, TShirtSlateXXS, TShirtFuschiaXXS,; TShirtYellowXXS, TShirtRedXXS, TShirtGreenXXS,; TShirtBlueXS, TShirtSlateXS, TShirtFuschiaXS,; TShirtYellowXS, TShirtRedXS, TShirtGreenXS,; TShirtBlueSM, TShirtSlateSM, TShirtFuschiaSM,; TShirtYellowSM, TShirtRedSM, TShirtGreenSM,; TShirtBlueMD, TShirtSlateMD, TShirtFuschiaMD,; TShirtYellowMD, TShirtRedMD, TShirtGreenMD,; TShirtBlueLG, TShirtSlateLG, TShirtFuschiaLG,; TShirtYellowLG, TShirtRedLG, TShirtGreenLG,; TShirtBlueXL, TShirtSlateXL, TShirtFuschiaXL,; TShirtYellowXL, TShirtRedXL, TShirtGreenXL,; TShirtBlueXXL, TShirtSlateXXL, TShirtFuschiaXXL,; TShirtYellowXXL, TShirtRedXXL, TShirtGreenXXL,; TShirtBlue3XL, TShirtSlate3XL, TShirtFuschia3XL,; TShirtYellow3XL, TShirtRed3XL, TShirtGreen3XL,; TShirtBlueYS, TShirtSlateYS, TShirtFuschiaYS,; TShirtYellowYS, TShirtRedYS, TShirtGreenYS,; TShirtBlueYM, TShirtSlateYM, TShirtFuschiaYM,; TShirtYellowYM, TShirtRedYM, TShirtGreenYM,; TShirtBlueYL, TShirtSlateYL, TShirtFuschiaYL,; TShirtYellowYL, TShirtRedYL, TShirtGreenYL from competitor" msgbox( "sql t-shirts created","Done", 64 ) q.active = true qf = q.rowset.fields q.rowset.first() do while not q.rowset.endofset qf["ShirtFee"].value = qf["TShirtBlueWSFee"].value + qf["TShirtSlateWSFee"].value +; qf["TShirtFuschiaWSFee"].value + qf["TShirtYellowWSFee"].value + uf["TShirtRedWSFee"].value +; uf["TShirtGreenWSFee"].value + qf["TShirtBlueWMFee"].value + qf["TShirtSlateWMFee"].value +; qf["TShirtFuschiaWMFee"].value + qf["TShirtYellowWMFee"].value + uf["TShirtRedWMFee"].value +; uf["TShirtGreenWMFee"].value + qf["TShirtBlueWLFee"].value + qf["TShirtSlateWLFee"].value +; qf["TShirtFuschiaWLFee"].value + qf["TShirtYellowWLFee"].value + uf["TShirtRedWLFee"].value +; uf["TShirtGreenWLFee"].value + qf["TShirtBlueWXLFee"].value + qf["TShirtSlateWXLFee"].value +; qf["TShirtFuschiaWXLFee"].value + qf["TShirtYellowWXLFee"].value + uf["TShirtRedWXLFee"].value +; uf["TShirtGreenWXLFee"].value + qf["TShirtBlueXXSLFee"].value + qf["TShirtSlateWXXSFee"].value +; qf["TShirtFuschiaXXSFee"].value + qf["TShirtYellowXXSFee"].value + uf["TShirtRedXXSFee"].value +; uf["TShirtGreenXXSFee"].value + qf["TShirtBlueXSFee"].value + qf["TShirtSlateXSFee"].value +; qf["TShirtFuschiaXSFee"].value + qf["TShirtYellowXSFee"].value + uf["TShirtRedXSFee"].value +; uf["TShirtGreenXSFee"].value + qf["TShirtBlueSMFee"].value + qf["TShirtSlateSMFee"].value +; qf["TShirtFuschiaSMFee"].value + qf["TShirtYellowSMFee"].value + uf["TShirtRedSMFee"].value +; uf["TShirtGreenSMFee"].value + qf["TShirtBlueMDFee"].value + qf["TShirtSlateMDFee"].value +; qf["TShirtFuschiaMDFee"].value + qf["TShirtYellowMDFee"].value + uf["TShirtRedMDFee"].value +; uf["TShirtGreenMDFee"].value + qf["TShirtBlueLGFee"].value + qf["TShirtSlateLGFee"].value +; qf["TShirtFuschiaLGFee"].value + qf["TShirtYellowLGFee"].value + uf["TShirtRedLGFee"].value +; uf["TShirtGreenLGFee"].value + qf["TShirtBlueXLFee"].value + qf["TShirtSlateXLFee"].value +; qf["TShirtFuschiaXLFee"].value + qf["TShirtYellowXLFee"].value + uf["TShirtRedXLFee"].value +; uf["TShirtGreenXLFee"].value + qf["TShirtBlueXXLFee"].value + qf["TShirtSlateXXLFee"].value +; qf["TShirtFuschiaXXLFee"].value + qf["TShirtYellowXXLFee"].value + uf["TShirtRedXXLFee"].value +; uf["TShirtGreenXXLFee"].value + qf["TShirtBlue3XLFee"].value + qf["TShirtSlate3XLFee"].value +; qf["TShirtFuschia3XLFee"].value + qf["TShirtYellow3XLFee"].value + uf["TShirtRed3XLFee"].value +; uf["TShirtGreen3XLFee"].value + qf["TShirtBlueYSFee"].value + qf["TShirtSlateYSFee"].value +; qf["TShirtFuschiaYSFee"].value + qf["TShirtYellowYSFee"].value + uf["TShirtRedYSFee"].value +; uf["TShirtGreenYSFee"].value + qf["TShirtBlueYMFee"].value + qf["TShirtSlateYMFee"].value +; qf["TShirtFuschiaYMFee"].value + qf["TShirtYellowYMFee"].value + uf["TShirtRedYMFee"].value +; uf["TShirtGreenYMFee"].value + qf["TShirtBlueYLFee"].value + qf["TShirtSlateYLFee"].value +; qf["TShirtFuschiaYLFee"].value + qf["TShirtYellowYLFee"].value + uf["TShirtRedYLFee"].value +; uf["TShirtGreenYLFee"].value msgbox( "Shirt Fee created","Done", 64 ) qf["ShirtTotal"].value = qf["TShirtBlueWS"].value + qf["TShirtSlateWS"].value +; qf["TShirtFuschiaWS"].value + qf["TShirtYellowWS"].value + uf["TShirtRedWS"].value +; uf["TShirtGreenWS"].value + qf["TShirtBlueWM"].value + qf["TShirtSlateWM"].value +; qf["TShirtFuschiaWM"].value + qf["TShirtYellowWM"].value + uf["TShirtRedWM"].value +; uf["TShirtGreenWM"].value + qf["TShirtBlueWL"].value + qf["TShirtSlateWL"].value +; qf["TShirtFuschiaWL"].value + qf["TShirtYellowWL"].value + uf["TShirtRedWL"].value +; uf["TShirtGreenWL"].value + qf["TShirtBlueWXL"].value + qf["TShirtSlateWXL"].value +; qf["TShirtFuschiaWXL"].value + qf["TShirtYellowWXL"].value + uf["TShirtRedWXL"].value +; uf["TShirtGreenWXL"].value + qf["TShirtBlueXXSL"].value + qf["TShirtSlateWXXS"].value +; qf["TShirtFuschiaXXS"].value + qf["TShirtYellowXXS"].value + uf["TShirtRedXXS"].value +; uf["TShirtGreenXXS"].value + qf["TShirtBlueXS"].value + qf["TShirtSlateXS"].value +; qf["TShirtFuschiaXS"].value + qf["TShirtYellowXS"].value + uf["TShirtRedXS"].value +; uf["TShirtGreenXS"].value + qf["TShirtBlueSM"].value + qf["TShirtSlateSM"].value +; qf["TShirtFuschiaSM"].value + qf["TShirtYellowSM"].value + uf["TShirtRedSM"].value +; uf["TShirtGreenSM"].value + qf["TShirtBlueMD"].value + qf["TShirtSlateMD"].value +; qf["TShirtFuschiaMD"].value + qf["TShirtYellowMD"].value + uf["TShirtRedMD"].value +; uf["TShirtGreenMD"].value + qf["TShirtBlueLG"].value + qf["TShirtSlateLG"].value +; qf["TShirtFuschiaLG"].value + qf["TShirtYellowLG"].value + uf["TShirtRedLG"].value +; uf["TShirtGreenLG"].value + qf["TShirtBlueXL"].value + qf["TShirtSlateXL"].value +; qf["TShirtFuschiaXL"].value + qf["TShirtYellowXL"].value + uf["TShirtRedXL"].value +; uf["TShirtGreenXL"].value + qf["TShirtBlueXXL"].value + qf["TShirtSlateXXL"].value +; qf["TShirtFuschiaXXL"].value + qf["TShirtYellowXXL"].value + uf["TShirtRedXXL"].value +; uf["TShirtGreenXXL"].value + qf["TShirtBlue3XL"].value + qf["TShirtSlate3XL"].value +; qf["TShirtFuschia3XL"].value + qf["TShirtYellow3XL"].value + uf["TShirtRed3XL"].value +; uf["TShirtGreen3XL"].value + qf["TShirtBlueYS"].value + qf["TShirtSlateYS"].value +; qf["TShirtFuschiaYS"].value + qf["TShirtYellowYS"].value + uf["TShirtRedYS"].value +; uf["TShirtGreenYS"].value + qf["TShirtBlueYM"].value + qf["TShirtSlateYM"].value +; qf["TShirtFuschiaYM"].value + qf["TShirtYellowYM"].value + uf["TShirtRedYM"].value +; uf["TShirtGreenYM"].value + qf["TShirtBlueYL"].value + qf["TShirtSlateYL"].value +; qf["TShirtFuschiaYL"].value + qf["TShirtYellowYL"].value + uf["TShirtRedYL"].value +; uf["TShirtGreenYL"].value msgbox( "Shirt Total created","Done", 64 ) q.rowset.next() enddo use */ msgbox( "The Competitor table should now have data in it","Done", 64 ) /* End of Program; CopyData.prg */