Hi Arie,
In a memorytable with all the files from the analyse I can select the files to export. The following code treats each selected file:
liFilesTotal is int liFilesCopied is int liMaxRecs is int = 1000 liTelRecs is int lsTempDir is string lsDirectory is string lsItems is string lsItemNames is string lsItemInfo is string lsKey is string lsSql is string lsSqlQry is string lsSub is string lbAllFiles is boolean=True lbCopyOk is boolean=True lbGevuld is boolean lbEersteRec is boolean IF YesNo(No, "Wilt u de geselecteerde bestanden kopieren naar een lokatie op de pc ?") THEN lsTempDir = fTempPath() lsDirectory = fSelectDir(lsTempDir, "Selecteer de doelmap ...") IF lsDirectory > "" THEN HourGlass(True) HClose("*") // Bepaal het aantal geselecteerde bestanden FOR EACH ROW liIndex OF TABLE_Files IF COL_Select THEN liFilesTotal ++ END FOR EACH ROW liIndex OF TABLE_Files IF COL_Select THEN liFilesCopied++ Gauge(liFilesCopied,liFilesTotal) lbGevuld = False //; lbEersteRec = true lsKey = "" lsItems = HListKey(COL_NAAM[liIndex],psAnalyse,psAnalysePassword,hLstDetail) FOR i = 1 _TO_ StringCount(lsItems,CRLF) + 1 IF ExtractString(ExtractString(lsItems,i,CRLF),5,TAB) = "1" THEN lsKey = ExtractString(ExtractString(lsItems,i,CRLF),1,TAB) BREAK END END IF lsKey > "" THEN liTelRecs = 0 IF HReadFirst(COL_NAAM[liIndex],lsKey) THEN WHILE NOT HOut(COL_NAAM[liIndex]) IF liTelRecs = 0 THEN lsSql = "SET IDENTITY_INSERT " + COL_NAAM[liIndex] + " ON;" + CRLF lsSql += "INSERT INTO %1 (%2) VALUES" lsItems = HListItem(COL_NAAM[liIndex],psAnalyse,psAnalysePassword,hLstNormal) lsItemNames = Replace(lsItems,CRLF,",") lsSql = StringBuild(lsSql,COL_NAAM[liIndex],lsItemNames) lsItems = HListItem(COL_NAAM[liIndex],psAnalyse,psAnalysePassword,hLstDetailPlus) lbEersteRec = True END liTelRecs++ // Exporteer hier het record naar een SQL-opdracht lsItemNames = "" FOR i = 1 _TO_ StringCount(lsItems,CRLF) + 1 lsItemInfo = ExtractString(lsItems,i,CRLF) SWITCH ExtractString(lsItemInfo,3,TAB) CASE "2","26","30","23","29" // "text" items lsSub = {COL_NAAM[liIndex] + "." + ExtractString(lsItemInfo,1,TAB),indItem} lsItemNames += [","] + "'" + Replace(lsSub,"'","`") + "'" CASE "15" // "text" memo IF Left({COL_NAAM[liIndex] + "." + ExtractString(lsItemInfo,1,TAB),indItem}) [= "{\ tf1" THEN lsSub = RTFToText({COL_NAAM[liIndex] + "." + ExtractString(lsItemInfo,1,TAB),indItem}) ELSE lsSub = {COL_NAAM[liIndex] + "." + ExtractString(lsItemInfo,1,TAB),indItem} END lsSub = Replace(lsSub,"'","`") lsItemNames += [","] + "'" + Replace(lsSub,[CRLF,CR],"' + CHAR(13)+CHAR(10) + '") + "'" CASE "1","3","4","5","6","7","8","9","12","13","17","19","20","22","27","28" ,"31" // int + reals + boolean (incl. currency) lsItemNames += [","] + {COL_NAAM[liIndex] + "." + ExtractString(lsItemInfo,1,TAB),indItem} CASE "14" // Date IF DateValid({COL_NAAM[liIndex] + "." + ExtractString(lsItemInfo,1,TAB),indItem}) THEN lsSub = {COL_NAAM[liIndex] + "." + ExtractString(lsItemInfo,1,TAB),indItem} lsItemNames += [","] + "'" + lsSub[[1 TO 4]] + "/" + lsSub[[5 TO 6]] + "/" + lsSub[[7 TO 8]] + "'" ELSE lsItemNames += [","] + "NULL" END CASE "11" // Time IF Left({COL_NAAM[liIndex] + "." + ExtractString(lsItemInfo,1,TAB),indItem}) > "" _AND_ ... TimeValid({COL_NAAM[liIndex] + "." + ExtractString(lsItemInfo,1,TAB),indItem}) THEN lsSub = {COL_NAAM[liIndex] + "." + ExtractString(lsItemInfo,1,TAB),indItem} lsItemNames += [","] + "'" + lsSub[[1 TO 2]] + ":" + lsSub[[3 TO 4]] IF Length(lsSub) >= 6 THEN lsItemNames += ":" + lsSub[[5 TO 6]] IF Length(lsSub) >= 8 THEN lsItemNames += ":" + lsSub[[7 TO]] lsItemNames += "'" ELSE lsItemNames += [","] + "NULL" END CASE "24" // Date + time IF DateTimeValid({COL_NAAM[liIndex] + "." + ExtractString(lsItemInfo,1,TAB),indItem}) THEN lsSub = {COL_NAAM[liIndex] + "." + ExtractString(lsItemInfo,1,TAB),indItem} lsItemNames += [","] + "'" + lsSub[[1 TO 4]] + "/" + lsSub[[5 TO 6]] + "/" + lsSub[[7 TO 8]] + " " lsItemNames += lsSub[[9 TO 10]] + ":" + lsSub[[11 TO 12]] + ":" + lsSub[[13 TO 14]] + ":" + lsSub[[15 TO 17]] lsItemNames += "'" ELSE lsItemNames += [","] + "NULL" END CASE "25" // Duration lsItemNames += [","] + "NULL" CASE "16","18","21" // Binary memo,image,etc. lsItemNames += [","] + "NULL" OTHER CASE END END IF lbEersteRec THEN lbEersteRec = False lsSql += [CRLF] + "(" + lsItemNames + ")" ELSE lsSql += "," + [CRLF] + "(" + lsItemNames + ")" END lbGevuld = True IF liTelRecs >= liMaxRecs THEN lsSqlQry += [CRLF] + lsSql + ";" + CRLF lsSql = "" liTelRecs = 0 END HReadNext(COL_NAAM[liIndex],lsKey) END END END IF lbGevuld THEN IF lsSql > "" THEN lsSqlQry += [CRLF] + lsSql + CRLF lsSql = "" liTelRecs = 0 END IF NOT fSaveText(lsDirectory + ["\"] + COL_NAAM[liIndex] + ".sql",lsSqlQry) THEN Error(ErrorInfo()) END END lsSqlQry = "" END END HourGlass(False) // Wis de progressbar Gauge() IF lbCopyOk THEN Info("Alle geselecteerde bestanden zijn correct geëxporteerd !") ELSE Error("Er zijn fouten opgetreden tijdens het exporteren,","mogelijk zijn niet alle geselecteerde bestanden geëxporteerd !") END END END |