* * *

Author Topic: Trouble Getting my CSV file to properly read with my SQL DB  (Read 391 times)

Jade

  • Newbie
  • Posts: 4
Trouble Getting my CSV file to properly read with my SQL DB
« on: September 06, 2017, 06:22:17 pm »
So I have gotten my CSV to read into lazarus. I have selected the fields i want to show that all works fine. Now I am working on comparing my CSV file to my SQL DB. To determine what files need to be deleted, which ones are ok, and which ones need to be written up to the DB from the CSV file. So my code works with the delete and the ok. For some reason my write code keeps telling me all 18 lines need to be written onto the SQL DB. I know thats not true cause I hand put in 2 of the information off the CSV file so i could test everything. 
This is the code i have right now

  For CSVCntr := 0 To Length(CSVList) - 1 Do
  Begin
    SQLCntr := 0;
    While (SQLCntr < Length(SQLList))
       and Not (MyWrite (CSVList[CSVCntr],SQLList[SQLCntr]))
        do SQLCntr := SQLCntr + 1;
     If SQLCntr <= Length(SQLList) Then
    Begin
      CSVList[CSVCntr].RecStatus:= rsWrite;
      WriteCnt := WriteCnt + 1;

    end;

  end; 

This is what i have MyWrite = too

Function MyWrite (InCSVRec, InSQLRec : TCustRec) : Boolean;
Begin
   MyWrite := (InSQLRec.LicID = InCSVRec.LicID)
          and (InSQLRec.CustID <> InCSVRec.CustID)
          and (InSQLRec.Password <> InCSVRec.Password);     

Almir.Bispo

  • Jr. Member
  • **
  • Posts: 57
  • CSV Comp DB is the Best NoSQL
    • CSV Comp DB (NoSQL)
Re: Trouble Getting my CSV file to properly read with my SQL DB
« Reply #1 on: September 06, 2017, 09:40:22 pm »
I did it for you:
Code: Pascal  [Select]
  1. //i'll use tstringlist class as exemple
  2. procedure TForm1.Button1Click(Sender: TObject);
  3. var csvlist,sqllist,csvpersist,sqlpersist:Tstringlist;
  4.    i:integer;
  5. begin
  6.   // persistence on ram
  7.   csvpersist:=Tstringlist.create;
  8.   sqlpersist:=Tstringlist.create;
  9.   //editable
  10.   csvlist:=Tstringlist.create;
  11.   sqllist:=Tstringlist.create;
  12.   //set items of csv
  13.   csvlist.add('A');
  14.   csvlist.add('B');
  15.   csvlist.add('C');
  16.  
  17.   //set sql list
  18.   sqllist.add('1');//it is not in csv
  19.   sqllist.add('B');//it is on csv
  20.   sqllist.add('D');//it is not on csv
  21.   sqllist.add('M');//it is not on csv
  22.   sqllist.add('V');//it is not on csv
  23.  
  24.   sqlpersist.text:=sqllist.text;
  25. //  verify csv list
  26.   for i:= 0 to csvlist.count-1 do
  27.   begin
  28.    //if not exists on sqllist ,then add it
  29.    if sqllist.IndexOf(csvlist.strings[i])=-1 then
  30.    begin
  31.      //sqlpersist.Add(csvlist.strings[i]+' inserted');
  32.      sqllist.add(csvlist.strings[i]+' inserted on sql list');
  33.    end;
  34.    //if exists,then,delete from sqllist
  35.    if sqllist.IndexOf(csvlist.strings[i])<>-1 then
  36.    begin
  37.      //only for show
  38.     sqllist.strings[sqlpersist.IndexOf(csvlist.strings[i])]:='['+csvlist.strings[i]+' ok]';
  39.    end;
  40.  
  41.   end;
  42.    //verify csv list to sql compare
  43.    for i:= 0 to sqlpersist.count-1 do
  44.    begin
  45.     //if it does exists on csv list,then deletes from sql
  46.     if csvlist.IndexOf(sqlpersist.strings[i])=-1 then
  47.     begin
  48.      //to real delete
  49. //     sqllist.Delete(i);
  50.      //only to show
  51.      sqllist.Strings[i]:=sqllist.Strings[i] +' Was deleted from sql list';
  52.     end;
  53.    end;
  54. memo1.text:=sqllist.text;
  55.   csvpersist.free;
  56.   csvlist.free;
  57.   sqllist.free;
  58.   sqlpersist.free;
  59.  
  60. end;                                  
  61.  
http://adltecnologia.blogspot.com.br
CSV Comp DB Developer {Pascal Lover}

Jade

  • Newbie
  • Posts: 4
Re: Trouble Getting my CSV file to properly read with my SQL DB
« Reply #2 on: September 07, 2017, 03:24:13 pm »
Var
  i, j     : Integer;
  CSVCntr  : Integer;
  SQLCntr  : Integer;

  TempStr    : String;

begin
  OKCnt   := 0;
  DelCnt  := 0;
  WriteCnt := 0;
  memo1.clear;
  SQLList := Nil;
  CSVList := Nil;

//  SDFDataSet1.FirstLineAsSchema:= True;
//  SDFDataSet1.Active := True;
  SDFDataSet1.First;

  While Not SDFDataSet1.EOF do
  Begin
    i := Length(CSVList);
    SetLength (CSVList,i+1);
    CustRecClear (CSVList);
    CSVList.CustID := SDFDataSet1['CustomerID'];
    CSVList.LicID := SDFDataSet1['LicenseID'];
    CSVList.Password := 'RLK';
    SDFDataSet1.Next;
  end;

  For i := 0 To Length(CSVList) - 1 Do
  Begin
    TempStr  := CSVList.LicID + '  '
              + CSVList.CustID + '  '
              + CSVList.Password;
    Memo1.Lines.Add(TempStr);
  end;

  SQLQuery1.First;

  While Not SQLQuery1.EOF do
  Begin
    i := Length(SQLList);
    SetLength (SQLList, i+1);
    CustRecClear (SQLList);
    SQLList.CustID := SQLQuery1['AU_CustomerID'];
    SQLList.LicID := SQLQuery1['AU_LicenseNum'];
    SQLList.Password:= SQLQuery1['AU_Password'];
    SQLQuery1.Next;
  end;

  For i := 0 To Length(SQLList) - 1 Do
  Begin
    TempStr :=SQLList.LicID + '  '
             + SQLList.CustID + '  '
             + SQLList.Password;
    Memo1.Lines.Add(TempStr);
  end;

// Now detect SQL Items that must be deleted

   For SQLCntr := 0 To Length(SQLList) - 1 Do
   Begin
     CSVCntr := 0;
     While (CSVCntr < Length(CSVList))
         and (SQLList[SQLCntr].LicID <> CSVList[CSVCntr].LicID)
         Do CSVCntr := CSVCntr + 1;
     If CSVCntr >= Length(CSVList) Then
     Begin
       SQLList[SQLCntr].RecStatus:= rsDelete;
       DelCnt := delCnt + 1;

       //SQLQuery1.SQL.Clear;
       //TempStr := 'Select * From ActiveUsers Where AU_LicenseNum = '
       //  + SQLList[SQLCntr].LicID;
       //SQLQuery1.SQL.Add(TempStr );
       //SQLQuery1.ExecSQL;
       //SQLQuery1.First;
       //While Not SQLQuery1.EOF do
       //Begin
       //  i := SQLQuery1.RecordCount;
       //  SQLQuery1.Delete;
       //End;

    end;

  end;

// Now detect SQL Items that are OK as is.

  For CSVCntr := 0 To Length(CSVList) - 1 Do
  Begin
    SQLCntr := 0;
    While (SQLCntr < Length(SQLList))
       and not (MyOk (CSVList[CSVCntr],SQLList[SQLCntr]))
        do SQLCntr := SQLCntr + 1;
     If SQLCntr < Length(SQLList) Then
    Begin
      CSVList[CSVCntr].RecStatus:= rsOK;
      OkCnt := okCnt + 1;

    end;

  end;

  // Now detect CSV items that need to be written to the DB   


This is what i have set for ok and the delete function when i try to add yours it throws me errors     

 

Recent

Get Lazarus at SourceForge.net. Fast, secure and Free Open Source software downloads Open Hub project report for Lazarus