?
Solved

Component Urgently Needed!

Posted on 2007-10-03
40
Medium Priority
?
552 Views
Last Modified: 2010-04-05
Hi all,

I really need some component help here!

I am using a ListView (Actually AdvListView) to import a CSV file....Search different results on it and the save it back to CSV.

The problem is that a ListView doesn't seem to be the most powerful component for this.

I need to find some component that will let my Load a CSV ... Make different Queries on it and the sve the results back to CSV.

I'll give 500 points for a solution.

Hope you can help!

Thanks

ST3VO
0
Comment
Question by:ST3VO
  • 18
  • 11
  • 4
  • +4
40 Comments
 
LVL 26

Expert Comment

by:Eddie Shipman
ID: 20009097
Why not import into a database, hook it up to a DBGrid and then save the results back out?
0
 
LVL 18

Expert Comment

by:Johnjces
ID: 20009116
St3evo,

Here is a procedure that will take your listbox items and save them back as a csv. I am not familiar with the AdvListView but the procedure is easily modifiable.

procedure SaveAsCSV(FileName : String; ListBox : TStrings);
var
  ss: TStringStream;
  fs: TFileStream;
begin                      //ListBox.Items.CommaText
  ss := TStringStream.Create(ListBox.CommaText);
  fs := TFileStream.Create(FileName, fmCreate);
  try
    ss.Position := 0;
    fs.CopyFrom(ss, ss.Size);
  finally
    ss.Free;
    fs.Free;
  end
end;

Usage:

SaveAsCSV(SaveDialog1.FileName, ListBox2.Items);

Hope this helped you.

John
0
 

Author Comment

by:ST3VO
ID: 20009186
Hi all,

My problem is that I get the data is csv format and so, i need to save it back to csv format.

All this already works fine with the ListView BUT I need to perform different Queries.

For example:

Search for all the Ford cars with the price lower that 2000 pounds any other specific stuff which I would be able to do without any problem using TQuery BUT there are not DB components that I know that can Read a CSV then use TQuery to do whatever and then save back to CSV, so I cannot use DBAware components for this because there are none available that I know of.

So, on a last resort I need to find another component which I can use to do more powerful Queries, unless anyone has a better idea :o/

0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 26

Expert Comment

by:Eddie Shipman
ID: 20009535
If you are not opposed to using mySQL as your database, it has functions to do that for you.
0
 
LVL 17

Expert Comment

by:TheRealLoki
ID: 20009600
How exactly do you want the "search" to work?
Do you need to search the csv file as you load it , and only display matches in teh listview?
or
Do you want to load the entire csv file, and then do a search, which then removes listview rows that do not match?
or
simply "finds" the matching row that you are searching for?
or something else?
Let me know please.
0
 
LVL 17

Expert Comment

by:TheRealLoki
ID: 20009669
how "powerful" do the queries need to be also? eg. is it ok if they only treat teh adta as etxt, or do you need it to support datetime, numbers, etc. as well?
would the query be on 1 "field" only? or on several
eg. select * from csvfile where field1 = "bob" and field2 <> "fiji" and field3 like 'add%'
?
0
 

Author Comment

by:ST3VO
ID: 20009911
OK. I need to load the CSV first. Then Perform Queries on it and then Save it back to CSV.

I get the data in CSV format so have to process and work with the CSV format.

The Headers on the CSV are an follows:

ID, Name,  VehicleID, Make, Registration Price, Year, Color, Category...etc

I must keep all the data at all times! With this I mean that I don't delete anything when doing the queries.

The Query Results go first and everthing other data is followed after (unsorted).

Well, this will only happen when I need all Ford Cars, for example. All the ford cars are displayed first and then all the others.

This is not a problem with the listview BUT I also need to search by Category..when mean for example:

Search for all Sport Cars  ... on the Category Column...so all Sport Cars come frst and then all the rest after.

Also Search for all Ford Cars ...which price is Under 2000 Pounds.

THese are the kind of Queries I need to perform.

Easy on a TQuery(SQL) but I don't know how to do this on a ListView.

Hope this helps!!!

 
0
 
LVL 26

Expert Comment

by:Eddie Shipman
ID: 20010049
Listviews cannot be "queried" as such, you have to filter the data in a compare function.

Like I have said, it is better to import your data into a DB structure, do your queries and
then do the export back to CSV.

0
 

Author Comment

by:ST3VO
ID: 20010132
That would be great but can it be done?

I would need to do the following:

1. Import the CSV into the DB components ... Including being able to use TQuery as I need to perform SQL Scripts for the Queries.

2. Do the stuff

3. Export from the DB Comp(s) to CSV.

Can this be done? Because I've searched everywhere but found nothing yet :o/
0
 
LVL 17

Expert Comment

by:TheRealLoki
ID: 20010204
so your queries are not really queries, they are just for sorting?
ie. you always display all rows, but in a different order?
Then you save all the rows in that order
is this correct?
0
 
LVL 17

Expert Comment

by:TheRealLoki
ID: 20010224
how many rows are you expecting in the listview? is. is it < 100, < 1000, or really big?
0
 

Author Comment

by:ST3VO
ID: 20010272
Well, they are kind of sorting. But a little more complex as I might need to sort information with several Columns.

For example All Ford Car (For Make Column) with Price (From Price Column) < 2000.

Data It can go from 100 to 30000 rows.

Normally not more that 1000 but I need capability for 30000.

0
 
LVL 26

Expert Comment

by:Eddie Shipman
ID: 20010471
OK,  there are 2 options I see, here.

1. I highly recommend SMImport/SMExport controls by Mike Shklonik: http://www.scalabium.com
They will allow the import/export to/from CSV into your dataset.
Then you can use any database and TDataset descendant (TQuery, TADOQuery, etc).

2. Other option is to use mySQL and use the SQL LOAD DATA INFILE and SELECT ... INTO OUTFILE
to import and export your CSV. Very simple to do.
0
 
LVL 26

Expert Comment

by:Eddie Shipman
ID: 20010479
In using mySQL, there are several data-aware controls for handling mySQL data.
ZeosLib is one that is free and avail here: http://zeos.firmos.at/portal.php
0
 

Author Comment

by:ST3VO
ID: 20010526
OK Thanks ..I'll check those components out and get back to you!!! Cheers!!!
0
 

Author Comment

by:ST3VO
ID: 20010888
I've checked out the SMImport/SMExport Components.

They look very interesting but I still don't know how I am going to be able to use TQuery where importing a CSV file.

In the component itself you browse to the CSV file. That's fine BUT where you write the SQL string on TQuery there's a mayor issue...I cannot!

Why? Because I cannot specify for example: SELECT * FROM Data.csv

Know what I mean?  Is there a way round this?

I've already bought 2 components that do importing from CSV and I had the same problem :o/

Any ideas?

0
 
LVL 27

Expert Comment

by:kretzschmar
ID: 20012200
following should work (no additional thirdparty-component needed)(using bde)

- create a schema-file for your textfile (see bde help about schema)
- link a ttable with tableType TTASCII
- create a (for ex.) a paradox-table using the batchmove-method
- do your queries and updates on this paradox-table
- write back the the changes to textfile using batchcmove-method

sample maybe next weekend

meikl ;-)
0
 

Author Comment

by:ST3VO
ID: 20012218
Hmm...And I need the BDE right? No way possible without it?
0
 
LVL 27

Expert Comment

by:kretzschmar
ID: 20012455
will see, if there is another chance without bde

meikl ;-)
0
 

Author Comment

by:ST3VO
ID: 20012544
Hi again meikl,

THinking about it further. I'll got either way with or without BDE!!! I just need to get this sorted as it's driving me crazy!!!

Thanks :o)

0
 
LVL 26

Expert Comment

by:Tomas Helgi Johannsson
ID: 20012974
   Hi!

In a previous question you asked you accepted the solution pointing to ADO components which is one easy way to import CSV files into TDataSet component.
To that compent you could attach some other DataAware components that can
manipulate the data.
Like after doing the import of the CSV file the DataSet has the structure of the CSV file. Then there is an easy way to add columns and data by simply adding a Field to the DataSet and update each row with the value of that particular Field.
You can also apply an Filter to that DataSet either by issuing a Filter statement onto the DataSet ( DataSet.Filter := '....'; DataSet.Filtered := true; ) or use DataAware component like DBComboBox or DBListBox to filter out the desired data.
Then when you save the data back to an CSV file you have the desired data with the new columns in your CSV file.

Regards,
   Tomas Helgi
0
 

Author Comment

by:ST3VO
ID: 20013167
Hi TomasHelgi,

I know I accepted ADO and I got it working BUT the needs are getting more complex and I need to be able
to perform SQL Queries ...Load From A CSV and Save the results to CSV.

I've just purchased the TMS Component Pack. I nearly have it working now with the DBAdvGrid. I just need to be able to re-write the ID column so instead of:

ID
54
23
2
1
65
Etc..... I get

ID
1
2
3
4
5
etc...

But without moving the other Column Positioning

 
0
 
LVL 26

Expert Comment

by:Tomas Helgi Johannsson
ID: 20013247
Here are some Tips on TDBAdvGrid

http://www.rwblinn.de/112k0t5.htm
And this is from the TMS Tips&Tricks
TDBAdvStringGrid

1. Dataset versus internal grid sort

When an internal grid sort is used, it is required that grid.KeepLinked is set to false. For performing a dataset sort from TDBAdvStringGrid, the technique shown in sample app http://www.tmssoftware.com/dbsort.zip can be used.

Regards
  Tomas Helgi
0
 

Author Comment

by:ST3VO
ID: 20013495
I just need to translate this:

  for i := 0 to listView1.Items.Count-1 do
       listView1.Items[i].Caption :=intToStr(i+1);

For use on TDBAdvGrid and I think it's done :o)

I'll check those links!!

0
 
LVL 26

Assisted Solution

by:Tomas Helgi Johannsson
Tomas Helgi Johannsson earned 300 total points
ID: 20013611
   Hi!

Check out these examples on moving data between Grid and ListView
http://www.swissdelphicenter.ch/torry/showcode.php?id=589
http://www.swissdelphicenter.ch/torry/showcode.php?id=590
Hope they help.

Regards,
  Tomas Helgi
0
 

Author Comment

by:ST3VO
ID: 20013628
OK...Thanks :o)
0
 
LVL 10

Expert Comment

by:dinilud
ID: 20013820
Try this. i think this will work for you.

unit1.dfm
========

object Form1: TForm1
  Left = 23
  Top = 124
  Width = 712
  Height = 415
  Caption = 'Form1'
  Color = clBtnFace
  Font.Charset = DEFAULT_CHARSET
  Font.Color = clWindowText
  Font.Height = -11
  Font.Name = 'MS Sans Serif'
  Font.Style = []
  OldCreateOrder = False
  PixelsPerInch = 96
  TextHeight = 13
  object DBGrid1: TDBGrid
    Left = 8
    Top = 8
    Width = 673
    Height = 313
    DataSource = DataSource1
    TabOrder = 0
    TitleFont.Charset = DEFAULT_CHARSET
    TitleFont.Color = clWindowText
    TitleFont.Height = -11
    TitleFont.Name = 'MS Sans Serif'
    TitleFont.Style = []
    OnTitleClick = DBGrid1TitleClick
  end
  object Button1: TButton
    Left = 72
    Top = 336
    Width = 137
    Height = 33
    Caption = 'Open'
    TabOrder = 1
    OnClick = Button1Click
  end
  object Button2: TButton
    Left = 232
    Top = 336
    Width = 137
    Height = 33
    Caption = 'Sort by Col 2'
    TabOrder = 2
    OnClick = Button2Click
  end
  object Button3: TButton
    Left = 392
    Top = 336
    Width = 137
    Height = 33
    Caption = 'Save'
    TabOrder = 3
    OnClick = Button3Click
  end
  object AsciiDataset1: TAsciiDataset
    DatabaseName = '.'
    Left = 336
    Top = 88
  end
  object ClientDataSet1: TClientDataSet
    Aggregates = <>
    Params = <>
    ProviderName = 'DataSetProvider1'
    Left = 248
    Top = 80
  end
  object DataSetProvider1: TDataSetProvider
    DataSet = AsciiDataset1
    Left = 288
    Top = 104
  end
  object DataSource1: TDataSource
    DataSet = ClientDataSet1
    Left = 264
    Top = 40
  end
  object OpenDialog1: TOpenDialog
    Left = 344
    Top = 192
  end
end

Unit1.pas
========

unit Unit1;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, StdCtrls, Grids, DBGrids, DB, Provider, DBClient, AsciiDataset;

type
  TForm1 = class(TForm)
    AsciiDataset1: TAsciiDataset;
    ClientDataSet1: TClientDataSet;
    DataSetProvider1: TDataSetProvider;
    DataSource1: TDataSource;
    DBGrid1: TDBGrid;
    Button1: TButton;
    Button2: TButton;
    Button3: TButton;
    OpenDialog1: TOpenDialog;
    procedure Button1Click(Sender: TObject);
    procedure DBGrid1TitleClick(Column: TColumn);
    procedure Button2Click(Sender: TObject);
    procedure Button3Click(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  Form1: TForm1;

implementation

{$R *.dfm}

procedure  createIndex(cds:TClientDataset;fldName,indxName,desc :String);
begin
 if cds.IndexDefs.IndexOf(indxName) > 0 then
    Exit;
  if pos(';',fldName)>0 then
  begin
      if desc ='D' then
          cds.AddIndex(indxName,fldName,[ixCaseInsensitive, ixDescending],'','',0)
      else
          cds.AddIndex(indxName,fldName,[ixCaseInsensitive],'','',0);
  end
  else
  if cds.FieldByName(fldName).FieldKind =fkLookup then
  begin
         if desc ='D' then
          cds.AddIndex(indxName,cds.FieldByName(fldName).KeyFields,[ixCaseInsensitive, ixDescending],'','',0)
      else
          cds.AddIndex(indxName,cds.FieldByName(fldName).KeyFields,[ixCaseInsensitive],'','',0);
  end
  else
  begin
      if desc ='D' then
          cds.AddIndex(indxName,fldName,[ixCaseInsensitive, ixDescending],'','',0)
      else
          cds.AddIndex(indxName,fldName,[ixCaseInsensitive],'','',0);
  end;
  cds.first;
end;

procedure sortGrid(column:TColumn);
var indexName,fldName : String;
  cds : TClientDataset;
begin
  try
    cds := TClientDataset(column.field.Dataset);
    indexName := cds.IndexName;
    fldName :=  column.Field.FieldName;
    if indexName = fldName then
    begin
        createIndex(cds,fldName,'D' + fldName,'D');
        cds.indexName := 'D' + fldName;
    end
    else
    begin
         createIndex(cds,fldName,fldName,'A');
         cds.indexName := fldName;
    end;
  except
  end;
end;


procedure TForm1.Button1Click(Sender: TObject);
begin
  if OpenDialog1.Execute then
  begin
      ClientDataSet1.Close;
      AsciiDataset1.DatabaseName:=ExtractFileDir(OpenDialog1.FileName);
      AsciiDataset1.TableName:=ExtractFileName(OpenDialog1.FileName);
      ClientDataSet1.Open;
  end;
end;

procedure TForm1.DBGrid1TitleClick(Column: TColumn);
begin
  sortGrid(Column);
end;

procedure TForm1.Button2Click(Sender: TObject);
begin
  sortGrid(DBGrid1.Columns[1]);
end;

procedure TForm1.Button3Click(Sender: TObject);
var i:Integer;
begin
  AsciiDataset1.Open;
  AsciiDataset1.ClearAll;
  ClientDataSet1.First;
  while not ClientDataSet1.Eof do
  begin
     AsciiDataset1.Append;
     for i:=0 to ClientDataSet1.FieldCount-1 do
     begin
       AsciiDataset1.FieldByName(ClientDataSet1.Fields[i].FieldName).Value:=
                                               ClientDataSet1.Fields[i].Value;
     end;
     AsciiDataset1.Post;
     ClientDataSet1.Next;
  end;
  AsciiDataset1.Close;
end;

end.
0
 
LVL 26

Expert Comment

by:Eddie Shipman
ID: 20014070
[quote]I still don't know how I am going to be able to use TQuery where importing a CSV file[/quote]

YOU DON'T USE TQUERY ON THE CSV FILE...

You use the SMImport component to import it into a database, use TQuery on the database and
use SMExport to export the TQuery results to CSV.

How difficult can it be to understand, here?
0
 

Author Comment

by:ST3VO
ID: 20014691
Hi EddieShipman,

I DID try out the component and I just needed a bit more to fit my needs.
I am now trying out DBAdvGrid (DBGrid) and I am nearly there.
I am importing the data and using TQuery for the SQL Scripts...also Exporting to CSV.

I just need to find out how to replace the contents of Column 0 and it's all done!

I appreciate all your help in all the ways to do this but with my limited knowledge and skills I have to stick with whatever I can get to work :o/

I have nealy go it working now with the DBGrid (Adv).

I DID also try all the other components including the one you posted about.

I purchased AsciiDataset and wrote to them too and they said that there is not way of getting SQL Queries on the component.

So, I've now purchased a whole TMS Component Pack and the DBGrid is the one I've got furthest to what I need.

It's costing me a fortune to get this sorted :o)

0
 

Author Comment

by:ST3VO
ID: 20014747
dinilud,

You code is also great but I need more than just filtering.

For example .... Search for all items from a specific column add them first, then the rest and order them by price.

I've done this via SQL:

SELECT * from Table where Category = 'FORD' UNION  ALL SELECT * from Table where Category <> 'FORD'

It gives me more flexibilty and I get the results.



0
 
LVL 26

Accepted Solution

by:
Eddie Shipman earned 1200 total points
ID: 20015127
OK, here's some code I found on the Borland newsgroups that I modified to do what you want.
Change the variables in Button1.click to reflect your CSV data. You MUST have a header row in
the file for this to work. You click the Open File button, select your CSV file and then click the Save
Results Button and select or name a file to export to.


unit ExpData;

interface

uses
  Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms,
  Dialogs, StdCtrls, checklst, ExtCtrls, DB, DBTables;

type
  TfmExpData = class(TForm)
    clbFields: TCheckListBox;
    Label1: TLabel;
    btnSaveResults: TButton;
    btnClose: TButton;
    btnSelectAll: TButton;
    btnToggle: TButton;
    SaveDialog: TSaveDialog;
    rgFormat: TRadioGroup;
    rgScope: TRadioGroup;
    OpenDialog: TOpenDialog;
    btnOpenFile: TButton;
    Table1: TTable;
    Query1: TQuery;
    procedure btnSelectAllClick(Sender: TObject);
    procedure btnToggleClick(Sender: TObject);
    procedure btnSaveResultsClick(Sender: TObject);
    procedure btnOpenFileClick(Sender: TObject);
    procedure btnCloseClick(Sender: TObject);
  private
    { Private declarations }
    FDataSet: TDataSet;
    FTableName: String;
    FName: string;
    procedure CopyToAscii(FileName: string; FieldSep, RowSep: string;
              QuotedValues, AllRecords: boolean);
    procedure CopyToTable(TableName: string; AllRecords: boolean);
  public
    { Public declarations }
    procedure ExportDataSet(DataSet: TDataSet; Name: string);
    procedure ImportDataSet(DataSet: TDataSet; Name: string);
  end;

var
  fmExpData: TfmExpData;

procedure CopyFromAscii(DataSet: TDataSet; FileName: string; FieldSep, RowSep: string;
                        QuotedValues: boolean);

implementation

{$R *.DFM}

const
  REP_ROWSEP: string = '^M';
  QUOTE: string = '"';
  REP_QUOTE: string = '^''';

const
  FileExt: array [0..3] of string = ('.txt', '.csv', '.tsv', '.dbf');

procedure TfmExpData.ExportDataSet(DataSet: TDataSet; Name: string);
var
  i: integer;
begin
  FName := Name;
  if FDataSet <> DataSet then
  begin
    FDataSet := DataSet;
    clbFields.Clear;
    with FDataSet do
    for i := 0 to FieldCount - 1 do
      if Fields[i].Visible then
        clbFields.Items.Add(Fields[i].DisplayName);
  end;
end;

procedure TfmExpData.btnSelectAllClick(Sender: TObject);
var
  i: integer;
begin
  with clbFields do
  begin
    for i := 0 to Items.Count - 1 do
      Checked[i] := true;
    end;
end;

procedure TfmExpData.btnToggleClick(Sender: TObject);
var
  i: integer;
begin
  with clbFields do
  begin
    for i := 0 to Items.Count - 1 do
      Checked[i] := not Checked[i];
  end;
end;

procedure TfmExpData.btnSaveResultsClick(Sender: TObject);
var
  AnyField: boolean;
  i: integer;
  FileName: string;
begin
  AnyField := false;
  with clbFields do
  begin
    for i := 0 to Items.Count - 1 do
      AnyField := AnyField or Checked[i];
  end;
  if not AnyField then
  begin
    clbFields.SetFocus;
    raise Exception.Create('Please select some data fields for export!');
  end;
  SaveDialog.FilterIndex := rgFormat.ItemIndex + 1;
  SaveDialog.FileName := FName + FileExt[rgFormat.ItemIndex];
  if SaveDialog.Execute then
  begin
    FileName := Trim(SaveDialog.FileName);
    case SaveDialog.FilterIndex of
    1:
      begin
        CopyToAscii(FileName, ' ', #13#10, false, rgScope.ItemIndex = 0);
        ModalResult := mrOk;
      end;
    2:
      begin
        CopyToAscii(FileName, ',', #13#10, true, rgScope.ItemIndex = 0);
        ModalResult := mrOk;
      end;
    3:
      begin
        CopyToAscii(FileName, #9, #13#10, true, rgScope.ItemIndex = 0);
        ModalResult := mrOk;
      end;
    4:
      begin
        CopyToTable(FileName, rgScope.ItemIndex = 0);
        ModalResult := mrOk;
      end;
    end;
  end;
  FDataset.Close;
  DeleteFile(FTableName);
end;

procedure TfmExpData.CopyToAscii(FileName: string; FieldSep, RowSep: string;
                                 QuotedValues, AllRecords: boolean);

var
  // RepRowSep is a replacement string when RowSep occurs in a string data field value
  RepRowSep: string;

  function FieldToStr(fidx: integer): string;
  var
    Text: string;
  begin
    Text := StringReplace(FDataSet.Fields[fidx].AsString, RowSep, RepRowSep, [rfReplaceAll]);
    if QuotedValues and ((FDataSet.Fields[fidx] is TStringField) or (FDataSet.Fields[fidx] is TDateTimeField)) then
      result := QUOTE + StringReplace(Text, QUOTE, REP_QUOTE, [rfReplaceAll]) + QUOTE
    else
      result := Text;
  end;

var
  fh: integer;
  cBuffer: string;
  MyOpen: boolean;
  fData, fCol: integer;
  FirstField: boolean;
  Text: string;
  RecTag: integer;
begin
  FirstField := false;
  RecTag := 0;
  if not FDataSet.Active then
  begin
    MyOpen := true;
    FDataSet.Open;
  end
  else
  begin
    MyOpen := false;
    if AllRecords then
      FDataSet.First;
  end;

  FDataSet.DisableControls;
  try
    fh := FileCreate(FileName);
    if fh < 0 then
      raise Exception.Create('Can''t create file ' + FileName);

    try
      if QuotedValues then
        RepRowSep := REP_ROWSEP
      else
      begin
        // If field values are not quoted than we have to determine max width
        // for all the columns. We will store this information in tag property
        // of datafield or if only one record is included then we need only one
        // width tag (RecTag) for the first fieldname column
        RecTag := Length('FieldName');
        fCol := -1;
        for fData := 0 to FDataSet.FieldCount - 1 do
        begin
          if FDataSet.Fields[fData].Visible then
          begin
            Inc(fCol);
            if clbFields.Checked[fCol] then
              with FDataSet.Fields[fData] do
              begin
                if AllRecords then
                  tag := Length(DisplayName)
                else if Length(DisplayName) > RecTag then
                  RecTag := Length(DisplayName);
              end;
          end;
        end;
        if AllRecords then
        begin
          RepRowSep := ' ';
          while not FDataSet.Eof do
          begin
            fCol := -1;
            for fData := 0 to FDataSet.FieldCount - 1 do
            begin
              if FDataSet.Fields[fData].Visible then
              begin
                Inc(fCol);
                if clbFields.Checked[fCol] then
                  with FDataSet.Fields[fData] do
                    if tag < Length(AsString) then
                      tag := Length(AsString);
              end;
              FDataSet.Next;
            end;
          end;
          FDataSet.First;
        end
        else
          RepRowSep := Format('%s%-*s%s', [RowSep, RecTag, '', FieldSep]);
      end;

      if AllRecords then
      begin
        FirstField := true;
        cBuffer := '';
      end
      else if QuotedValues then
        cBuffer := 'FieldName' + FieldSep + 'FieldValue' + RowSep
      else
        cBuffer := Format('%-*s%s%s', [RecTag, 'FieldName', FieldSep, 'FieldValue']) + RowSep;
      fCol := -1;
      for fData := 0 to FDataSet.FieldCount - 1 do
      begin
        if FDataSet.Fields[fData].Visible then
        begin
          Inc(fCol);
          if clbFields.Checked[fCol] then
          begin
            if QuotedValues then
            begin
              Text := FDataSet.Fields[fData].DisplayName;
            end
            else
            begin
              if AllRecords then
                Text := Format('%-*s', [FDataSet.Fields[fData].Tag, FDataSet.Fields[fData].DisplayName])
              else
                Text := Format('%-*s', [RecTag, FDataSet.Fields[fData].DisplayName]);
            end;
            if AllRecords then
            begin
              if FirstField then
              begin
                FirstField := false;
                cBuffer := cBuffer + Text;
              end
              else
              begin
                cBuffer := cBuffer + FieldSep + Text;
              end;
            end
            else
              cBuffer := cBuffer + Text + FieldSep + FieldToStr(fData) + RowSep;
          end;
        end;
      end;

      if AllRecords then
        cBuffer := cBuffer + RowSep;
      FileWrite(fh, cBuffer[1], Length(cBuffer));

      if AllRecords then
      begin
        while not FDataSet.Eof do
        begin
          cBuffer := '';
          FirstField := true;
          fCol := -1;
          for fData := 0 to FDataSet.FieldCount - 1 do
          begin
            if FDataSet.Fields[fData].Visible then
            begin
              Inc(fCol);
              if clbFields.Checked[fCol] then
              begin
                if QuotedValues then
                  Text := FieldToStr(fData)
                else
                  Text := Format('%-*s', [FDataSet.Fields[fData].Tag, FieldToStr(fData)]);
                if FirstField then
                begin
                  FirstField := false;
                  cBuffer := cBuffer + Text;
                end
                else
                  cBuffer := cBuffer + FieldSep + Text;
              end;
            end;
          end;
          cBuffer := cBuffer + RowSep;
          FileWrite(fh, cBuffer[1], Length(cBuffer));
          FDataSet.Next;
        end;
      end;
    finally
      FileClose(fh);
    end;

  finally
    FDataSet.EnableControls;
    if MyOpen and FDataSet.Active then
      FDataSet.Close;
  end;
end;

procedure TfmExpData.CopyToTable(TableName: string; AllRecords: Boolean);
var
  Table: TTable;
  MyOpen: boolean;
  FNameLen, FValueLen: integer;
  fData, fCol, fTab: integer;
begin
  if not FDataSet.Active then
  begin
    MyOpen := true;
    FDataSet.Open;
  end
  else
  begin
    MyOpen := false;
    if AllRecords then
      FDataSet.First;
  end;

  Table := TTable.Create(Self);
  try
    Table.DatabaseName := ExtractFilePath(TableName);
    Table.TableName := ExtractFileName(TableName);
    Table.TableType := ttDBase;

    Table.FieldDefs.Clear;
    if AllRecords then
    begin
      fCol := -1;
      for fData := 0 to FDataSet.FieldCount - 1 do
      begin
        if FDataSet.Fields[fData].Visible then
        begin
          Inc(fCol);
          if clbFields.Checked[fCol] then
            Table.FieldDefs.Add(FDataSet.Fields[fData].FieldName,
          FDataSet.Fields[fData].DataType,
          FDataSet.Fields[fData].Size, false);
        end;
      end;
    end
    else
    begin
      // We have to determine fields len
      FNameLen := 0;
      FValueLen := 0;
      fCol := -1;
      for fData := 0 to FDataSet.FieldCount - 1 do
      begin
        if FDataSet.Fields[fData].Visible then
        begin
          Inc(fCol);
          if clbFields.Checked[fCol] then
          begin
            with FDataSet.Fields[fData] do
            begin
              if Length(DisplayName) > FNameLen then
                FNameLen := Length(DisplayName);
              if Length(AsString) > FValueLen then
                FValueLen := Length(AsString);
            end;
          end;
        end;
        Table.FieldDefs.Add('FieldName', ftString, FNameLen, false);
        Table.FieldDefs.Add('FieldValue', ftString, FValueLen, false);
      end;
    end;

    Table.CreateTable;
    Table.Open;

    if AllRecords then
    begin
      while not FDataSet.Eof do
      begin
        Table.Append;
        fTab := 0;
        fCol := -1;
        for fData := 0 to FDataSet.FieldCount - 1 do
        begin
          if FDataSet.Fields[fData].Visible then
          begin
            Inc(fCol);
            if clbFields.Checked[fCol] then
            begin
              if not FDataSet.Fields[fData].IsNull then
                Table.Fields[fTab].Value := FDataSet.Fields[fData].Value;
              Inc(fTab);
            end;
          end;
        end;
        Table.Post;
        FDataSet.Next;
      end;
    end
    else
    begin
      fCol := -1;
      for fData := 0 to FDataSet.FieldCount - 1 do
      begin
        if FDataSet.Fields[fData].Visible then
        begin
          Inc(fCol);
          if clbFields.Checked[fCol] then
          begin
            Table.Append;
            Table.Fields[0].Value := FDataSet.Fields[fData].DisplayName;
            Table.Fields[1].Value := FDataSet.Fields[fData].AsString;
            Table.Post;
          end;
        end;
      end;
    end;
  finally
    if MyOpen and FDataSet.Active then
      FDataSet.Close;
    if Assigned(Table) then
    begin
      if Table.Active then
        Table.Close;
      Table.Free;
    end;
  end;
end;

procedure TfmExpData.ImportDataSet(DataSet: TDataSet; Name: string);
var
  FileName: string;
begin
  if fmExpData = nil then
    fmExpData := TfmExpData.Create(nil);

  fmExpData.OpenDialog.FileName := Name + FileExt[fmExpData.OpenDialog.FilterIndex];
  if fmExpData.OpenDialog.Execute then
  begin
    FileName := Trim(fmExpData.OpenDialog.FileName);
    case fmExpData.OpenDialog.FilterIndex of
    1:
      begin
        CopyFromAscii(DataSet, FileName, ',', #13#10, true);
      end;
    2:
      begin
        CopyFromAscii(DataSet, FileName, #9, #13#10, true);
      end;
    end;
  end;
end;

procedure CopyFromAscii(DataSet: TDataSet; FileName: string; FieldSep, RowSep: string;
                        QuotedValues: boolean);
const
  FILE_BUFFER = 4096;
var
  MyOpen: boolean;
  fh: integer;
  FileEof, FileEol, FileBol: boolean;
  cBuffer: string;
  nBufSize, nBufLen, nBufPos, nRead: integer;
  nRowSepPos: integer;
  cField: string;

  function NextField: boolean;
  var
    nFieldSepPos: integer;
    cSearchSep: string;
  begin
    result := false;
    if (nRead <> 0) and (nBufSize - nBufLen + nBufPos-1 > FILE_BUFFER) then
    begin
      if nBufLen >= nBufPos then
      begin
        Move(cBuffer[nBufPos], cBuffer[1], nBufLen-nBufPos+1);
        nRowSepPos := nRowSepPos-(nBufPos-1);
        nBufLen := nBufLen-(nBufPos-1);
        nBufPos := 1;
      end
      else
      begin
        nBufLen := 0;
        nBufPos := 1;
        nRowSepPos := 0;
      end;
      nRead := FileRead(fh, cBuffer[nBufLen+1], FILE_BUFFER);
      nBufLen := nBufLen + nRead;
    end;

    // If no more bytes in the buffer than quit.
    if nBufPos > nBufLen then
    begin
      FileEol := true;
      FileEof := true;
      Exit;
    end;

    FileBol := false;
    FileEol := false;
    if nRowSepPos < nBufPos then
    begin
      // Finding new newline position
      nRowSepPos := nBufPos;
      while (nRowSepPos <= nBufLen) and (RowSep <> Copy(cBuffer, nRowSepPos, Length(RowSep))) do
        Inc(nRowSepPos);
      FileBol := true;
    end;
    if nBufPos = nRowSepPos then
    begin
      // At the end of line
      FileEol := true;
      Inc(nBufPos, Length(RowSep));
      Exit;
    end;

    if QuotedValues and (cBuffer[nBufPos] = '"') then
    begin
      cSearchSep := '"' + FieldSep;
      Inc(nBufPos);
    end
    else
    begin
      cSearchSep := FieldSep;
    end;
    nFieldSepPos := nBufPos;
    while (nFieldSepPos < nRowSepPos) and (cSearchSep <> Copy(cBuffer, nFieldSepPos, Length(cSearchSep))) do
      Inc(nFieldSepPos);
    if QuotedValues and (nFieldSepPos = nRowSepPos) and (cBuffer[nFieldSepPos-1] = '"') then
      cField := Copy(cBuffer, nBufPos, nFieldSepPos-nBufPos-1)
    else
      cField := Copy(cBuffer, nBufPos, nFieldSepPos-nBufPos);
    if nFieldSepPos < nRowSepPos then
      nBufPos := nFieldSepPos + Length(cSearchSep)
    else
      nBufPos := nRowSepPos;

    cField := StringReplace(cField, REP_ROWSEP, RowSep, [rfReplaceAll]);
    cField := StringReplace(cField, REP_QUOTE, QUOTE, [rfReplaceAll]);

    result := true;
  end;

var
  ColumnList: TList;
  Field: TField;
  fData: integer;
begin

  if not DataSet.Active then
  begin
    MyOpen := true;
    DataSet.Open;
  end
  else
  begin
    MyOpen := false;
  end;

  DataSet.DisableControls;
  try
    fh := FileOpen(FileName, fmOpenRead or fmShareDenyWrite);
    if fh < 0 then
      raise Exception.Create('Can''t open file ' + FileName);

    try
      // Initialization
      nBufSize := FILE_BUFFER*3 div 2;
      nBufLen := 0;
      nBufPos := 1;
      nRowSepPos := 0;
      nRead := -1;
      SetLength(cBuffer, nBufSize);
      FileEof := false;
      FileEol := false;
      FileBol := false;

      ColumnList := TList.Create;
      try
        // Colecting the columns info
        repeat
          if NextField then
          begin
            Field := nil;
            for fData := 0 to DataSet.FieldCount - 1 do
            begin
              if DataSet.Fields[fData].Visible and
                (DataSet.Fields[fData].FieldKind = fkData) and
                (UpperCase(DataSet.Fields[fData].DisplayName) = UpperCase(cField)) then
              begin
                Field := DataSet.Fields[fData];
              end;
            end;
            ColumnList.Add(Field);
          end;
        until FileEol;

        // Colecting the data
        repeat
          fData := -1;
          repeat
            if NextField then
            begin
              Inc(fData);
              if FileBol then
                DataSet.Append;
              if ColumnList[fData] <> nil then
                TField(ColumnList[fData]).AsString := cField;
            end;
          until FileEol;
          if DataSet.State in dsEditModes then
            DataSet.Post;
        until FileEof;
      finally
        ColumnList.Free;
      end;
    finally
      FileClose(fh);
    end;

  finally
    DataSet.EnableControls;
    if MyOpen and DataSet.Active then
      DataSet.Close;
  end;
end;

procedure TfmExpData.btnOpenFileClick(Sender: TObject);
var
  SortBy: String;
begin
  FTableName := 'c:\temp\MyCSV.db';
  SortBy := 'Name';
  Table1.Active := false;
  Table1.DatabaseName := ExtractFilePath(FTableName);
  Table1.TableName := ExtractFileName(FTableName);
  Table1.TableType := ttParadox; // this line not important if you
                                 // included the extension with the
                                 // filename as we did above
  Table1.IndexDefs.Clear;
  Table1.FieldDefs.Clear;
  {You can assign Field & Index definitions from another Table}
  Table1.FieldDefs.Add('Name', ftString, 255);
  Table1.FieldDefs.Add('E-Mail', ftString, 255);
  Table1.FieldDefs.Add('Notes', ftString, 255);
  Table1.CreateTable;
  Table1.Open;
  ImportDataSet(Table1, 'eddie');
  Table1.Close;
  Query1.DatabaseName := Table1.DatabaseName;
  Query1.SQL.Text := 'select * from ' + Table1.TableName + ' order by '+ SortBy ;
  Query1.Open;
  ExportDataSet(Query1, 'eddie');
end;

procedure TfmExpData.btnCloseClick(Sender: TObject);
begin
  Close;
end;

end.

DFM:
object fmExpData: TfmExpData
  Left = 347
  Top = 185
  Width = 441
  Height = 334
  Caption = 'Export data'
  Color = clBtnFace
  Font.Charset = DEFAULT_CHARSET
  Font.Color = clWindowText
  Font.Height = -11
  Font.Name = 'MS Sans Serif'
  Font.Style = []
  OldCreateOrder = True
  PixelsPerInch = 96
  TextHeight = 13
  object Label1: TLabel
    Left = 8
    Top = 8
    Width = 140
    Height = 13
    Caption = '&Data fields you want exported'
    FocusControl = clbFields
  end
  object clbFields: TCheckListBox
    Left = 8
    Top = 24
    Width = 385
    Height = 137
    Columns = 4
    ItemHeight = 13
    TabOrder = 0
  end
  object btnSaveResults: TButton
    Left = 179
    Top = 234
    Width = 75
    Height = 25
    Caption = 'Save Results'
    TabOrder = 5
    OnClick = btnSaveResultsClick
  end
  object btnClose: TButton
    Left = 281
    Top = 234
    Width = 75
    Height = 25
    Cancel = True
    Caption = 'Close'
    ModalResult = 2
    TabOrder = 6
    OnClick = btnCloseClick
  end
  object btnSelectAll: TButton
    Left = 8
    Top = 168
    Width = 57
    Height = 25
    Caption = '&All fields'
    TabOrder = 1
    OnClick = btnSelectAllClick
  end
  object btnToggle: TButton
    Left = 8
    Top = 196
    Width = 57
    Height = 25
    Caption = '&Toggle'
    TabOrder = 2
    OnClick = btnToggleClick
  end
  object rgFormat: TRadioGroup
    Left = 176
    Top = 168
    Width = 217
    Height = 57
    Caption = '&Format'
    Columns = 2
    ItemIndex = 0
    Items.Strings = (
      'Text'
      'Comma sep.val.'
      'Tab sep.val.'
      'DBASE')
    TabOrder = 4
  end
  object rgScope: TRadioGroup
    Left = 68
    Top = 168
    Width = 105
    Height = 57
    Caption = '&Scope'
    ItemIndex = 0
    Items.Strings = (
      'All records'
      'Current record')
    TabOrder = 3
  end
  object btnOpenFile: TButton
    Left = 77
    Top = 234
    Width = 75
    Height = 25
    Caption = 'Open File'
    Default = True
    TabOrder = 7
    OnClick = btnOpenFileClick
  end
  object SaveDialog: TSaveDialog
    Filter =
      'Text (*.txt)|*.TXT|Comma separated values (*.csv)|*.CSV|Tab sepa' +
      'rated values (*.tsv)|*.TSV|DBASE (*.dbf)|*.DBF'
    Options = [ofOverwritePrompt, ofHideReadOnly, ofPathMustExist]
    Left = 390
    Top = 266
  end
  object OpenDialog: TOpenDialog
    Filter =
      'Comma separated values (*.csv)|*.CSV|Tab separated values (*.tsv' +
      ')|*.TSV'
    Options = [ofHideReadOnly, ofPathMustExist, ofFileMustExist]
    Left = 351
    Top = 266
  end
  object Table1: TTable
    DatabaseName = 'DBDEMOS'
    TableName = 'EddieCSV.db'
    Left = 275
    Top = 266
  end
  object Query1: TQuery
    Left = 313
    Top = 266
  end
end
0
 
LVL 26

Expert Comment

by:Eddie Shipman
ID: 20015140
Change the variables in Button1.click to reflect your CSV data.

I mean change them in btnOpenFileClick...
0
 

Author Comment

by:ST3VO
ID: 20015366
OK..I'll give it a shot...Thanks!!!
0
 

Author Comment

by:ST3VO
ID: 20015395
I get error Creating form. Invalid Stream Format for some reason :o/
0
 
LVL 26

Expert Comment

by:Eddie Shipman
ID: 20015720
What version of Delphi are you using?
0
 
LVL 26

Expert Comment

by:Eddie Shipman
ID: 20015722
You should be able to recreate the form from the DFM code above.
0
 

Author Comment

by:ST3VO
ID: 20016033
BDS 2006. I've tried replacing the unit and the DFM but it won't let me go back into form view I get that error!
0
 
LVL 26

Expert Comment

by:Eddie Shipman
ID: 20016455
I guess you'll have to recreate manually. You think you can handle that?
0
 

Author Comment

by:ST3VO
ID: 20016527
Yep...Done!
0
 

Author Comment

by:ST3VO
ID: 20016563
Opps...No compiling!

OK Guys....I'm going to stick with the AdvDBGrid as it's nearly done and working!

Thanks you all for your efforts and patience :o)

0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Objective: - This article will help user in how to convert their numeric value become words. How to use 1. You can copy this code in your Unit as function 2. than you can perform your function by type this code The Code   (CODE) The Im…
Introduction I have seen many questions in this Delphi topic area where queries in threads are needed or suggested. I know bumped into a similar need. This article will address some of the concepts when dealing with a multithreaded delphi database…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
Screencast - Getting to Know the Pipeline
Suggested Courses

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question