Seperating string into table rows

[usurper4]

I know this seems stupid...but bear with me. We run reports from a database that come out as a long string of names, separated by a semicolon and a space (ex: bob davis; dave davis).

These strings can be upwards of 2000 names long.

Now, the stupid part: how can I use Access to pull this list into a table that has just one field (Name) and populate all 2000-whatever rows?

I can't figure this out for the life of me, and I know it's something simple.

 

[SydneyGeek]

Here's a manual-ish method.

Assuming that the huge string is in a text file, open that file in Word.
Replace semi-colon space with ^p (return)
Place the field name at the top of the list
Close and save the file, still in .txt format

You should now have a column of names that can be imported into Access.

Denis

 

[usurper4]

Okay, that's a little bit of a "duh" moment. Now, I'll just automate Access to do that for me in Notepad and I should be good to go!

 

[Norie]

Why not just use the Split function with the ; as a delimiter?

 

[usurper4]

Okay, so how would I use this in this case? Do you have any VBA that just pulls a string from a text file and splits it? That's something I haven't been able to do.

 

[Norie]

usurper4

Sorry I'm confused.

In the first post you mentioned a report, now you mention a text file.

Which is it?

 

[usurper4]

My bad. The report is exported as a txt file that I'm trying to use as data.

 

[Norie]

Well you can easily read a text file using VBA.

For example.

Sub test()
Dim strInput
Dim arrNames
Dim FF
Dim I
    FF = FreeFile()
    
    Open "C:\SplitTest.txt" For Input As FF
        Line Input #FF, strInput
    Close #FF
    
    arrNames = Split(strInput, ";")
    
    For I = LBound(arrNames) To UBound(arrNames)
        MsgBox arrNames(I)
    Next I
End Sub

 

[usurper4]

EXACTLY what I needed. Thx Norie!