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

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 :@)
seancaddellAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mocartsCommented:
hi, Sean :)
did you tried to use TBatchMove? (see Delphi help)
0
seancaddellAuthor Commented:
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
mocartsCommented:
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
CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

kretzschmarCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
seancaddellAuthor Commented:



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
kretzschmarCommented:
>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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Delphi

From novice to tech pro — start learning today.