Delimited Text to 10 seperate columns

memaxt

Registered User.
Local time
Yesterday, 20:51
Joined
Mar 12, 2013
Messages
62
Hi everyone,

I'm trying to build a query that can parse Delimited text to columns,

for example I have the following:

ID,Name,Tel,Fax,Email,Directorate,DOB,AOCD,Reg,CD

I would like to convert the above in 10 seperate columnns within a query?

Is this possible? I know you can import delimited text to columns but that is not what i'm after for other reasons.

Many thanks
Max
 
Check for Split Function. You might be needing to write your own code, and it could be very slow on a big data set.
 
Yes Bob, I did check, but I replied just at the same time you posted the cross post. Thankfully I did not do any research. :D
 
Yes Bob, I did check, but I replied just at the same time you posted the cross post. Thankfully I did not do any research. :D
That's the benefit of a degree. :D I would have to do the research but didn't bother because of the cross-post.
 
Since one other important piece of information hasn't yet been mentioned in either posts, am I allowed to add the following Bob? :)

You only one to split once and return the corresponding values when called. You'll need a function for this.
 
Since one other important piece of information hasn't yet been mentioned in either posts, am I allowed to add the following Bob?
But of course you can. As I'm sure you are aware, everyone can say what they think as long as it is not offensive.:)
The comment I aimed at Paul was made because of the discussion we had at our meeting yesterday.
 
But of course you can. As I'm sure you are aware, everyone can say what they think as long as it is not offensive.:)
The comment I aimed at Paul was made because of the discussion we had at our meeting yesterday.
I was only joking! :)
 
That's the benefit of a degree. :D
Ha Ha ! Come on Bob, you would have got that if you did not find the cross post.
wink.gif
I was only joking! :)
I don't buy it. Lol. JK
 
Just wanted to say many thanks for all your replies! you are all very helpful!!! I love this forum!

But

I'm still struggling with split function, i'm very poor when its comes to using such functions.

If possible could you give me more pointers or even a snippet of code relating to that delimited i've used to build on?
 
More than a snippet:
Code:
Private x                As Byte
Private varSplit         As Variant
Private varParts(1 To 9) As Variant
Private Const STR_DELIM  As String = ","

Public Function GetParts(PartNo As Byte, _
                         Optional TheVal As Variant) As Variant
    
    If Not IsMissing(TheVal) Then
        If Len(TheVal & vbNullString) = 0 Then Exit Function
    End If
    
    Select Case PartNo
        Case 0
            varSplit = Split(TheVal, STR_DELIM)
            GetParts = varSplit(0)
            
            ' Save the other parts
            If UBound(varSplit) > 0 Then
                For x = 1 To UBound(varSplit)
                    varParts(x) = varSplit(x)
                Next
            End If
        
        Case 1 To 9
            GetParts = varParts(PartNo)
            varParts(PartNo) = Null
    End Select
End Function
To call:
Code:
GetParts(0, "ID,Name,Tel,Fax,Email,Directorate,DOB,AOCD,Reg,CD")
GetParts(1)
GetParts(2)
.
.
.
GetParts(9)
... the base index is 0, not 1.

You'll need some error handling in there too.
 
You are amazing!! Big thank you!

So rather than doing this in a query? I am adding this code to a form? then calling it?


More than a snippet:
Code:
Private x                As Byte
Private varSplit         As Variant
Private varParts(1 To 9) As Variant
Private Const STR_DELIM  As String = ","

Public Function GetParts(PartNo As Byte, _
                         Optional TheVal As Variant) As Variant
    
    If Not IsMissing(TheVal) Then
        If Len(TheVal & vbNullString) = 0 Then Exit Function
    End If
    
    Select Case PartNo
        Case 0
            varSplit = Split(TheVal, STR_DELIM)
            GetParts = varSplit(0)
            
            ' Save the other parts
            If UBound(varSplit) > 0 Then
                For x = 1 To UBound(varSplit)
                    varParts(x) = varSplit(x)
                Next
            End If
        
        Case 1 To 9
            GetParts = varParts(PartNo)
            varParts(PartNo) = Null
    End Select
End Function
To call:
Code:
GetParts(0, "ID,Name,Tel,Fax,Email,Directorate,DOB,AOCD,Reg,CD")
GetParts(1)
GetParts(2)
.
.
.
GetParts(9)
... the base index is 0, not 1.

You'll need some error handling in there too.
 
I thought you wanted to save the parts in a table?

Apologies you are correct.. So by running this it will create a table and populate rows as and when data comes in... Complete noob in this bit of coding
 
Basically I've linked my outlook to access on one of my machines, the email contents is delimited.. So the ideal scenerio is... Email comes in... Delimited goes to columns then updates my share point table... I will set the timer to requery every 30 seconds to check for new emails

Does this sound logical?
 
Sounds ok but I think it's too heavy handed. There must be an event handler in Outlook that handles this. So instead of doing this in Access do it in Outlook and let the event handler "listen" for new emails.
 
... and when you find the event, you can use WithEvents to handle it in Access if you wish.
 

Users who are viewing this thread

Back
Top Bottom