Gustav Brock
Gustav at cactus.dk
Thu Jul 16 16:17:04 CDT 2009
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( >