Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2003-11-19
6
Medium Priority
?
895 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 700 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
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!

 
LVL 27

Accepted Solution

by:
kretzschmar earned 700 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

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

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…
Have you ever had your Delphi form/application just hanging while waiting for data to load? This is the article to read if you want to learn some things about adding threads for data loading in the background. First, I'll setup a general applica…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…
Suggested Courses

972 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