How to transfer data between Paradox and .EXE/.CSV files

[terrytype]

This thread is a contribution to this great website on these subjects.
TO TRANSFER FROM PARADOX TO EXCEL worksheet
1. One needs to create an "interface" Paradox table ["LedgTots" is what I use below] in which the details to be transfered have been acumulated.
2. That information is saved as "Interface" in the same folder where PROJECT [excel worksheet] resides. PROJCET being reliant upon "Interface" to spread the data as required. The Interface creation takes place as follows:-

procedure TfrmLedgers.btnSprdSheetClick(Sender: TObject);
var
AsAtDate : TDate; // Earlier alternative to Default stop date of licence
AsAtYear, AsAtMonth, AsAtDay : Word;
RangeE: Excel2000.Range;
I, Row: Integer;
Bookmark: TBookmarkStr;
begin
AsAtDate := dmStdAcc.PivotTbl1AsAtDate.value;
DecodeDate (AsAtDate, AsAtYear, AsAtMonth, AsAtDay);
if dmStdAcc.PivotTbl1KeyOrigin.value = 'GL'then
begin
if MessageDlg('Have you closed ALL spreadseets?, mtConfirmation, [mbYes, mbNo], 0) = mrNo then
begin
MessageDlg('Posting to spreadseets halted', mtInformation, [mbOk], 0);
Exit;
end;
ShowMessage ('Watch the progress bar whilst SpreadSheets are updated.');

if (AsAtMonth >= 3) AND (AsAtMonth <= 5)then
ShowMessage('WARNING! Save Book1 to Interface');
if (AsAtMonth >= 6) AND (AsAtMonth <= 8)then
ShowMessage('WARNING! Save Book1 to Interface2');
if (AsAtMonth >= 9) AND (AsAtMonth <= 11)then
ShowMessage('WARNING! Save Book1 to Interface3');
if (AsAtMonth = 12) then
ShowMessage('WARNING! Save Book1 to Interface4');
if (AsAtMonth >= 1) AND (AsAtMonth < 3)then
ShowMessage('WARNING! Save Book1 to Interface4');
if dmStdAcc.PivotTbl1.state in dsEditModes then
else
dmStdAcc.PivotTbl1.edit;
dmStdAcc.PivotTbl1SortType.value := 'N';
dmStdAcc.PivotTbl1.Post;
dmUpDates.DefineLedger;
dmUpDates.DefineLedgTots;
with dmStdAcc.qryLedgTots do
// Load G/L Totals into Spreadsheet interface table LedgTOts
if dmStdAcc.qryLedgTotsPNo.Value = 0 then
dmUpDates.CreateLedgTots;
dmUpDates.DefineLedgTots;
PopulateLedgTots;
// create and show
ExcelApplication1.Visible [0] := True;
ExcelApplication1.Workbooks.Add (NULL, 0);
// fill is the first row with field titles
RangeE := ExcelApplication1.ActiveCell;
dmUpDates.DefineLedgTots;
for I := 0 to dmStdAcc.qryLedgTots.Fields.Count - 1 do
begin
RangeE.Value := dmStdAcc.qryLedgTots.Fields .DisplayLabel;
RangeE := RangeE.Next;
end;
// add field data in following rows
dmStdAcc.qryLedgTots.DisableControls;
try
Bookmark := dmStdAcc.qryLedgTots.Bookmark;
try
dmStdAcc.qryLedgTots.First;
Row := 2;
while not dmStdAcc.qryLedgTots.EOF do
begin
RangeE := ExcelApplication1.Range ['A' + IntToStr (Row),
'A' + IntToStr (Row)];
for I := 0 to dmStdAcc.qryLedgTots.Fields.Count - 1 do
begin
RangeE.Value := dmStdAcc.qryLedgTots.Fields .AsString;
RangeE := RangeE.Next;
end;
dmStdAcc.qryLedgTots.Next;
Inc (Row);
end;
finally
dmStdAcc.qryLedgTots.Bookmark := Bookmark;
end;
finally
dmStdAcc.qryLedgTots.EnableControls;
// format the section
RangeE := ExcelApplication1.Range ['A1', 'E' + IntToStr (Row - 1)];
RangeE.AutoFormat (3, NULL, NULL, NULL, NULL, NULL, NULL);
end;
end;
end;

TO POPULATE A PARADOX TABLE FROM a .CSV FILE AS DOWNLOADED FROM A BANK.

procedure TForm1.btnPopulateClick(Sender: TObject);
var
rowData : TStringList;
s : string;
tf : TextFile;
lsTemp : string; // Temporary scratch pad variable
// transactionDate : string; // Column B of downloaded spreadshe+et
transactionDate : variant; // Column B of downloaded spreadsheet
transDate : variant; // date for CB line
transactionAmount : Currency; // Column D of downloaded spreadsheet
transactionDescription : String; // Column F of downloaded spreadsheet
transactionDocument : String; // Column C of downloaded spreadsheet
transactionAccount : Integer; // Column H of downloaded spreadsheet
// curYear, curMonth, curDay : word;
curDay : String;
intDay : variant;
liYear, liMonth, liDay : Integer;
i : Integer;
myFileName : String;

begin
DM.DefineLedgTots;
rowData := TStringList.Create;
try
// Assign the downloaded csv filename below
// NB : The downloaded standard bank file FIRST HAD THE TOP THREE LINES MANUALLY DELETED in Excel,
// as well as the last line, because these lines mean nothing to the import - then the CSV file was
// saved before running this import

// Assignfile(tf,'download.csv'); // FILENAME of downloaded and "panelbeated" CSV file - ONLY the actual transactions
// Assignfile(tf,'C:\Program Files\StdAccounting\download.csv');

myFileName := 'C:\h\StdAcc\' + Trim(DM.PivotTbl1PNo.AsString) + '\download.csv';
// myFileName := 'C:\h\StdAcc\' + Trim(IntToStr(UsrTfrPNo.AsInteger)) + '\download.csv';

Assignfile(tf, myFileName);
// Assignfile(tf,'C:\h\StdAcc\50\download.csv');

// must remain in the file before running this - you need to first remove the superfluous
// lines in the CSV file (top 3 lines and last line) and also the last 2 columns (G & H) via Excel
// and save the file again, BEFORE running this code to do the import...naming the file "download.csv".

// Open the downloaded csv file as a text file
Reset(tf);

// Process each line of the downloaded csv file as a record
while not Eof(tf) do
begin
// Read the next line in to a string
ReadLn(tf, s);

// Break up the line (each column is delimmited with a comma, being a CSV file)
rowData.CommaText := s;

// ProcessLine
// rowData now contains a separate item for the value of each column for the row of the
// spreadsheet we are currently dealing with
// In other words, rowData[0] will be column A of the spreadsheet
// rowData[1] will be column B of the spreadsheet
// and so on.

// Remember also, each column represented in rowData is a STRING - you will need convert the relevant
// columns to other data types as appropriate. For bank's download, this is done for the various
// columns below:

// Column A just seems to contain "PROV" indicating "Provisional", so we just ignore that column and forget about rowData[0]

// Column B contains the date of the transaction, but it's a string, so we need to pull that out and put it
// into the transactionDate variable by encoding a new date:
// -- TRANSACTION DATE ---------------------------------------------------
lsTemp := Trim(rowData[1]);
// All 6 characters for full date
// First 4 characters contain the year, so let's get that first
liYear := StrToInt(System.Copy(lsTemp, 1, 4));
// Next 2 characters are the month...
liMonth := StrToInt(System.Copy(lsTemp, 5,2));
// Next 2 characters are the day...
liDay := StrToInt(System.Copy(lsTemp, 7, 2));
curDay := (System.Copy(lsTemp, 7, 2));
// transactionDate := EncodeDate(liDay,liMonth,liYear);
// curDay := liDay;


// Column B contains the DATE of the transaction // NB 2 is the DATE column because column A is 0
// - rowData is "zero based" [ie first element starts at 0 and not 1]
// -- DATE -------------------------------------------------------------

transactionDate := EncodeDate(liYear,liMonth,liDay);
// showmessage(transactionDate); // You can unremark this if you want to see what it's importing and interpreting for the date

// -- DOCUMENT -------------------------------------------------------------

transactionDocument := Trim(rowData[2]);
// showmessage(transactionDocument); // You can unremark this if you want to see what it's importing and interpreting for the date

// Column D contains the amount of the transaction
// -- AMOUNT -------------------------------------------------------------
lsTemp := Trim(rowData[3]);
transactionAmount := StrToCurr(lsTemp);
// showmessage(CurrToStr(transactionAmount)); // You can unremark this if you want to see what it's importing and interpreting for the amount

// transactionAccount:= Trim(rowData[7]); // NB 7 is the Account column.

// Column E & F contains the descriptions
// -- DESCRIPTION --------------------------------------------------------

// The descriptions are a bit tricky so they don't go into the rowData very nicely
transactionDescription := ''; // First reset from last row...
for i := 4 to rowData.Count - 1 do
begin
lsTemp := Trim(rowData);
transactionDescription := transactionDescription + lsTemp + ' ';
// showmessage(transactionDescription); // You can unremark this if you want to see what it's importing and interpreting for the amount
end;

// lsTemp := Trim(rowData[5]);
// transactionDescription := lsTemp;

// transactionAccount := StrToInt(lsTemp);

// showmessage(transactionDescription); // You can unremark this if you want to see what it's importing and interpreting for the first description

// =======================================================================
// FROM THIS POINT, you can now utilise the variables:
//
// transactionDate : Transaction date
// transactionAmount : Value of transaction
// transactionDescription : Description
//
// At this point now, you can open and process/post to your paradox tables.
// It will hit this point for each line on the bank statement.....

{
Open Paradox table...
Post this transaction
Close Paradox table..
etc etc
}
with DM.qryLedgTots do
begin
if DM.qryLedgTots.state in dsEditModes then
else
DM.qryLedgTots.edit;
DM.qryLedgTots.APPEND;
DM.qryLedgTotsCBDate.Value := transactionDate;
DM.qryLedgTotsDY.Value := CurDay;
DM.qryLedgTotsDoc.Value := transactionDocument;
DM.qryLedgTotsLPayee.Value := transactionDescription; // Leger Account is determined from UponChangeLNo
DM.qryLedgTotsDays28.Value := transactionAmount;
DM.qryLedgTotsPNo.Value := DM.PivotTbl1PNo.value;
DM.qryLedgTots.Post;
end;
// DM.qryLedgTots.Next;
end;
if DM.qryLedgTots.UpdatesPending then
DM.qryLedgTots.ApplyUpdates;
CloseFile(tf);
finally
rowData.Free;
end;
end;

Hope this helps somebody.





Old Man Delphi