Solved

trying to import data from csv and insert it into Access Table using ADO

Posted on 2003-11-19
6
885 Views
Last Modified: 2010-04-05
Hi experts again.

I am very new to the database game and need some assistance.

Thanks to kretzschmar I know have my csv file loaded into my Ttable component.  My problem is now transferring this data into my access database table.  I am using ADO to connect to my access database and can perform queries etc no problem.  But I can't work out how to transfer the data from my TTable to my Access Table.

350 points for this one as I suspect it is quite straight forward, but I need it urgently.

Thanks

Sean :@)
0
Comment
Question by:seancaddell
  • 2
  • 2
  • 2
6 Comments
 
LVL 9

Expert Comment

by:mocarts
ID: 9778198
hi, Sean :)
did you tried to use TBatchMove? (see Delphi help)
0
 

Author Comment

by:seancaddell
ID: 9778392
I tried it but I dont think it is applicable in this case.  When I select the destination in TBatchMove component it does not list my ADO connections
0
 
LVL 9

Assisted Solution

by:mocarts
mocarts earned 175 total points
ID: 9778583
oh, yes - TBatchMove accepts only TTable descendants as destination..
so you need to scroll through your csv TTable and insert records manually (or you can write more complex code with mappings if there is more than one destination table in your Access database for your csv table)
example (written from head - so there can be compile errors etc.):

var
  ra: integer; // reccords affected
begin
  Table1.First;
  while not Table1.eof do begin
    ADOConnection1.Execute('INSERT INTO ACCESSTABLE1 (Field1, Field2) VALUES (' + Table1.FieldByName('Field1').AsString +','+ Table1.FieldByName('Field2').AsString+')', ra);
   if ra = 0 then
    raise Exception.Create('Insert into table ACCESSTABLE1 failed!');
    ADOConnection1.Execute('INSERT INTO ACCESSTABLE2 (Field1, Field2) VALUES (' + Table1.FieldByName('Field3').AsString +','+ Table1.FieldByName('Field4').AsString+')', ra);
   if ra = 0 then
    raise Exception.Create('Insert into table ACCESSTABLE2 failed!');
   Table1.Next;
  end;  // end of loop
end;

wbr, mo.
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 27

Accepted Solution

by:
kretzschmar earned 175 total points
ID: 9778832
assuming the structures are the same on both

var i : integer;
...
table1.first;
while not table1.eof do
begin
   ADOTable1.Append;
   for i := 0 to table1.fieldcount-1 do
     ADotable1.Fields[i].Value := table1.Fields[i].Value;
   ADOTable1.Post;
   table1.next;
end;

just from head -> not tested -> typos possible

meikl ;-)
0
 

Author Comment

by:seancaddell
ID: 9779270



kretzschmar's program is tidier and is working nicely, I am going to share the points on this on equally.  One more question.

Can I batch the appending in any way to speed things up?
0
 
LVL 27

Expert Comment

by:kretzschmar
ID: 9779792
>Can I batch the appending in any way to speed things up?

thinking about it (ado has a like batchmode, but i've never used it)
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Introduction The parallel port is a very commonly known port, it was widely used to connect a printer to the PC, if you look at the back of your computer, for those who don't have newer computers, there will be a port with 25 pins and a small print…
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…
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…

867 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now