My Assistant
Site Message
(Message will auto close in 2 seconds)
  |
 Import Delimited Text File Into Table
|
|
 Feb 20 2012, 10:46 AM
Post
#1
|
|
|
New Member Posts: 10  |
I've searched and expermited until I'm ready to shot this computer.
I need to import a comma delimited text file that has an extension of b (ie, one of the file names is 35246a.b). The file names are generated from an Oracle db and renaming those files is not an option. I just want the data to be added to the same table each time. I'd rather this was all done through VBA using SQL code but at this point I really don't care if the Easter Bunny squats on the computer and makes it happen!! I've tried using transfertext but I keep getting a read-only error that has something to do with the extension. The below code was the closest that I have been able to get anything to work and it messes up the data (it appears that the code is not recognizing that the file is delimited and seperated by a comma. Disclaimer: The below code is not really mine but code that I got from another forum, so the credit goes elsewhere. CODE Public Sub oldImport() Dim strSQL As String Dim rs1 As DAO.Recordset Dim DB As DAO.Database Set DB = CurrentDb() 'Set rs = db.OpenRecordset(strSQL) Open "D:\PH1label\35246a.b" For Input As #1 MsgBox "File is open..." ' Open the table to insert the text file into strSQL = "Select * from tblTPSsubjects" MsgBox "Fields: " + strSQL 'rs1.Open strSQL, cncurrent, adOpenDynamic, adLockOptimistic Set rs1 = DB.OpenRecordset(strSQL) MsgBox "Database table is open..." Do While Not EOF(1) ' Read a line of data. Line Input #1, LineData rs1.AddNew rs1.Fields("LawsonGroup") = Left(LineData, 4) rs1.Fields("SID") = Mid(LineData, 5, 12) rs1.Fields("Initials") = Mid(LineData, 17, 9) rs1.Fields("Color") = Mid(LineData, 26, 7) rs1.Fields("NameF") = Mid(LineData, 33, 4) rs1.Fields("Num1W") = Mid(LineData, 37, 20) rs1.Fields("DOB") = Mid(LineData, 57, 2) rs1.Fields("Age") = Mid(LineData, 59, 7) rs1.Fields("Rando") = Mid(LineData, 66, 11) MsgBox "Data: " + LineData rs1.Update rs1.MoveNext MsgBox "Import Complete..." Loop ' Close the data file. Close #1 rs1.Close End Sub Thanks a lot, Chris This post has been edited by strive4peace: Feb 21 2012, 12:04 AM
Reason for edit: added code tags and indents
|
 |
 
|
|
Feb 20 2012, 10:49 AM
Post
#2
|
|
|
UtterAccess VIP Posts: 8,191 From: CT |
(IMG:style_emoticons/default/welcome2UA.gif)
Chris, why can't you rename the file once you get it? bob |
|
|
|
|
Feb 20 2012, 10:57 AM
Post
#3
|
|
|
New Member Posts: 10 |
These files are really used for printing labels and barcodes on Zebra printers. They just happen to have the data needed for this db. These files are generated by the users (there are about 50 different users on this specific task at any given time with lots of turnover of staff) on an as needed basis (it could be never re-generated or re-generated several times a day, there is no way to forcast that).
I guess it could be saved locally, renamed and then deleted after import. The problem is that this industry if very, very highly regulated (pharma research data) and pulling the data from a copy of the Oracle generated file is ruled to be "non validated" data. |
|
|
|
|
Feb 20 2012, 11:05 AM
Post
#4
|
|
|
UtterAccess VIP Posts: 8,191 From: CT |
what version of access are you using?
have you tried doing the import using the wizard and not code ? |
|
|
|
|
Feb 20 2012, 11:12 AM
Post
#5
|
|
|
New Member Posts: 10 |
Access 2010
I've tried using the wizard and it worked but there are over 100 files (and growing by 10-30 each month as new studies are added) and you never know which one the user will need. I tried saving the import and using it with the transfertext code but i got the read-only error. Thanks for the help! |
|
|
|
|
Feb 20 2012, 11:18 AM
Post
#6
|
|
|
UtterAccess VIP Posts: 8,191 From: CT |
unfortunately, i do not have 2010. In addition, I don't have any other suggestion.
Hang in there, others will chime in I am sure. |
|
|
|
|
Feb 20 2012, 10:57 PM
Post
#7
|
|
|
UtterAccess Guru Posts: 775 |
QUOTE (chrish20202 @ Feb 20 2012, 05:12 PM)  Access 2010 I've tried using the wizard and it worked but there are over 100 files (and growing by 10-30 each month as new studies are added) and you never know which one the user will need. I tried saving the import and using it with the transfertext code but i got the read-only error. Thanks for the help! If all the files are in the same directory, you can use DIR to loop the directory's file contents and import each one inside a loop. If the files all have the same structure, and the user should be able to choose one, then you could use the FileOpenAPI (first article in the Modules section of www.mvps.org/access (AccessWeb). then you'd just do a TransferText or whatever inside your loop. |
|
|
|
|
Feb 21 2012, 08:20 AM
Post
#8
|
|
|
UtterAccess VIP Posts: 1,956 From: UK |
i think i saw a similar thing in A2010
a csv import that used to work no longer works with a file extension other than .csv why not a) temporarily change the file name to .csv b) import it c) rename it back again. the name command will rename a file the reason why your sample code will not work is that the code is assuming a fixed layout for the the fields, whixch is just what a csv is not. |
|
|
|
 |
|
Jump To Forum:
|
Go to Top · Lo-Fi Version | Time is now: 19th June 2013 - 09:33 AM |