Recent

Author Topic: program import CSV & perform calculation  (Read 6306 times)

Polly

  • Newbie
  • Posts: 3
program import CSV & perform calculation
« on: June 29, 2014, 09:38:55 am »
Hi, I am new to programming and would like to turn my excel sheets with macros into their own executables. By re writing them in Lazarus.

To get  started I was hoping someone would be able to show me some example code of a user form with a menu bar that can import , edit and save a csv file into memory and perform calculations in the background. For example import 100 rows of data in colums 1& 2 then for every row add columns 1+2 and store the answer.

Many thanks in advance.


Lazarus  running on windows. 8 - 64 bit
« Last Edit: June 29, 2014, 09:42:03 am by Polly »

BigChimp

  • Hero Member
  • *****
  • Posts: 5740
  • Add to the wiki - it's free ;)
    • FPCUp, PaperTiger scanning and other open source projects
Re: program import CSV & perform calculation
« Reply #1 on: June 29, 2014, 09:46:42 am »
You don't need to use csv files - you can use fpspreadsheet which supports .xls files, as well as .xlsx and .ods files:
http://wiki.lazarus.freepascal.org/FPSpreadsheet

See the demo programs and documentation that come with it...

If you do want to use csv files, I recommend using csvdocument:
http://wiki.lazarus.freepascal.org/CsvDocument
Want quicker answers to your questions? Read http://wiki.lazarus.freepascal.org/Lazarus_Faq#What_is_the_correct_way_to_ask_questions_in_the_forum.3F

Open source including papertiger OCR/PDF scanning:
https://bitbucket.org/reiniero

Lazarus trunk+FPC trunk x86, Windows x64 unless otherwise specified

Polly

  • Newbie
  • Posts: 3
Re: program import CSV & perform calculation
« Reply #2 on: June 29, 2014, 10:10:14 am »
Hi thanks for the reply. I will take a look at those links.

I would prefer to use a csv but xls can work just as well.


Mike.Cornflake

  • Hero Member
  • *****
  • Posts: 1249
Re: program import CSV & perform calculation
« Reply #3 on: June 29, 2014, 10:41:41 am »
I suspect FPSpreadsheet can also handle CSV, but there is another approach as well.  TSDFDataset handles CSV well, giving you access to the DB controls for display/navigation.
Lazarus Trunk/FPC Trunk on Windows [7, 10]
  Have you tried searching this forum or the wiki?:   http://wiki.lazarus.freepascal.org/Alternative_Main_Page
  BOOKS! (Free and otherwise): http://wiki.lazarus.freepascal.org/Pascal_and_Lazarus_Books_and_Magazines

BigChimp

  • Hero Member
  • *****
  • Posts: 5740
  • Add to the wiki - it's free ;)
    • FPCUp, PaperTiger scanning and other open source projects
Re: program import CSV & perform calculation
« Reply #4 on: June 29, 2014, 10:44:20 am »
No, fpspreadsheet cannot handle CSV, sorry.

Also TSDFDataset may happen to work for your CSVs but it is in no way a CSV file format parser; see
http://wiki.freepascal.org/CSV
Want quicker answers to your questions? Read http://wiki.lazarus.freepascal.org/Lazarus_Faq#What_is_the_correct_way_to_ask_questions_in_the_forum.3F

Open source including papertiger OCR/PDF scanning:
https://bitbucket.org/reiniero

Lazarus trunk+FPC trunk x86, Windows x64 unless otherwise specified

Mike.Cornflake

  • Hero Member
  • *****
  • Posts: 1249
Re: program import CSV & perform calculation
« Reply #5 on: June 29, 2014, 11:21:11 am »
Also TSDFDataset may happen to work for your CSVs but it is in no way a CSV file format parser...

Ahh.  True.  I work in an industry for whom basic programming specifications are a mystery yet to be solved.  One of the software packages (used extensively in the North Sea *NOT* written by me or any company I've worked for as a developer) breaks if the users enters commas, quotes or carriage returns into any text field, as does another software package out in Asia.  Both these software packages backend to respectable databases (MSSQL & MySQL) but use CSV as an intermediate format for sharing data with other software packages...  Consequently reports generated from these are a grammatical nightmare :)  I have my rfc4180 rant email on hand so each year I can just re-forward it to the relevant dev teams...

I've only used TSDFDataset for simple CSV files, log files really.  I honestly thought TSDFDataset had had the rfc4180 fixes incorporated.  I've followed the link you provided (and from there into the bugtracker) and can see that this isn't the case.

Lazarus Trunk/FPC Trunk on Windows [7, 10]
  Have you tried searching this forum or the wiki?:   http://wiki.lazarus.freepascal.org/Alternative_Main_Page
  BOOKS! (Free and otherwise): http://wiki.lazarus.freepascal.org/Pascal_and_Lazarus_Books_and_Magazines

wp

  • Hero Member
  • *****
  • Posts: 6310
Re: program import CSV & perform calculation
« Reply #6 on: June 29, 2014, 12:09:26 pm »
@Polly: Assuming your data are in the first sheet of an xls file, you have a header row and two columns A and B which will have to be added in your program, then this is the way to get you started (no guarantee, did not run it...):

Code: [Select]
type
  TDataRec = record ColAValue, ColBValue, SumValue: Double; end;
var
  data: array of TDataRec;
  workbook: TsWorkbook;
  worksheet: TsWorksheet;
  row, lastrow: Integer;
begin
  workbook := TsWorkbook.Create;
  try
    workbook.ReadFromFile(AFileName, sfExcel8); 
      // assuming an Excel8 file, but you can drop this parameter, but you must be aware of some
      // exceptions in the debugger then, they don't occur without the debugger.
    worksheet := workbook.GetWorksheetByIndex(0);  // 1st worksheet
    lastrow := worksheet.GetLastRowIndex;
    SetLength(data, lastrow);        // normally: "lastrow+1", but we drop the first row!
    for row:=1 to lastrow do begin  // we skip the first row because of the headers!
      data[row].ColAValue := worksheet.ReadAsNumber(row, 0);
      data[row].ColBValue := worksheet.ReadAsNumber(row, 1);
      data[row].SumValue := data[row].ColAValue + data[row].ColBValue;
    end;
  finally
    workbook.Free;
  end;
Lazarus trunk / fpc 3.0.4 / all 32-bit on Win-10

Polly

  • Newbie
  • Posts: 3
Re: program import CSV & perform calculation
« Reply #7 on: July 23, 2014, 08:31:13 am »
thank you all for your replies.
Apologies for the delay, but I have been otherwise occupied with work and have not been able to give the programming any time.

I will have a look at the information provided and hopefully be able to use it.

Many thanks.

Polly

Fred vS

  • Hero Member
  • *****
  • Posts: 1675
    • miXimum is the DJ's best friend
Re: program import CSV & perform calculation
« Reply #8 on: July 27, 2014, 07:16:19 pm »
I use Lazarus 1.8.0 32/64 and FPC 3.0.3 32/64 on Linux Mint Mate 17 32/64, Windows 10, Windows 7 32/64, Windows XP 32,  FreeBSD 64 and Mac OS X Snow Leopard 32.
Widgetset: fpGUI, MSEgui, Win32, GTK2, Qt, Carbon.

https://github.com/fredvs