Solved

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

Posted on 2003-11-19
6
887 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
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering 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

In this tutorial I will show you how to use the Windows Speech API in Delphi. I will only cover basic functions such as text to speech and controlling the speed of the speech. SAPI Installation First you need to install the SAPI type library, th…
Hello everybody This Article will show you how to validate number with TEdit control, What's the TEdit control? TEdit is a standard Windows edit control on a form, it allows to user to write, read and copy/paste single line of text. Usua…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

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