424,054 Members | 1,078 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,054 IT Pros & Developers. It's quick & easy.

Extract string into column data

P: 76
Hi there!

I have a txt file with a string of about 1,000 ip addresses, delimited by comma.

I need to read the file into Access (just to have it displayed in a qry for view.

I have the .txt file linked in Access, but how to get each entry (delimited by comma) into a new line item?
Nov 1 '11 #1

✓ answered by patjones

Create a table called tblIPAddresses, with one column named fldIPAddress. Then paste the code below into a VBA global module (remember to compile), replacing the bolded text with the full path to your text file. Open the Immediate Window (View > Immediate Window) and execute the routine by typing ReadTextFile and hitting ENTER.

Expand|Select|Wrap|Line Numbers
  1. Public Sub ReadTextFile()
  2.  
  3. Dim iCount As Integer
  4. Dim strInput As String
  5. Dim arr() As String
  6.  
  7. 'Open the text file containing IP addresses
  8.  Open "C:\Documents and Settings\delimited_strings.txt" For Input As #1
  9.  
  10. 'Initialize counter and array that will hold IP addresses
  11.  iCount = 1
  12.  ReDim arr(0 To 1)
  13.  
  14. 'Loop through the elements in the text file, assigning each one to a spot in the array
  15.  Do While Not EOF(1)
  16.     Input #1, strInput
  17.     arr(iCount) = strInput
  18.     iCount = iCount + 1
  19.     ReDim Preserve arr(iCount)
  20.  Loop
  21.  
  22. 'Close the text file
  23.  Close #1
  24.  
  25. 'Delete any previous entries from the table that will hold the IP addresses
  26.  DoCmd.RunSQL "DELETE FROM tblIPAddresses;"
  27.  
  28. 'Loop through the array, inserting the elements into the table
  29.  For iCount = 1 To UBound(arr)
  30.     DoCmd.RunSQL "INSERT INTO tblIPAddresses(fldIPAddress) VALUES ('" & arr(iCount) & "');"
  31.  Next iCount
  32.  
  33. End Sub
  34.  

This will dump your parsed IP addresses into the table. I have another solution in mind involving a SQL statement which I'll put together when 5:00 PM is not fast approaching.

Pat

Share this Question
Share on Google+
6 Replies


patjones
Expert 100+
P: 931
It's just one long string without each new entry on a separate line? In order to do such an import correctly Access requires each record to be on a new line, so we'll need to determine another way to accomplish this.
Nov 1 '11 #2

patjones
Expert 100+
P: 931
Create a table called tblIPAddresses, with one column named fldIPAddress. Then paste the code below into a VBA global module (remember to compile), replacing the bolded text with the full path to your text file. Open the Immediate Window (View > Immediate Window) and execute the routine by typing ReadTextFile and hitting ENTER.

Expand|Select|Wrap|Line Numbers
  1. Public Sub ReadTextFile()
  2.  
  3. Dim iCount As Integer
  4. Dim strInput As String
  5. Dim arr() As String
  6.  
  7. 'Open the text file containing IP addresses
  8.  Open "C:\Documents and Settings\delimited_strings.txt" For Input As #1
  9.  
  10. 'Initialize counter and array that will hold IP addresses
  11.  iCount = 1
  12.  ReDim arr(0 To 1)
  13.  
  14. 'Loop through the elements in the text file, assigning each one to a spot in the array
  15.  Do While Not EOF(1)
  16.     Input #1, strInput
  17.     arr(iCount) = strInput
  18.     iCount = iCount + 1
  19.     ReDim Preserve arr(iCount)
  20.  Loop
  21.  
  22. 'Close the text file
  23.  Close #1
  24.  
  25. 'Delete any previous entries from the table that will hold the IP addresses
  26.  DoCmd.RunSQL "DELETE FROM tblIPAddresses;"
  27.  
  28. 'Loop through the array, inserting the elements into the table
  29.  For iCount = 1 To UBound(arr)
  30.     DoCmd.RunSQL "INSERT INTO tblIPAddresses(fldIPAddress) VALUES ('" & arr(iCount) & "');"
  31.  Next iCount
  32.  
  33. End Sub
  34.  

This will dump your parsed IP addresses into the table. I have another solution in mind involving a SQL statement which I'll put together when 5:00 PM is not fast approaching.

Pat
Nov 1 '11 #3

P: 76
Looks like it will work, but getting a continual yes/no button at line
Expand|Select|Wrap|Line Numbers
  1. DoCmd.RunSQL "INSERT INTO tblIPAddresses(fldIPAddress) VALUES ('" & arr(iCount) & "');"
Nov 1 '11 #4

NeoPa
Expert Mod 15k+
P: 31,170
Or you could read the whole line into a string variable then assign it to an array in one command using Split(StringVar, ","). The array could then still be processed as by the code in the latter portion of the code in Pat's post (#3).
Nov 2 '11 #5

patjones
Expert 100+
P: 931
Hi,

In Access, unless you specify otherwise, a dialog box will appear whenever you run an action query (action queries are anything that makes a change to data in a table - INSERT, UPDATE, DELETE). You can turn this feature off by going to the Microsoft button at the top left corner of the window > Access Options > Advanced and then unchecking Confirm Record changes and Action queries.

I think this should do it for you.

Pat
Nov 2 '11 #6

NeoPa
Expert Mod 15k+
P: 31,170
There are two standard ways to run SQL Action Queries from VBA :
Expand|Select|Wrap|Line Numbers
  1. Call DoCmd.RunSQL("SQL String")
  2. Call CurrentDb.Execute("SQL String")
The first will send warnings to the operator, but only if the SetWarnings value is true (Set using Call DoCmd.SetWarnings(True/False)). The latter doesn't ever trigger such warnings. Thus, it is never necessary to set this option using the Access interface (Even if the former method is used the SetWarnings value can be set beforehand and afterwards to avoid this).
Nov 2 '11 #7

Post your reply

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