Darryl Collins
Darryl.Collins at coles.com.au
Thu Jul 16 19:35:35 CDT 2009
Yes, I would use a byte array. A bit fiddly to set up, but very fast and reliable once you get it working. Sure it reads line by line, but that is a good thing IMHO as you can verify each field as it is read and before it is uploaded anywhere. You can also perform calc and summaries within the array itself if you only want to load a summary set of the data. regard Darryl -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Gustav Brock Sent: Friday, 17 July 2009 7:17 AM To: accessd at databaseadvisors.com Subject: Re: [AccessD] TransferText wierdness Hi John I read the file line by line. A line is read into an array of fields which I loop through applying all validation and formatting while building a record which I finally add to (or update) the recordset with DAO. This gives you 100% control, you can easily skip lines, and it runs at an incredible speed. Credit goes to Neal Kling (not with us any more) who didn't invent this technique but pointed me and several others in that direction. Good Bye TransferText and ImportSpecs. /gustav >>> jwcolby at colbyconsulting.com 16-07-2009 17:06 >>> >For reasons like this, I never use the TransferText method. Gustav, What would you use? John W. Colby www.ColbyConsulting.com Gustav Brock wrote: > Hi John > > Yes, that is "fun". For reasons like this, I never use the TransferText method. > However, could it be that Name is a reserved word? What happens if you use Drug_Name instead of Drug Name? > > /gustav > > >>>> jwcolby at colbyconsulting.com 16-07-2009 16:26:27 >>> > You will love this one. > > Place the following text into a text file and run a transfer text on it: > > R4inc quarter,20083,,,,,, > NDC,Drug Name,Rebate Amt Due,#Rx,State Paid,Quantity,Formula,Unit Price > > DoCmd.TransferText acImportDelim , , "RawFormat", "YourPath\YourName.csv" > > the following is what I get: > > PK F1 F2 F3 F4 F5 F6 F7 F8 F9 F10 F11 F12 > 26361 R4inc quarter 20083 > 26362 NDC Rebate Amt Due #Rx State Paid Quantity Formula Unit Price > Notice that the "Drug Name" data item in the second row dropped out. > > Now, try this one: > > R4inc quarter,20083,,,,,, > NDC,Drug Name,Rebate Amt Due,#Rx,State Paid,Quantity,Formula,Unit Price > 597001314,COMBIVENT AER,0,5,513.01,73.5,2: 7.66735 - 3.1734 - 4.6244,0 > > > > The following is what I get. > > F1 F2 F3 F4 F5 F6 F7 F8 F9 F10 F11 F12 > R4inc quarter 20083 > NDC Drug Name Formula > 597001314 COMBIVENT AER 0 5 513.01 73.5 2: 7.66735 - 3.1734 - 4.6244 0 > Notice the dropping of TONS of data items in the "field names" row. > > But hey, why fix the bugs in Access when there are pretty toolbars that need designing eh? > > 8( > -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com ______________________________________________________________________ This email and any attachments may contain privileged and confidential information and are intended for the named addressee only. If you have received this e-mail in error, please notify the sender and delete this e-mail immediately. Any confidentiality, privilege or copyright is not waived or lost because this e-mail has been sent to you in error. It is your responsibility to check this e-mail and any attachments for viruses. No warranty is made that this material is free from computer virus or any other defect or error. Any loss/damage incurred by using this material is not the sender's responsibility. The sender's entire liability will be limited to resupplying the material. ______________________________________________________________________