 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Importing / Linking / August 2004how to import fast
I am reading a huge text file to transfer it in access in five tables I read a line, parse it then i create the record with a recordset (no parameter so i suppose it is a dbopentable recordset) command like rst.addnew It is slow. Is there a faster way? like a sql statement or any other type of connection I write in a regular access mdb. I cannot import with a transfertext since the file is a printer dump. Hi PF, It depends on the contents of the file. As you say "printer dump" I assume it's got page headers and/or footers, column headers, blank lines, and formfeeds. If the actual data lines are all in the same format, it's usually possible to filter out all the non-data lines at the textfile stage (most quickly done with Perl, awk or whatever), leaving a fixed-width textfile which TransferText can handle. On the other hand if the textfile contains groupings that you have to preserve (e.g. if it's a printout of invoices or orders, each with several header lines, a variable number of detail lines, and footer lines for tax and totals) there's no escape from reading the file line by line and parsing each line. In this situation it's sometimes worth using textfile tools to parse the file and re-assemble it into two or more simple delimited files ready for Access to import, but if you've already written working VBA code probably the best thing to do is start it running and head for the pub. >I am reading a huge text file to transfer >it in access in five tables [quoted text clipped - 11 lines] >I cannot import with a transfertext since the file >is a printer dump. -- John Nurick [Microsoft Access MVP] Please respond in the newgroup and not by email. thanks for the answer I think in my case the parsing is the best, what i want to know is when it comes to add data in access is there something faster than rst.addnew (add to a recordset) is there a way to insert say 1000 records in one insert? Before i was generating a flat text file then import the text file. Now i don't generate the textfile since i build the tables in access directly while normalising them using seek to find already transfer data etc... It is long and i would like improving the speed. regards pf > Hi PF, > [quoted text clipped - 35 lines] > > Please respond in the newgroup and not by email. AFAIK the only way you can insert a bunch of records into a table in a single operation is with an append query - which requires you to have the data in tables already. An alternative to creating a record with rst.AddNew and then setting the values of each field is to build and execute a single-record append query INSERT INTO target [(field1[, field2[, ...]])] VALUES (value1[, value2[, ...]) for each record. I don't know whether this would be faster than recordset operations. >thanks for the answer > [quoted text clipped - 55 lines] >> >> Please respond in the newgroup and not by email. -- John Nurick [Microsoft Access MVP] Please respond in the newgroup and not by email. I don't really see any way to improve the speed using DAO. The dbOpenTable is the fastest of the recordset types and using the SEEK is the fastest method to access the data. It sounds like you are checking for the existance of the data first (which is a GOOD thing) before importing it, so that has to take some time too. If you are not using the WITH operator: With rs .addnew (etc) that might speed things up a bit. Also, if the tables are heavily indexed, you might experiment with removing them (aside from the primary key and whatever you are SEEKing on), import the data, then re-build the indexes. That is sometimes faster. Lastly, I've heard claims that ADO is faster, but I cannot verify that. Anybody else know?  Signature--Roger Carlson www.rogersaccesslibrary.com Reply to: Roger dot Carlson at Spectrum-Health dot Org > thanks for the answer > [quoted text clipped - 55 lines] > > > > Please respond in the newgroup and not by email. i'll try the with operator, good idea thanks > I don't really see any way to improve the speed using DAO. The dbOpenTable > is the fastest of the recordset types and using the SEEK is the fastest [quoted text clipped - 76 lines] > > > > > > Please respond in the newgroup and not by email. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.