?
Solved

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

Posted on 2003-11-19
6
Medium Priority
?
891 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Technology Partners: 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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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…
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…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses
Course of the Month10 days, 17 hours left to enroll

770 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