Stuart McLachlan
stuart at lexacorp.com.pg
Thu Jul 16 20:24:24 CDT 2009
I just read a line at a time and use Split() to convert the data into a string array. Very easy to set up. ... 'Get a record Line Input #ff, strTemp strFields = Split(strtemp,chr$(9)) ... ' do any validation such as stripping currency symbols and commas ' from Amount fields etc ... ... 'add to table rs.addnew rs(0) = strtemp(0) 'add a text or date field rs(1) = val(strTemp(1)) 'add a numeric field .... On 17 Jul 2009 at 10:35, Darryl Collins wrote: > 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. > ______________________________________________________________________ > > -- > AccessD mailing list > AccessD at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/accessd > Website: http://www.databaseadvisors.com