Extract string into column data

[artemetis]

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?

[patjones]

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.

[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.

Public Sub ReadTextFile()

Dim iCount As Integer
Dim strInput As String
Dim arr() As String

'Open the text file containing IP addresses
 Open "[B]C:\Documents and Settings\delimited_strings.txt[/B]" For Input As #1

'Initialize counter and array that will hold IP addresses
 iCount = 1
 ReDim arr(0 To 1)

'Loop through the elements in the text file, assigning each one to a spot in the array
 Do While Not EOF(1)
    Input #1, strInput
    arr(iCount) = strInput
    iCount = iCount + 1
    ReDim Preserve arr(iCount)
 Loop

'Close the text file
 Close #1

'Delete any previous entries from the table that will hold the IP addresses
 DoCmd.RunSQL "DELETE FROM tblIPAddresses;"

'Loop through the array, inserting the elements into the table
 For iCount = 1 To UBound(arr)
    DoCmd.RunSQL "INSERT INTO tblIPAddresses(fldIPAddress) VALUES ('" & arr(iCount) & "');"
 Next iCount

End Sub

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

[artemetis]

Looks like it will work, but getting a continual yes/no button at line

DoCmd.RunSQL "INSERT INTO tblIPAddresses(fldIPAddress) VALUES ('" & arr(iCount) & "');"

[NeoPa]

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).

[patjones]

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

[NeoPa]

There are two standard ways to run SQL Action Queries from VBA :

Call DoCmd.RunSQL("SQL String")
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.SetWarnin gs(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).