By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
428,659 Members | 940 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 428,659 IT Pros & Developers. It's quick & easy.

How to import text file to access and assign value to table?

P: 14
I have a text file that I want to read and assign parts to a certain field in MS Access table

When you look at this file you will see numbers with corresponding info Ex: 120A. CMPBLLBK I want to have 120A be called “Location” and “CMPBLLBK” be populated in that location.

Basically when it is read into access I want a table that
that has fields named [TSR Number] [Type Action][TYPE OF SERVICE][NETWORK REQUIREMENTS],etc


Any help would be appreciated.


TcossDocument2 has the format in which I will be reading
TcossDocument is an actual document
Attached Files
File Type: txt TcossDocument2.txt (706 Bytes, 291 views)
File Type: txt TcossDocument.txt (1.5 KB, 366 views)
Feb 28 '11 #1

✓ answered by TheSmileyCoder

This is an example of an approach, not complete code. I suggest you try working with it, and ask when you get stuck, or if there are parts of the example you dont understand.
First open recordset to our table:
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4.  
  5.  
  6. Public Function readFile(strPath As String) As String
  7.     'Create a filesystemobject
  8.         Dim fso As Object
  9.         Set fso = CreateObject("scripting.filesystemobject")
  10.  
  11.     'Open file for reading
  12.         readFile = fso.OpenTextFile(strPath, 1).ReadAll
  13.  
  14.     'Cleanup
  15.         Set fso = Nothing
  16.  
  17. End Function
  18.  
  19.  
  20. Public Sub InputFile(strPath As String)
  21.     Dim strContent As String
  22.     strContent = readFile(strPath)
  23.  
  24.     Dim rsDao As DAO.Recordset
  25.     Set rsDao = CurrentDb.OpenRecordset("SELECT * FROM tbl_Example", dbOpenDynaset)
  26.     rsDao.AddNew
  27.     With rsDao
  28.         ![TSR Number] = getValue(strContent, 101, 103)
  29.         ![Type Action] = getValue(strContent, 103, 104)
  30.         ![TYPE OF SERVIC] = getValue(strContent, 104, 105)
  31.         .
  32.         .
  33.         .
  34.         ![Requesting Activity's Requirement Number] = getValue(strContent, 514)
  35.     End With
  36.     rsDao.Update
  37.     Set rsDao = Nothing
  38.  
  39.  
  40. End Sub
  41.  
  42. Public Function getValue(strInput As String, lngStart As Long, Optional lngEnd As Long = 0)
  43.     'Get starting location
  44.         Dim lngPosStart As Long
  45.         Dim strStart As String
  46.         strStart = vbCrLf & lngStart & ". "
  47.         lngPosStart = InStr(1, strInput, strStart) + Len(strStart)
  48.     'Get end location (if any)
  49.         Dim lngPosEnd As Long
  50.         If lngEnd <> 0 Then
  51.             lngPosEnd = InStr(1, strInput, vbCrLf & lngEnd & ".")
  52.         Else
  53.             lngPosEnd = Len(strInput)
  54.         End If
  55.  
  56.     getValue = Mid(strInput, lngPosStart, lngPosEnd - lngPosStart)
  57.  
  58. End Function
  59.  
  60.  

Share this Question
Share on Google+
15 Replies


TheSmileyCoder
Expert Mod 100+
P: 2,321
This will open a file and read it into a textstring. You can then manipulate the string from there to get what you need.

Expand|Select|Wrap|Line Numbers
  1. Public Function readFile(strPath As String) As String
  2.     'Create a filesystemobject
  3.         Dim fso As Object
  4.         Set fso = CreateObject("scripting.filesystemobject")
  5.  
  6.     'Open file for reading
  7.         readFile = fso.OpenTextFile(strPath, ForReading).ReadAll
  8.  
  9.     'Cleanup
  10.         Set fso = Nothing
  11.  
  12. End Function
Feb 28 '11 #2

P: 14
Thank You for your quick response-Can you show or guide me how I would write the first line to create the table aspect. I can follow after that

Basically when it is read into access I want a table that
that has fields named [TSR Number] [Type Action][TYPE OF SERVICE][NETWORK REQUIREMENTS] and the corresponding information from the text in that field i.e. After reading the text [TSR Number] would have "EJ13JAN102064" populated in it
Feb 28 '11 #3

TheSmileyCoder
Expert Mod 100+
P: 2,321
Do you need to create the tables on a per file basis? What should happen if the table allready exists? Append or overwrite?
Feb 28 '11 #4

P: 14
I will receive a new file every time so it will need to append
Feb 28 '11 #5

TheSmileyCoder
Expert Mod 100+
P: 2,321
This is an example of an approach, not complete code. I suggest you try working with it, and ask when you get stuck, or if there are parts of the example you dont understand.
First open recordset to our table:
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4.  
  5.  
  6. Public Function readFile(strPath As String) As String
  7.     'Create a filesystemobject
  8.         Dim fso As Object
  9.         Set fso = CreateObject("scripting.filesystemobject")
  10.  
  11.     'Open file for reading
  12.         readFile = fso.OpenTextFile(strPath, 1).ReadAll
  13.  
  14.     'Cleanup
  15.         Set fso = Nothing
  16.  
  17. End Function
  18.  
  19.  
  20. Public Sub InputFile(strPath As String)
  21.     Dim strContent As String
  22.     strContent = readFile(strPath)
  23.  
  24.     Dim rsDao As DAO.Recordset
  25.     Set rsDao = CurrentDb.OpenRecordset("SELECT * FROM tbl_Example", dbOpenDynaset)
  26.     rsDao.AddNew
  27.     With rsDao
  28.         ![TSR Number] = getValue(strContent, 101, 103)
  29.         ![Type Action] = getValue(strContent, 103, 104)
  30.         ![TYPE OF SERVIC] = getValue(strContent, 104, 105)
  31.         .
  32.         .
  33.         .
  34.         ![Requesting Activity's Requirement Number] = getValue(strContent, 514)
  35.     End With
  36.     rsDao.Update
  37.     Set rsDao = Nothing
  38.  
  39.  
  40. End Sub
  41.  
  42. Public Function getValue(strInput As String, lngStart As Long, Optional lngEnd As Long = 0)
  43.     'Get starting location
  44.         Dim lngPosStart As Long
  45.         Dim strStart As String
  46.         strStart = vbCrLf & lngStart & ". "
  47.         lngPosStart = InStr(1, strInput, strStart) + Len(strStart)
  48.     'Get end location (if any)
  49.         Dim lngPosEnd As Long
  50.         If lngEnd <> 0 Then
  51.             lngPosEnd = InStr(1, strInput, vbCrLf & lngEnd & ".")
  52.         Else
  53.             lngPosEnd = Len(strInput)
  54.         End If
  55.  
  56.     getValue = Mid(strInput, lngPosStart, lngPosEnd - lngPosStart)
  57.  
  58. End Function
  59.  
  60.  
Feb 28 '11 #6

P: 14
Thank you very much-it is greatly appreciated. I must being doing something wrong-I pasted the code in ACCESS but I continue to get an error. Sub or Function not defined. I am just learning this aspect of access, please be patient. Should I replace strPath with location of file?
Feb 28 '11 #7

P: 14
I must being doing something wrong-I pasted the code in ACCESS but I continue to get an error. Sub or Function not defined. I am just learning this aspect of access, please be patient. Should I replace strPath with location of file?
Mar 1 '11 #8

TheSmileyCoder
Expert Mod 100+
P: 2,321
Hi Jeffrey

As I said, this is example of code, not complete "Ready-to-copy" code.
For instance the part:
Expand|Select|Wrap|Line Numbers
  1.     With rsDao 
  2.         ![TSR Number] = getValue(strContent, 101, 103) 
  3.         ![Type Action] = getValue(strContent, 103, 104) 
  4.         ![TYPE OF SERVICE] = getValue(strContent, 104, 105) 
  5.         . 
  6.         . 
  7.         . 
  8.         ![Requesting Activity's Requirement Number] = getValue(strContent, 514) 
  9.     End With 
is only partly filled out.

Yes, strPath is the full path including filename and filetype, for example "C:\ImportFiles\myFile.txt"

The code is also assuming that the table is allready created.
Mar 1 '11 #9

P: 14
I understood that it is not complete-I was trying to get it to read file-I assumed I was coding it wrong as to why I was getting compile error (the function or sub not defined) I was only working with the read portion of code
Mar 1 '11 #10

TheSmileyCoder
Expert Mod 100+
P: 2,321
The first time I posted the readFile code, i accidentally included a constant acForReading that is only "known" by access if a reference is set to the "Windows Script Host Object Model".
The second time I posted it, I had replaced the acForReading with its value 1. Maybe that is the result of the error you are getting.

The Correct way to use the readFile is:
Expand|Select|Wrap|Line Numbers
  1. Dim strFileContent as string
  2. strFileContent=ReadFile("C:\ImportFiles\myFile.txt")
Mar 1 '11 #11

P: 14
I am sorry just to understand the code should look like this
Public Function readFile(strPath As String) As String
'Create a filesystemobject

Dim fso As Object
Set fso =

CreateObject("scripting.filesystemobject")

'Open file for reading
Dim strFileContent As String
strFileContent = readFile("C:\ImportFiles\myFile.txt")



'Cleanup
Set fso = Nothing

End Function
Mar 1 '11 #12

TheSmileyCoder
Expert Mod 100+
P: 2,321
Not even close. Your mixing up the basics quite severely.
Expand|Select|Wrap|Line Numbers
  1. Public Function readFile(strPath As String) As String
  2.     'Create a filesystemobject
  3.         Dim fso As Object
  4.         Set fso = CreateObject("scripting.filesystemobject")
  5.  
  6.     'Open file for reading
  7.         readFile = fso.OpenTextFile(strPath, 1).ReadAll
  8.  
  9.     'Cleanup
  10.         Set fso = Nothing
  11.  
  12. End Function
Looking at the first line:
Expand|Select|Wrap|Line Numbers
  1. Public Function readFile(strPath As String) As String
The Public Function tells access that we want to create a function, and it should be PUBLIC. That means that you can use it from anywhere within Access.
The Readfile is the name of the function.
The (strPath As String) tells access that a string variable is requested for the function to work.
The last As String tells access that a string is to be returned when the function is used.

The
Expand|Select|Wrap|Line Numbers
  1. readFile = fso.OpenTextFile(strPath, 1).ReadAll
is where the return value is assigned.

The rest
Expand|Select|Wrap|Line Numbers
  1. 'Create a filesystemobject
  2.         Dim fso As Object
  3.         Set fso = CreateObject("scripting.filesystemobject")
  4.     'Cleanup
  5.         Set fso = Nothing
I wont start to explain, suffice to say they are needed to make the manipulation we want.

If you want to make use of VBA code, I would suggest spending a few hours on some online tutorials.
Mar 1 '11 #13

P: 14
I agree with you and I thank you very much for your assistance and patience. Be blessed
Mar 1 '11 #14

P: 14
I took your advice and researched a bit-I receive a argument is not optional error when I call this. For instance referring to code below:

Call InputFile (should there be a variable here)as I believe the procedure is looking for one. Am I still off target?




Public Sub InputFile(strPath As String)
Dim strContent As String
strPath = "C:\Users\Jeff\Desktop\TcossDocument.txt"
strContent = readFile(strPath)

Dim rsDao As DAO.Recordset
Set rsDao = CurrentDb.OpenRecordset("SELECT * FROM tblTSR", dbOpenDynaset)
rsDao.AddNew
With rsDao
![TSR Number] = getValue(strContent, 101, 103)
![Type Action] = getValue(strContent, 103, 104)
![TYPE OF SERVICE] = getValue(strContent, 104, 105)
![Requesting Activity's Requirement Number] = getValue(strContent, 514)
End With
rsDao.Update
Set rsDao = Nothing


End Sub
Mar 3 '11 #15

TheSmileyCoder
Expert Mod 100+
P: 2,321
One could write all the code necessary for reading the file and appending it to a table in 1 single procedure or function. However a basic principle of good coding is to try to make your code reusable. Therefore I made a function ReadFile, so that I can re-use it should I ever again need to read a text file.

The function takes an input argument strPath of type string, to tell the function where to find the file we want to import, and then ReadFile returns with the content of the file.

If this project was my own, I would create a button, on some form, and code the button to respond to click events, and when clicked, code access to open a filebrowser to browse to the file in question, then import it. Thats the basic starting point, then comes addition of error handling, handling when user selects a wrong file type (say a word document).
Mar 3 '11 #16

Post your reply

Sign in to post your reply or Sign up for a free account.