Delimited Text to 10 seperate columns (1 Viewer)

M

memaxt

Registered User.
Local time
Today, 13:06
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
 
pr2-eugin

pr2-eugin

Super Moderator
Local time
Today, 21:06
Joined
Nov 30, 2011
Messages
8,494
Check for Split Function. You might be needing to write your own code, and it could be very slow on a big data set.
 
pr2-eugin

pr2-eugin

Super Moderator
Local time
Today, 21:06
Joined
Nov 30, 2011
Messages
8,494
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
 
bob fitz

bob fitz

AWF VIP
Local time
Today, 21:06
Joined
May 23, 2011
Messages
4,726
pr2-eugin said:
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
Click to expand...
That's the benefit of a degree. :D I would have to do the research but didn't bother because of the cross-post.
 
V

vbaInet

AWF VIP
Local time
Today, 21:06
Joined
Jan 22, 2010
Messages
26,374
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.
 
bob fitz

bob fitz

AWF VIP
Local time
Today, 21:06
Joined
May 23, 2011
Messages
4,726
vbaInet said:
Since one other important piece of information hasn't yet been mentioned in either posts, am I allowed to add the following Bob?
Click to expand...
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.
 
V

vbaInet

AWF VIP
Local time
Today, 21:06
Joined
Jan 22, 2010
Messages
26,374
bob fitz said:
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.
Click to expand...
I was only joking! :)
 
pr2-eugin

pr2-eugin

Super Moderator
Local time
Today, 21:06
Joined
Nov 30, 2011
Messages
8,494
bob fitz said:
That's the benefit of a degree. :D
Click to expand...
Ha Ha ! Come on Bob, you would have got that if you did not find the cross post.
vbaInet said:
I was only joking! :)
Click to expand...
I don't buy it. Lol. JK
 
M

memaxt

Registered User.
Local time
Today, 13:06
Joined
Mar 12, 2013
Messages
62
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?
 
V

vbaInet

AWF VIP
Local time
Today, 21:06
Joined
Jan 22, 2010
Messages
26,374
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.
 
M

memaxt

Registered User.
Local time
Today, 13:06
Joined
Mar 12, 2013
Messages
62
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?


vbaInet said:
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.
Click to expand...
 
V

vbaInet

AWF VIP
Local time
Today, 21:06
Joined
Jan 22, 2010
Messages
26,374
I thought you wanted to save the parts in a table?
 
M

memaxt

Registered User.
Local time
Today, 13:06
Joined
Mar 12, 2013
Messages
62
vbaInet said:
I thought you wanted to save the parts in a table?
Click to expand...

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
 
M

memaxt

Registered User.
Local time
Today, 13:06
Joined
Mar 12, 2013
Messages
62
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?
 
V

vbaInet

AWF VIP
Local time
Today, 21:06
Joined
Jan 22, 2010
Messages
26,374
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.
 
V

vbaInet

AWF VIP
Local time
Today, 21:06
Joined
Jan 22, 2010
Messages
26,374
... and when you find the event, you can use WithEvents to handle it in Access if you wish.
 
You must log in or register to reply here.

Similar threads

C
JSON Data Types when importing to Access
2
Replies
25
Views
720
Edgar_
E
S
Text box in report to count field in a query/table.
Replies
15
Views
758
Skeu-Skeu
S
T
PG Admin 4 how to set pipe delimited export
Replies
6
Views
1,147
Minty
Minty
S
VBA Code for Bold Text & Hyperlink
Replies
19
Views
1,313
cheekybuddha
cheekybuddha
R
Solved Importing and calculating the data
Replies
12
Views
607
Robrobby
R

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom