Recent

Author Topic: [SOLVED] How to save DBGrid data to CSV Text file or Clipboard  (Read 10159 times)

Gizmo

  • Hero Member
  • *****
  • Posts: 831
[SOLVED] How to save DBGrid data to CSV Text file or Clipboard
« on: September 20, 2017, 05:06:55 pm »
OK, so my journey into using SQLIte is gaining momentum, but problems are starting to appear.

I can't easily copy the content of the DBGrid to clipboard, whereas with StringGrids I could easily save using SG.CopyToClipboard

I can't easily save the content of the DBGrid to a CSV file, whereas with StringGrids I could easily save using SG.SaveToCSVFile. Although there is a "SaveToFile(filename)" procedure in DBGrid, the resulting output is scrambled XML from what I can see. Not a valid CSV file. So I'm wondering if I am going wrong somewhere there.

Anyway, that is two issues that I have incorporating SQLIte with DBGrids in Freepascal. My users have to have the ability to save to at least CSV file (I can manually do HTML) and they need to be able to copy to clipboard. From what I can see, neither seem built into the component. Or am I missing it?

There are a few hacks and other solutions that I have Googled involving creation of stringlists and memos and various other things, but they all seem rather convoluted as well.

Can I just check before I go any further that there is not an easy way to save\output the result of DBGrids to files and clipboards? If neither of these are possible, I don't think my adventures into SQLIte will continue. I need a robust solution that can store large volumes of data, and SQLIte with DBGrid seems to be the answer. But it doesn't seem to have the output flexibility that StringGrids have. That TBufDataset is fast but it is memory based, so it is likely to cause problems for large data volumes.

Thanks
« Last Edit: September 26, 2017, 06:17:17 pm by Gizmo »

taazz

  • Hero Member
  • *****
  • Posts: 5368
Re: How to save DBGrid data to CSV Text file or Clipboard
« Reply #1 on: September 20, 2017, 05:12:30 pm »
OK, so my journey into using SQLIte is gaining momentum, but problems are starting to appear.

I can't easily copy the content of the DBGrid to clipboard, whereas with StringGrids I could easily save using SG.CopyToClipboard

I can't easily save the content of the DBGrid to a CSV file, whereas with StringGrids I could easily save using SG.SaveToCSVFile. Although there is a "SaveToFile(filename)" procedure in DBGrid, the resulting output is scrambled XML from what I can see. Not a valid CSV file. So I'm wondering if I am going wrong somewhere there.

Anyway, that is two issues that I have incorporating SQLIte with DBGrids in Freepascal. My users have to have the ability to save to at least CSV file (I can manually do HTML) and they need to be able to copy to clipboard. From what I can see, neither seem built into the component. Or am I missing it?

There are a few hacks and other solutions that I have Googled involving creation of stringlists and memos and various other things, but they all seem rather convoluted as well.

Can I just check before I go any further that there is not an easy way to save\output the result of DBGrids to files and clipboards? If neither of these are possible, I don't think my adventures into SQLIte will continue. I need a robust solution that can store large volumes of data, and SQLIte with DBGrid seems to be the answer. But it doesn't seem to have the output flexibility that StringGrids have. That TBufDataset is fast but it is memory based, so it is likely to cause problems for large data volumes.

Thanks

1) sqlite already have the data saved in to your disk.
2) http://wiki.freepascal.org/lazdbexport
3) you can use  the export control above to copy the csv in to clipboard for a quick and dirty solution or write your own string builder from the fields of the dataset.

In the case of a dbgrid you must think of the grid as the window to your data and the query as the data manager anything your need on the data you ask the manager the window is there to allow your users to make sense of the data only.
Good judgement is the result of experience … Experience is the result of bad judgement.

OS : Windows 7 64 bit
Laz: Lazarus 1.4.4 FPC 2.6.4 i386-win32-win32/win64

Almir.Bispo

  • Jr. Member
  • **
  • Posts: 91
  • CSV Comp DB is the Best NoSQL
    • CSV Comp DB (NoSQL)
Re: How to save DBGrid data to CSV Text file or Clipboard
« Reply #2 on: September 20, 2017, 05:45:27 pm »
Another option for your application is to use a nosql database like csv comp db that already has most of the functions that you have requested in this post. In the system that I present already there is the function of transforming a csv table into html natively and you can use the ILDE-Pascal library as follows:
Code: Pascal  [Select][+][-]
  1. //id table is a csv table
  2. CSV_COMP_DB_execute(  html(db,id_table,table_border,font_size,font_color,css) );
  3.  
There are more than 50 functions for database management. Remember that a csv file is literally treated as a relational table or without a schema (in this case, csv comp db)

about its need, in some cases the dataset has SafeFileAs ()
Specifically the SdfDataSet
Look that: http://adltecnologia.blogspot.com.br

CSV Comp DB Developer {Pascal Lover}

Gizmo

  • Hero Member
  • *****
  • Posts: 831
Re: How to save DBGrid data to CSV Text file or Clipboard
« Reply #3 on: September 20, 2017, 06:52:29 pm »
Taaz

Good shout on the lazdbexport package. Installed that and within about 30 minutes I built a procedure for saving DBGrids to CSV files and that seems to work very well and fast.

It's just copying to clipboard which is presenting a problem now. Whereas I could use Clipboard.AsText (from clipbrd unit) before, I can't use that with DBGrid. So I'm trying to work out if there's a way to do it some other way - eiother the whole grid (if not more than say 10K rows) or just rows the user may have selected via a right click popup menu option. It's taking some getting used to, the DBGrid component, and learning how I can and can't use it. 

taazz

  • Hero Member
  • *****
  • Posts: 5368
Re: How to save DBGrid data to CSV Text file or Clipboard
« Reply #4 on: September 20, 2017, 08:14:06 pm »
Ok here is a quick and dirty implementation of a csv copy to clipboard. It will copy any dataset you feed it. Do what ever you want with it. It has no typing errors and it compiles but that is as far as I tested it.
I expect no problems from it if you do have any problems post here I'll address them promptly.
Code: Pascal  [Select][+][-]
  1. function GetCSVString(const aDataset:TDataset; const aSeperator:Char=','; const AddFieldNames:Boolean=false):String;
  2. const
  3.   StringDatatypes: set of TFieldType = [ftString, ftMemo, ftFmtMemo, ftFixedChar, ftWideString, ftFixedWideChar, ftWideMemo];
  4.  
  5. var
  6.   vCntr:integer;
  7.   vLine:String;
  8.  
  9.   function GetFieldValue(const aField:TField):string;
  10.   begin
  11.     Result := aField.AsString;
  12.     if aDataset.Fields[vCntr].DataType in StringDatatypes then Result := '"'+Result+'"';
  13.   end;
  14.   procedure TerminateLine;
  15.   begin
  16.     SetLength(vLine,Length(vLine)-1);//truncate the last ','
  17.     vLine:= vLine+LineEnding;
  18.   end;
  19.  
  20. begin
  21.   Result := '';
  22.   vLine := '';
  23.   if ADdFieldNames then begin
  24.     for vCntr := 0 to aDataset.FieldCount -1 do begin
  25.       vLine := vLine+aDataset.Fields[0].FieldName+aSeperator;
  26.     end;
  27.     TerminateLine;
  28.   end;
  29.   Result := Result + vLine;
  30.   aDataset.First;
  31.   while not aDataset.EOF do begin
  32.     vLine:= '';
  33.     for vCntr := 0 to aDataset.FieldCount-1 do begin
  34.       vLine := vLine+GetFieldValue(aDataset.Fields[vCntr])+aSeperator;
  35.     end;
  36.     TerminateLine;
  37.     Result := Result+vLine;
  38.     aDataset.Next;//<---- infinite loop breaker
  39.   end;
  40. end;
  41. procedure DatasetToClipBoard(const aDataset:TDataset);
  42. begin
  43.   Clipboard.AsText:= GetCSVString(aDataset, ',', True);
  44. end;
  45.  
If it works with out problems post an message so I will not have to check the forums every 30 minutes.

EDIT: Yeap as always forgot the most important part move to the next record. If you already got the code then sorry but you will have to wait a bit (about two or three life times) to finish. when you are bored waiting just ctrl+f2 and add the infinite loop breaker above.
« Last Edit: September 20, 2017, 08:41:58 pm by taazz »
Good judgement is the result of experience … Experience is the result of bad judgement.

OS : Windows 7 64 bit
Laz: Lazarus 1.4.4 FPC 2.6.4 i386-win32-win32/win64

PatBayford

  • Full Member
  • ***
  • Posts: 125
Re: How to save DBGrid data to CSV Text file or Clipboard
« Reply #5 on: September 20, 2017, 11:48:08 pm »
This question demonstrates perfectly a misunderstanding common to database novices - the confusion between the "data", held in the database,  and the GUI tool displaying that data.
NONE of the GUI tools store data in any way, shape or form, unless you specifically design them to do so.
The data is stored in the underlying database, whatever form it takes. The TDataSet component you drop on a Form is an access tool for this database, normally a TDataSource, which provides mechanisms for the TDataSet to read and write data to the database.
Therefore you can display data from several different sources ON THE SAME FORM, merely by pointing the TDataSource at a different database, and, maybe, changing the TDataSet.
As to the question proper, is there a good reason why Gizmo can't/shouldn't use one of the CSV DataSets available in Lazarus? This, after all, would store the data in a CSV file directly, can display it in a DBGrid, or even, a StringGrid. Copying such data to the Clipboard should be trivial.
Lazarus 1.8.0 FPC 3.0.2 SVN 56594 Windows 10 64bit (i386-win32-win32/win64)

Gizmo

  • Hero Member
  • *****
  • Posts: 831
Re: How to save DBGrid data to CSV Text file or Clipboard
« Reply #6 on: September 21, 2017, 10:49:39 am »
Quote
This question demonstrates perfectly a misunderstanding common to database novices

I'm familiar with how databases work. I also get that a database and a component are seperate. I am, however, unfamiliar with coding (programmatically) database integration with visual elements. My users need the ability to save what they see in the DBGrid in a human useable form - a CSV, and XLS, a PDF, whatever. Not a raw SQLIte database. They also need to be able to just click it and have it or part of it copied to a clipboard.

Which answers your next point :
Quote
is there a good reason why Gizmo can't/shouldn't use one of the CSV DataSets available in Lazarus
Quote
Copying such data to the Clipboard should be trivial.

Yes. Because I already am, but they don't scale to enterprise level for millions of files (the grid slows and becomes clunky with may entries) which is what I keep being asked by users. My program currently does utilise StringGrids and users can currently save to CSV and\or copy to clipboard (if not too many entries of course). For a few thousand files, its fine (sg.savetoCSVfile and clipboard.astext := sg). But it has matured beyond that. I'm merely asking for help with replicating that existing behaviour now I am moving the program to SQLite technology and ability to copy a few rows to clipboard from the grid is useful. My original question does in fact allude to this.
« Last Edit: September 21, 2017, 02:30:08 pm by Gizmo »

Gizmo

  • Hero Member
  • *****
  • Posts: 831
Re: How to save DBGrid data to CSV Text file or Clipboard
« Reply #7 on: September 21, 2017, 10:58:26 am »
Taaz....thank you so much for your amazing response. I tried your solution last night and it worked perfectly, first time. It's frankly amazing how you and others like you cobble these things together with a blink of an eye.

Thank you. SOLVED.

PatBayford

  • Full Member
  • ***
  • Posts: 125
Re: [SOLVED] How to save DBGrid data to CSV Text file or Clipboard
« Reply #8 on: September 22, 2017, 04:13:04 am »
OK - I understand your problem now Gizmo - I'm not used to colossal databases, well not as a coder anyway!
Glad you have a working solution.
Lazarus 1.8.0 FPC 3.0.2 SVN 56594 Windows 10 64bit (i386-win32-win32/win64)

Gizmo

  • Hero Member
  • *****
  • Posts: 831
Re: How to save DBGrid data to CSV Text file or Clipboard
« Reply #9 on: September 25, 2017, 12:52:17 pm »
Taaz

Actually there seems to be a snag...

After a bit more testing, I find that the copy to clipboard only partially takes place. If there are, say 12 rows, I notice that half way through one of the rows, for example the 4th row, it just suddenly stops. Sometimes half way through a filename, or a filepath or some other field. Other times, 10 rows will be copied. Other times only 2. See screenshot below showing it cutting off the last row of 4. And for some reason, the "id" entry is used for all 4 columns, instead of their respective names.

I've gone through with the debugger several times and it just keeps going. No problems or unexpected issues. Yet when I paste the clipboard into Notepad, there's often just a few values. I don't get it. Do you have any ideas Taaz?
« Last Edit: September 25, 2017, 01:03:58 pm by Gizmo »

taazz

  • Hero Member
  • *****
  • Posts: 5368
Re: How to save DBGrid data to CSV Text file or Clipboard
« Reply #10 on: September 25, 2017, 01:14:12 pm »
Taaz

Actually there seems to be a snag...

After a bit more testing, I find that the copy to clipboard only partially takes place. If there are, say 12 rows, I notice that half way through one of the rows, for example the 4th row, it just suddenly stops. Sometimes half way through a filename, or a filepath or some other field. Other times, 10 rows will be copied. Other times only 2. See screenshot below showing it cutting off the last row of 4.

I've gone through with the debugger several times and it just keeps going. No problems or unexpected issues. Yet when I paste the clipboard into Notepad, there's often just a few values. I don't get it. Do you have any ideas Taaz?
There are a couple of things wrong with the code posted that I can see now.

Code: Pascal  [Select][+][-]
  1. function GetCSVString(const aDataset:TDataset; const aSeperator:Char=','; const AddFieldNames:Boolean=false):String;
  2. const
  3.   StringDatatypes: set of TFieldType = [ftString, ftMemo, ftFmtMemo, ftFixedChar, ftWideString, ftFixedWideChar, ftWideMemo];
  4.  
  5. var
  6.   vCntr:integer;
  7.   vLine:String;
  8.   vFile :TFileStream = nil; //<--- 3 debug helper.
  9.   function GetFieldValue(const aField:TField):string;
  10.   begin
  11.     Result := aField.AsString;
  12.     if aField.DataType in StringDatatypes then Result := '"'+Result+'"';//<-- 1 use the field passed.
  13.   end;
  14.   procedure TerminateLine;
  15.   var
  16.     vMode : Word;
  17.   begin
  18.     SetLength(vLine,Length(vLine)-1);//truncate the last ','
  19.     vLine:= vLine+LineEnding;
  20.     //debug helper code to be deleted when finished
  21.     if Not Assigned(vFile) then begin
  22.       if FileExists(ChangeFileExt(Application.ExeName,'.dbg')) then vMode:= fmOpenWrite or fmShareExclusive else vMode:= fmCreate;
  23.       vFile := TfileStream.Create(ChangeFileExt(Application.ExeName,'.dbg'), vMode);
  24.     end;
  25.     vFile.Write(vLine[1],Length(vLine));
  26.     //debug helper code to be deleted when finished
  27.   end;
  28.  
  29. begin
  30.   Result := '';
  31.   vLine := '';
  32.   if ADdFieldNames then begin
  33.     for vCntr := 0 to aDataset.FieldCount -1 do begin
  34.       vLine := vLine+aDataset.Fields[vCntr].FieldName+aSeperator;//<-- 2 don't use the same field name for all fields
  35.     end;
  36.     TerminateLine;
  37.   end;
  38.   Result := Result + vLine;
  39.   aDataset.First;
  40.   while not aDataset.EOF do begin
  41.     vLine:= '';
  42.     for vCntr := 0 to aDataset.FieldCount-1 do begin
  43.       vLine := vLine+GetFieldValue(aDataset.Fields[vCntr])+aSeperator;
  44.     end;
  45.     TerminateLine;
  46.     Result := Result+vLine;
  47.     aDataset.Next;//<---- infinite loop breaker
  48.   end;
  49.   if assigned(vFile) then freeandnil(vFile);
  50. end;
  51.  
  52. procedure DatasetToClipBoard(const aDataset:TDataset);
  53. begin
  54.   Clipboard.AsText:= GetCSVString(aDataset, ',', True);
  55. end;
But those are minor problems that do not create the problem shown. I have added a small debug code on the procedure TerminateLine to write the string in a file.
This will give you a way to check if the code produces the error or the assignment to the clipboard somehow creates the problem if hte results are the same the next thing to check is that the aField.AsString method returns the complete string and not a part of it. It would help if I had a small sample application to work against, just a small db with a couple of records that show the problem and a form that loads the data in to a dataset and copys them in to the clipboard
I have the impression that the asString method truncates the data to 255 bytes/characters etc but I need to confirm it and find a work around.
Good judgement is the result of experience … Experience is the result of bad judgement.

OS : Windows 7 64 bit
Laz: Lazarus 1.4.4 FPC 2.6.4 i386-win32-win32/win64

Gizmo

  • Hero Member
  • *****
  • Posts: 831
Re: How to save DBGrid data to CSV Text file or Clipboard
« Reply #11 on: September 25, 2017, 02:13:34 pm »
Taaz...

Firstly, thanks for the suggested changes.

Secondly, I applied your proceudre and function to the sqlite demo in the Lazarus\examples\database folder, and, it works perfectly in that!! So it must be something to do with my database grid or something weird. I have no idea yet, but I didn't want you to continue working out how to fix something that actually works fine. It's just that I must have implemented it wrong. I'll report back when I work out what

taazz

  • Hero Member
  • *****
  • Posts: 5368
Re: How to save DBGrid data to CSV Text file or Clipboard
« Reply #12 on: September 25, 2017, 03:31:55 pm »
Taaz...

Firstly, thanks for the suggested changes.

Secondly, I applied your proceudre and function to the sqlite demo in the Lazarus\examples\database folder, and, it works perfectly in that!! So it must be something to do with my database grid or something weird. I have no idea yet, but I didn't want you to continue working out how to fix something that actually works fine. It's just that I must have implemented it wrong. I'll report back when I work out what
make sure to test it on long string fields and memos it might look right on short ones.
Good judgement is the result of experience … Experience is the result of bad judgement.

OS : Windows 7 64 bit
Laz: Lazarus 1.4.4 FPC 2.6.4 i386-win32-win32/win64

Gizmo

  • Hero Member
  • *****
  • Posts: 831
Re: [SOLVED] How to save DBGrid data to CSV Text file or Clipboard
« Reply #13 on: September 26, 2017, 06:19:25 pm »
Taaz

A bit of a chickens way out but I took your debug writing file, and then created a StringList and used SL.LoadFromFile to open the newly created file, and then used Clipboard.AsText := sl.Text

I know thats horribly inproper but it does work. So thanks very much for the file writing code, which, by the way, writes everything out fully to file. I have no idea why the clipboard element was not working in my program (as I say, it worked fine in the example project so it's something to do with my program).

 

TinyPortal © 2005-2018