Solved

Text to Columns in Access

Posted on 2009-04-06
9
1,225 Views
Last Modified: 2012-06-27
I preface this question by clarifying that I already know how to accomplish this task in Excel, Word, TextPad etc.  I want to know if/how this can be done directly in MS Access to avoid the whole export-parse-import process.  

The "Original POST" column contains data that is grouped using dashes (-).  Some of the cells have 1 dash between two values (GATE2-001), others have 2 dashes separating 3 values (51-AB-492), and still others have no dashes and just a single value (0010)

What I need to do is to split this single column into 3 columns, RIGHT JUSTIFIED, using the dash as a delimiter.  See example below.  Is this possible in Access?  If so, what is the syntax?  (the spacing of this sample isn't great, please view the attached file for a better look)
                                           
Original POST      Resulting POST      NewCol 1            NewCol 2
GATE-001                        GATE            001
GATE-002                        GATE            002
GATE-003                        GATE            003
GATE-004                        GATE            004
GATE-005                        GATE            005
51-AB-492            51            AB            492
51-AB-493            51            AB            493
51-AB-494            51            AB            494
51-AB-495            51            AB            495
51-AB-496            51            AB            496
51-AB-497            51            AB            497
00010                                    00010
00011                                    00011
00012                                    00012
00013                                    00013
00014                                    00014
00015                                    00015
00016                                    00016

Text-to-Columns-Example.xls
0
Comment
Question by:Uniqueinc
  • 6
  • 2
9 Comments
 
LVL 65

Expert Comment

by:rockiroads
ID: 24080204
do u want this solution in sql or vba. Certainly easy in vba, can make a vba func is probably a littler easier to read

add this to a module and save it (ensure module name not the same as func name)

Public Function GetPart(ByVal sSource As String, ByVal iPos As Integer) As String

    Dim sWords() As String
   
    If InStr(1, sSource, "-") > 0 Then
        If InStrRev(sSource, "-") = InStr(1, sSource, "-") Then
            sSource = "-" & sSource
        End If
    Else
        sSource = "--" & sSource
    End If
    sWords = Split(sSource, "-")
   
    If iPos > UBound(sWords) + 1 Then
        GetPart = ""
    Else
        GetPart = sWords(iPos - 1)
    End If
End Function




Now in your query do this

select ([Original POST], GetPart([Original POST],1) AS [Resulting Post], GetPart[Original POST],2) AS NewCol1, GetPart([Original POST],3) AS NewCol3
from mytable


0
 
LVL 65

Expert Comment

by:rockiroads
ID: 24080246
To test this, create a test func
eg
Public Sub TestGetPart()
    Debug.Print "COLUMN 1", "COLUMN 2", "COLUMN 3"
    Debug.Print GetPart("51-AB-492", 1), GetPart("51-AB-492", 2), GetPart("51-AB-492", 3)
    Debug.Print GetPart("GATE-001 ", 1), GetPart("GATE-001 ", 2), GetPart("GATE-001", 3)
    Debug.Print GetPart("00012", 1), GetPart("00012", 2), GetPart("00012", 3)
End Sub


and run this (click anywhere in code and hit F5) then check results in immediate window. It looks like this

COLUMN 1      COLUMN 2      COLUMN 3
51            AB            492
              GATE          001
                            00012
0
 

Author Comment

by:Uniqueinc
ID: 24080505
I'd like to do it in an Access query, if possible.  

Along this line, I've copied your sample into a new Access query, and it gives me a syntax error (Missing operator) around the 'AS [Resulting Post]' section.  Any ideas?
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 24080957
Sorry about that. I had a rogue opening ( and one missing also

corrected sql

select [Original POST], GetPart([Original POST],1) AS [Resulting POST], GetPart([Original POST],2) AS NewCol1, GetPart([Original POST],3) AS NewCol3
from mytable


change mytable to your tablename obviously

0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 65

Expert Comment

by:rockiroads
ID: 24080965
Original POST      Resulting POST      NewCol1      NewCol3
51-AB-492      51      AB      492
GATE-001            GATE      001
00012                  00012
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 24080978
man, display is messed up.

With regards to a access query, it probably can be done but will be full of iif's probably, a little messy. Thats why I prefer a easier to read and maintain solution by using udf's.
0
 

Author Comment

by:Uniqueinc
ID: 24081323
I misunderstood before.  I realize now that you were saying to use the the query in conjunction with the VB code.  I was trying to use it independently.  

It only takes a couple of minutes to export the table to Excel, split the columns and re-import into Access.  I was hoping to speed things up by running a query directly in Access, but if that takes longer than my original plan, it's not worth it.  

I appreciate your time, and that you didn't make me feel like an idiot, as usually happens here:-)
0
 
LVL 65

Accepted Solution

by:
rockiroads earned 250 total points
ID: 24081505
Sometimes takes a few posts before we gain an understanding. An expert here is not an expert if they don't have the patience and are not able to adjust their responses accordingly.

if this is a one off you could try a vba approach only by opening a recordset

eg just past in a module, this runs by itself

Public Sub SortMyTableOut()
   
    Dim rs As dao.Recordset
    Dim sWords() As String
    Dim sSource As String
   

    Debug.Print "Working"

'enter your tablename here
    Set rs = CurrentDb.OpenRecordset("tblpost")
    Do While rs.EOF = False
       
        rs.Edit
       
        sSource = Nz(rs("Original POST"), "")
       
        'Dump in immediate window - comment out to slightly speed it up more
        Debug.Print "Processing", sSource
       
        If InStr(1, sSource, "-") > 0 Then
            If InStrRev(sSource, "-") = InStr(1, sSource, "-") Then
                sSource = "-" & sSource
            End If
        Else
            sSource = "--" & sSource
        End If
        sWords = Split(sSource, "-")
       
        If 1 > UBound(sWords) + 1 Then
            rs("Resulting POST").Value = ""
        Else
            rs("Resulting POST").Value = sWords(0)
        End If
       
        If 2 > UBound(sWords) + 1 Then
            rs("NewCol1").Value = ""
        Else
            rs("NewCol1").Value = sWords(1)
        End If
       
        If 3 > UBound(sWords) + 1 Then
            rs("NewCol2").Value = ""
        Else
            rs("NewCol2").Value = sWords(2)
        End If
       
        rs.Update
       
        rs.MoveNext
    Loop
    rs.Close
    Set rs = Nothing
End Sub



0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

26 Experts available now in Live!

Get 1:1 Help Now