Use Dfclient.pkg Use Import_textfile.dg Use cCJGrid.pkg Use cCJGridColumn.pkg Use Windows.pkg Use cIndexComboForm.pkg Use tsTextFileStructure.pkg Use cCJCommandBarSystem.pkg Use cSplitterContainer.pkg Object Copy_from_file is a dbModalPanel Set Label to "Import from textfile" Set Location to 11 18 Set Size to 311 323 Property String psCurrentFilename "" Property tsTextFileStructure plsTextFileStructure Property Boolean pbExamineFile Set piMinSize to 250 323 Object oImportFile is a Form Set Label to "Filename from:" Set Size to 13 194 Set Location to 10 65 Set Prompt_Button_Mode to pb_PromptOn Set peAnchors to anTopLeftRight Procedure Prompt String sInitDir sFile Move (vSelect_File("Textfiles (*.txt;*.csv;*.asc)|*.txt;*.csv;*.asc|All files (*.*)|*.*", "Choose file", "")) to sFile If (Trim(sFile) <> "") Begin Set value to sFile Set pbExamineFile to True End End_Procedure Procedure Activating tsTextFileStructure lsFileStructure Forward Send Activating Get plsTextFileStructure to lsFileStructure Set Value to lsFileStructure.sFilename End_Procedure End_Object Object oExamine is a Button Set Location to 10 267 Set Label to "Examine" Set peAnchors to anTopRight Procedure OnClick Set pbExamineFile to True End_Procedure End_Object Object oBox is a Container3d Set Size to 241 319 Set Location to 29 2 Set peAnchors to anAll Object oSplitterContainer1 is a cSplitterContainer Set pbSplitVertical to False Set piSplitterLocation to 125 Set peFixedPanel to fpFixLastPanel Set piMaxSplitterLocation to 80 Set piMinSplitterLocation to 80 Object oSplitterContainerChild1 is a cSplitterContainerChild Object oTextBox1 is a Textbox Set Location to 4 2 Set Size to 10 310 Set Auto_Size_State to False Set Label to "Joined fields" Set Justification_Mode to jMode_Center Set peAnchors to anTopLeftRight End_Object Object oChainedFields is a cCJGrid Set Size to 106 310 Set Location to 16 2 Set pbDrawGridForEmptySpace to False Set pbAllowAppendRow to False Set pbAllowColumnReorder to False Set pbAllowColumnRemove to False Set pbAllowInsertRow to False Set pbAllowEdit to False Set pbAllowDeleteRow to False Set pbAutoAppend to False Set pbFocusSubItems to False Set pbSelectionEnable to True Set pbHeaderReorders to True Set peDrawSortTriangleStyle to xtpReportDrawSortTriangleNever Set peColumnStyle to xtpColumnShaded Set peAnchors to anAll Object oFieldTo is a cCJGridColumn Set piWidth to 270 Set psCaption to "To table field" End_Object Object oFieldFrom is a cCJGridColumn Set piWidth to 272 Set psCaption to "From text column" End_Object Object oChainedFieldsMenu is a cCJContextMenu Object oDeleteMenuItem is a cCJMenuItem Set psCaption to "Remove join" Set psTooltip to "Remove join" Set psShortcut to "Shift+F2" Procedure OnExecute Variant vCommandBarControl Send RemoveJoin End_Procedure End_Object End_Object Set phoContextMenu to oChainedFieldsMenu Set phoHeaderContextMenu to 0 // No column selector. Procedure JoinFields String sFrom String sTo Boolean bAddRowMode Get pbAllowAppendRow to bAddRowMode Set pbAllowAppendRow to True Send Request_AppendRow Send UpdateCurrentValue of oFieldFrom sFrom Send UpdateCurrentValue of oFieldTo sTo Send Request_Save Set pbAllowAppendRow to bAddRowMode End_Procedure Procedure RemoveJoin Boolean bAllowDeleteMode String sFrom sTo Integer iRow Handle hoDatasource Get phoDataSource to hoDatasource Get SelectedRow of hoDatasource to iRow If (iRow = -1) Procedure_Return Get pbAllowDeleteRow to bAllowDeleteMode Set pbAllowDeleteRow to True Get SelectedRowValue of oFieldFrom to sFrom Get SelectedRowValue of oFieldTo to sTo Send Request_Delete Set pbAllowDeleteRow to bAllowDeleteMode Send AddFieldName of oFieldsTo sTo Send AddTextColumn of oFieldsFrom sFrom End_Procedure Function JoinedFields String[] ByRef sFieldsFrom String[] ByRef sFieldsTo Returns Integer tDataSourceRow[] lsData Handle hoDataSource Integer iJoints iCurJoin iColFrom iColTo Get phoDataSource to hoDataSource Get DataSource of hoDataSource to lsData Move (SizeOfArray(lsData)) to iJoints Get piColumnId of oFieldFrom to iColFrom Get piColumnId of oFieldTo to iColTo For iCurJoin from 0 to (iJoints - 1) Move lsData[iCurJoin].sValue[iColFrom] to sFieldsFrom[iCurJoin] Move lsData[iCurJoin].sValue[iColTo] to sFieldsTo[iCurJoin] Loop Function_Return iJoints End_Function On_Key kDelete_Record Send RemoveJoin End_Object End_Object Object oSplitterContainerChild2 is a cSplitterContainerChild // ToDo: If it is possible add another splitter container here? Object oContainerFrameToSecondSplitter is a Container3d Set Location to 0 0 Set Size to 82 314 Set peAnchors to anAll Set Border_Style to Border_None Object oSplitterContainer2 is a cSplitterContainer Set piSplitterLocation to 186 Set piMinSplitterLocation to 120 Set piMaxSplitterLocation to 80 Object oSplitterContainerChild3 is a cSplitterContainerChild Object oFieldsTo is a cCJGrid Set Size to 77 128 Set Location to 2 2 Set pbDrawGridForEmptySpace to False Set pbAllowAppendRow to False Set pbAllowColumnReorder to False Set pbAllowColumnRemove to False Set pbAllowInsertRow to False Set pbAllowEdit to False Set pbAllowDeleteRow to False Set pbAutoAppend to False Set pbFocusSubItems to False Set pbSelectionEnable to True Set peDrawSortTriangleStyle to xtpReportDrawSortTriangleNever Set peColumnStyle to xtpColumnShaded Set phoHeaderContextMenu to 0 Set peAnchors to anAll Object oFieldNames is a cCJGridColumn Set piWidth to 224 Set psCaption to "Table fields" End_Object Procedure DoLoadFieldNames Integer iNumFields iField iFile iFieldNamesCol iRow tDataSourceRow[] lsData Get piFilenumber to iFile Get piColumnId of oFieldNames to iFieldNamesCol Move 0 to iRow Get_Attribute DF_FILE_NUMBER_FIELDS of iFile to iNumFields For iField from 1 to iNumFields Get_Attribute DF_FIELD_NAME of iFile iField to lsData[iRow].sValue[iFieldNamesCol] Increment iRow Loop Send InitializeData lsData Send MovetoFirstRow End_Procedure Procedure Activating Forward Send Activating Send DoLoadFieldNames End_Procedure Procedure RemoveSelectedFieldName Boolean bAllowDeleteMode Get pbAllowDeleteRow to bAllowDeleteMode Set pbAllowDeleteRow to True Send Request_Delete Set pbAllowDeleteRow to bAllowDeleteMode End_Procedure Procedure AddFieldName String sFieldname Boolean bAppendMode Get pbAllowAppendRow to bAppendMode Set pbAllowAppendRow to True Send Request_AppendRow Send UpdateCurrentValue of oFieldNames sFieldname Send Request_Save Set pbAllowAppendRow to bAppendMode End_Procedure End_Object Object oJoinFields is a Button Set Label to "Join selected fields" Set Size to 77 50 Set Location to 2 132 Set MultiLineState to True Set peAnchors to anTopBottomRight Procedure OnClick Send JoinSelectedFields End_Procedure End_Object End_Object Object oSplitterContainerChild4 is a cSplitterContainerChild Object oFieldsFrom is a cCJGrid Set Size to 77 121 Set Location to 2 2 Set pbDrawGridForEmptySpace to False Set pbAllowAppendRow to False Set pbAllowColumnReorder to False Set pbAllowColumnRemove to False Set pbAllowInsertRow to False Set pbAllowEdit to False Set pbAllowDeleteRow to False Set pbAutoAppend to False Set pbFocusSubItems to False Set pbSelectionEnable to True Set peDrawSortTriangleStyle to xtpReportDrawSortTriangleNever Set peColumnStyle to xtpColumnShaded Set phoHeaderContextMenu to 0 Set peAnchors to anAll Object oTextColumnNames is a cCJGridColumn Set piWidth to 218 Set psCaption to "Text columns" End_Object Procedure DoLoadTextColumnNames Integer iNoFields iCnt iRow iTextColumnNameCol tDataSourceRow[] lsData tsTextFileStructure lsTextFileStructure Get piColumnId of oTextColumnNames to iTextColumnNameCol Get plsTextFileStructure to lsTextFileStructure Move (SizeOfArray(lsTextFileStructure.lsField)) to iNoFields Move 0 to iRow For iCnt from 0 to (iNoFields - 1) If (not(lsTextFileStructure.lsField[iCnt].bSkip)) Begin Move lsTextFileStructure.lsField[iCnt].sName to lsData[iRow].sValue[iTextColumnNameCol] Increment iRow End Loop Send InitializeData lsData Send MovetoFirstRow End_Procedure Procedure RemoveSelectedTextColumn Boolean bAllowDeleteMode Get pbAllowDeleteRow to bAllowDeleteMode Set pbAllowDeleteRow to True Send Request_Delete Set pbAllowDeleteRow to bAllowDeleteMode End_Procedure Procedure AddTextColumn String sTextColumn Boolean bAppendMode Get pbAllowAppendRow to bAppendMode Set pbAllowAppendRow to True Send Request_AppendRow Send UpdateCurrentValue of oTextColumnNames sTextColumn Send Request_Save Set pbAllowAppendRow to bAppendMode End_Procedure End_Object End_Object End_Object End_Object Object oJoinAll is a Button Set Label to "Join all fields with the same name" Set Size to 26 239 Set Location to 83 2 Set MultiLineState to True Set peAnchors to anBottomLeftRight Procedure OnClick Send JoinAllWithSameName End_Procedure End_Object Object oJoinMissing is a Button Set Label to "Make missing fields and join them" Set Size to 26 70 Set Location to 83 243 Set MultiLineState to True Set peAnchors to anBottomRight Procedure OnClick Integer iFrom iMaxFrom iMaxJoin iFilenumber Integer iJoinFieldToCol iJoinFieldFromCol iNewField Integer iCurrentTextField iAnswer String sTo sFrom sCurrentFile Handle hDataSourceFrom hDataSourceJoin hServer hFile hTextFile tDataSourceRow[] lsDataFrom lsDataJoin lsEmptyData Number nTextFieldLength nRight tsTextFileStructure lsTextFileStructure Get psCurrentFilename to sCurrentFile Get YesNo_Box (SFormat("This will alter the table stucture of the current database table %1. Proceed with the structure change?", sCurrentFile)) "Database table structure change" to iAnswer If (iAnswer <> MBR_Yes) Procedure_Return Send JoinAllWithSameName Get phoDataSource of oFieldsFrom to hDataSourceFrom Get DataSource of hDataSourceFrom to lsDataFrom Move (SizeOfArray(lsDataFrom)) to iMaxFrom If (iMaxFrom = 0) Procedure_Return Get phoDataSource of oChainedFields to hDataSourceJoin Get DataSource of hDataSourceJoin to lsDataJoin Move (SizeOfArray(lsDataJoin)) to iMaxJoin Get piColumnId of oFieldTo to iJoinFieldToCol Get piColumnId of oFieldFrom to iJoinFieldFromCol Get plsTextFileStructure to lsTextFileStructure Get Create (RefClass(cTextFile)) to hTextFile Set plsTextFileStructure of hTextFile to lsTextFileStructure Send DoCloseAllFiles Get Server to hServer Get main_file of hServer to iFilenumber Open sCurrentFile as iFilenumber Mode DF_EXCLUSIVE If (Found) Begin Send DoStartResInfo of oResInfo Move iFilenumber to hFile Structure_Start hFile For iFrom from 0 to (iMaxFrom - 1) Move lsDataFrom[iFrom].sValue[0] to sFrom Move 0 to iNewField Create_Field hFile at iNewField Get TextColumnNumber of hTextFile sFrom to iCurrentTextField If (lsTextFileStructure.lsField[iCurrentTextField].iType = C_TEXTFIELDTYPE_ASCII) Begin Set_Attribute DF_FIELD_TYPE of hFile iNewField to DF_ASCII Set_Attribute DF_FIELD_LENGTH of hFile iNewField to (Integer(nTextFieldLength)) End Else If (lsTextFileStructure.lsField[iCurrentTextField].iType = C_TEXTFIELDTYPE_NUMERIC) Begin Set_Attribute DF_FIELD_TYPE of hFile iNewField to DF_BCD Move lsTextFileStructure.lsField[iCurrentTextField].nLength to nTextFieldLength Move ((nTextFieldLength - Integer(nTextFieldLength)) * 10.0) to nRight Set_Attribute DF_FIELD_LENGTH of hFile iNewField to (Integer(nTextFieldLength)) Set_Attribute DF_FIELD_PRECISION of hFile iNewField to nRight End Else If (lsTextFileStructure.lsField[iCurrentTextField].iType = C_TEXTFIELDTYPE_DATE) Begin Set_Attribute DF_FIELD_TYPE of hFile iNewField to DF_DATE End Set_Attribute DF_FIELD_NAME of hFile iNewField to sFrom Get_Attribute DF_FIELD_NAME of hFile iNewField to sTo // has been changed to legal fieldname Move sFrom to lsDataJoin[iMaxJoin].sValue[iJoinFieldFromCol] Move sTo to lsDataJoin[iMaxJoin].sValue[iJoinFieldToCol] Increment iMaxJoin Loop Structure_End hFile DF_STRUCTEND_OPT_NONE "" (oResInfo) Close iFilenumber Open sCurrentFile as iFilenumber Send InitializeData of oChainedFields lsDataJoin Send InitializeData of oFieldsFrom lsEmptyData Send DoStopResInfo of oResInfo End Send Destroy of hTextFile End_Procedure End_Object Procedure JoinSelectedFields String sTextColumn sFieldname Get SelectedRowValue of oTextColumnNames to sTextColumn If (sTextColumn = "") Procedure_Return Get SelectedRowValue of oFieldNames to sFieldname If (sTextColumn = "") Procedure_Return Send JoinFields of oChainedFields sTextColumn sFieldname Send RemoveSelectedTextColumn of oTextColumnNames Send RemoveSelectedFieldName of oFieldNames End_Procedure Procedure JoinAllWithSameName Integer iFrom iTo iMaxFrom iMaxTo iMaxJoin Integer iJoinFieldToCol iJoinFieldFromCol String sTo sFrom Handle hDataSourceFrom hDataSourceTo hDataSourceJoin tDataSourceRow[] lsDataFrom lsDataTo lsDataJoin Get phoDataSource of oFieldsFrom to hDataSourceFrom Get phoDataSource of oFieldsTo to hDataSourceTo Get phoDataSource of oChainedFields to hDataSourceJoin Get DataSource of hDataSourceFrom to lsDataFrom Get DataSource of hDataSourceTo to lsDataTo Get DataSource of hDataSourceJoin to lsDataJoin Move (SizeOfArray(lsDataFrom)) to iMaxFrom Move (SizeOfArray(lsDataTo)) to iMaxTo Move (SizeOfArray(lsDataJoin)) to iMaxJoin If (iMaxFrom = 0 or iMaxTo = 0) Procedure_Return Get piColumnId of oFieldTo to iJoinFieldToCol Get piColumnId of oFieldFrom to iJoinFieldFromCol Move (iMaxTo - 1) to iTo While (iTo > -1) Move (Uppercase(lsDataTo[iTo].sValue[0])) to sTo Move (iMaxFrom - 1) to iFrom While (iFrom > -1) Move (Uppercase(lsDataFrom[iFrom].sValue[0])) to sFrom If (sTo = sFrom) Begin Move lsDataFrom[iFrom].sValue[0] to lsDataJoin[iMaxJoin].sValue[iJoinFieldFromCol] Move lsDataTo[iTo].sValue[0] to lsDataJoin[iMaxJoin].sValue[iJoinFieldToCol] Increment iMaxJoin Move (RemoveFromArray(lsDataFrom, iFrom)) to lsDataFrom Decrement iMaxFrom Move (RemoveFromArray(lsDataTo, iTo)) to lsDataTo Decrement iMaxTo Move -1 to iFrom End Else Decrement iFrom Loop Decrement iTo Loop Send InitializeData of oChainedFields lsDataJoin Send InitializeData of oFieldsTo lsDataTo Send InitializeData of oFieldsFrom lsDataFrom End_Procedure End_Object End_Object End_Object Object oComboIndex is a cIndexComboForm Set Label to "Update by index:" Set Size to 14 259 Set Location to 276 62 Set Label_Justification_Mode to JMode_Right Set Label_Col_Offset to 2 Set peAnchors to anBottomLeftRight Procedure OnAddIndex Integer iIndex String ByRef sComboValue Forward Send OnAddIndex iIndex (&sComboValue) If (iIndex = 0) Begin Move "00: (Add records only)" to sComboValue End End_Procedure End_Object Object oOK_bn is a Button Set Label to "&Start" Set Location to 293 217 Set peAnchors to anBottomRight Procedure OnClick Send DoImportTheFile End_Procedure End_Object Object oAnnuller_bn is a Button Set Label to "&Cancel" Set Location to 293 271 Set peAnchors to anBottomRight Procedure OnClick Send Close_Panel End_Procedure End_Object On_Key Key_Alt+Key_S Send KeyAction to oOk_bn On_Key Key_Alt+Key_C Send KeyAction to oAnnuller_bn On_Key kCancel Send close_panel Procedure ExamineTextfile Boolean bOk String sFile tsTextFileStructure lsTextFileStructure Get Value of oImportFile to sFile Move (Trim(sFile)) to sFile If (sFile <> "") Begin Get MakeTextFileStructure of Import_textfile sFile (&lsTextFileStructure) to bOk If bOk Begin Set plsTextFileStructure to lsTextFileStructure Send ResetGrid of oChainedFields Send DoLoadFieldNames of oFieldsTo Send DoLoadTextColumnNames of oFieldsFrom End Else Begin Send ResetGrid of oChainedFields Send DoLoadFieldNames of oFieldsTo Send ResetGrid of oFieldsFrom End End End_Procedure Procedure DoCopyFromFile Handle hDDO String sCurrentFilename String sImportFile tsTextFileStructure lsTextFileStructure Set Server to hDDO Set psCurrentFilename to sCurrentFilename Get plsTextFileStructure to lsTextFileStructure Move sImportFile to lsTextFileStructure.sFilename Set plsTextFileStructure to lsTextFileStructure Set pbExamineFile to True Send Popup End_Procedure Procedure DoImportTheFile Handle hTextFile hDDO Boolean bOk Integer iFile iRecordsBefore iRowsRead iNewRecords iUpdatedRecords tsTextFileStructure lsTextFileStructure Get plsTextFileStructure to lsTextFileStructure Get Create (RefClass(cTextFile)) to hTextFile Set plsTextFileStructure of hTextFile to lsTextFileStructure Send Initialize_StatusPanel of ghoStatusPanel "AnyFlex" "Import from textfile" "Importing data..." Send Start_StatusPanel of ghoStatusPanel Get Server to hDDO Get main_file of hDDO to iFile Get_Attribute DF_FILE_RECORDS_USED of iFile to iRecordsBefore Get OpenTextFile of hTextFile to bOk If bOk Begin Send Ignore_Error of Error_Object_Id 28 Send ImportFile iFile hTextFile (&iRowsRead) (&iNewRecords) (&iUpdatedRecords) Send Trap_Error of Error_Object_Id 28 Get CloseTextFile of hTextFile to bOk End Send Destroy of hTextFile Send Stop_StatusPanel of ghoStatusPanel Send Info_Box (SFormat("Rows read from text file: %1\n\nNew records: %2\nUpdated records: %3\n\nRows not used: %4", iRowsRead, iNewRecords, iUpdatedRecords, (iRowsRead - iNewRecords - iUpdatedRecords))) "Import of textfile result" End_Procedure Procedure ImportFile Integer iFile Handle hTextFile Integer ByRef iRowsRead Integer ByRef iNewRecords Integer ByRef iUpdatedRecords tsTextFileStructure lsFileStructure String sFieldname sTestValue Integer iIndex iJoints iJoin iFields iField iFilePosition iActPct iOldPct iAnswer Integer[] iFieldsFrom iFieldsTo String[] sFieldsFrom sFieldsTo sRow BigInt biFileSize biCurrentPosition Boolean bEOF bOk bParseRow bSave bRecordExists bSaveError bStopImport Get plsTextFileStructure to lsFileStructure Get SelectedIndex of oComboIndex to iIndex Get JoinedFields of oChainedFields (&sFieldsFrom) (&sFieldsTo) to iJoints Get_Attribute DF_FILE_NUMBER_FIELDS of iFile to iFields For iField from 1 to iFields Get_Attribute DF_FIELD_NAME of iFile iField to sFieldname Move (SearchArray(sFieldname, sFieldsTo)) to iJoin If (ijoin > -1) Begin Move iField to iFieldsTo[iJoin] End Loop For iJoin from 0 to (iJoints - 1) Get TextColumnNumber of hTextFile sFieldsFrom[iJoin] to iFieldsFrom[iJoin] Loop Get TextFileSize of hTextFile to biFileSize Move 0 to iRowsRead Move -1 to iOldPct Repeat Get TextFilePosition of hTextFile to biCurrentPosition Get NextTextRow of hTextFile (&sRow) (&bEOF) to bOk If (not(bOk)) Begin Send UserError (SFormat("An error ocurred while reading from position %1.", biCurrentPosition)) "Import of textfile" Procedure_Return End If (SizeOfArray(sRow) <> SizeOfArray(lsFileStructure.lsField)) Begin Send UserError (SFormat("Error reading from position %1. Number of columns read are %2 and %3 was expected.", biCurrentPosition, SizeOfArray(sRow), SizeOfArray(lsFileStructure.lsField))) "Import of textfile" Procedure_Return End Increment iRowsRead Move ((biCurrentPosition * 1000.0) / biFileSize) to iActPct If (iActPct <> iOldPct) Begin Send Update_StatusPanel of ghoStatusPanel (SFormat("Imported %1%%. Fileposition %2.", NumToStr(iActPct / 10, 1), biCurrentPosition)) Move iActPct to iOldPct End If (not(iRowsRead = 1 and lsFileStructure.bFirstRowHasFieldNames = True)) Begin Lock Clear iFile For iJoin from 0 to (iJoints - 1) If (not(lsFileStructure.lsField[iJoin].bSkip)) Begin Set_Field_Value iFile iFieldsTo[iJoin] to sRow[iFieldsFrom[iJoin]] End Loop If (iIndex > 0) Begin // Try finding the record. If found it is updated otherwise it is created. Move False to bSave Vfind iFile iIndex EQ Move (Found) to bRecordExists If (not(bRecordExists)) Begin Clear iFile End For iJoin from 0 to (iJoints - 1) // Only update the record if needed. Get_Field_Value iFile iFieldsTo[iJoin] to sTestValue If (sTestValue <> sRow[iFieldsFrom[iJoin]]) Begin Set_Field_Value iFile iFieldsTo[iJoin] to sRow[iFieldsFrom[iJoin]] Move True to bSave End Loop End Else Begin Move True to bSave End If bSave Begin indicate err False SaveRecord iFile Move (Err) to bSaveError If (not(bSaveError)) Begin If bRecordExists Begin Increment iUpdatedRecords End Else Begin Increment iNewRecords End End End Unlock End Get Check_StatusPanel of ghoStatusPanel to bStopImport If bStopImport Begin Send Stop_StatusPanel of ghoStatusPanel Get YesNo_Box "Do you want to stop the import?" "Import from textfile" to iAnswer If (iAnswer <> MBR_Yes) Begin Send Start_StatusPanel of ghoStatusPanel Move False to bStopImport End End Until (bEOF = True or bStopImport) End_Procedure Object oIdleHandler is a cIdleHandler Procedure OnIdle Boolean bExamineFile Forward Send OnIdle Get pbExamineFile to bExamineFile If bExamineFile Begin Set pbExamineFile to False Send ExamineTextfile End End_Procedure End_Object Procedure Activating Forward Send Activating Set pbEnabled of oIdleHandler to True End_Procedure Procedure Deactivating Forward Send Deactivating Set pbEnabled of oIdleHandler to False End_Procedure End_Object