Split dynamic data in access

[westc4]

Hello, I am attempting to split a string of data located in one cell into multiple single cells, with "." as the deliminator in access. I normally work with excel and would just use the text to column function, but that is not an option here.

my data will not be fixed in length, here is an example of what I have:

111.222.aaa.bbb.ccc

ee.22.11.9855.daed.fre2

degt.ee2.222.444.qqqq.aaa

222.ddd.ffff.2222.6666.eee

<tbody>
</tbody>

What I am looking to accomplish is:

111	222	aaa	bbb	ccc
ee	22	11	9855	daed	fre2
degt	ee2	222	444	qqqq	aaa
222	ddd	ffff	2222	6666	eee

<tbody>
</tbody>

Thank you in advance for your help!

 

[Joe4]

Is that data already in Access, or are you importing it into Access (from some external file)?

 

[baderms]

you can use the split to create a array, for example

Dim aData() as String

adata = split(Your data string, ".")

To reference the individual elements you would do like this: adata(0) to see the first element. you can use the ubound to determine the upper number of elements.

 

[westc4]

Is that data already in Access, or are you importing it into Access (from some external file)?

The data will be imported.

 

[Joe4]

The data will be imported.

From where?
What kind of file is it that you are importing (Excel, text, etc)?

 

[westc4]

From where?
What kind of file is it that you are importing (Excel, text, etc)?

The raw data is from an excel file, pulled into an access table (data) with the Data Type as "Short Text", field name is (data1).

Thank you.

 

[Joe4]

If you save it out to a Text File, then when you import as a Text File, you can choose the delimited option with the period as the delimiter.
To me, that is probably the easiest way.

Otherwise, if importing as an Excel file, you are going to have to try to deal with it after the fact, after it is already in Access in a single-field.
You would then need to use a query or VBA to separate them into multiple fields.
The tricky part there is you kind of need to know how many fields you will need ahead of time (so you know how many calculations to do and/or how many table fields to set up for it).

 

[westc4]

It will be coming in as an excel file so I am going to need to deal with it after it is in Access. I have been playing with some coding and have this:

Public Function FINDBREAK(ByVal pInput As Variant, _
        Optional pSplitChar As Variant = ".") As Variant
    
    Dim varResult As Variant
    Dim varPieces As Variant

    If IsNull(pInput) Then
        varResult = Null
    Else
        varPieces = Split(pInput, pSplitChar)
        If UBound(varPieces) > 1 Then
            varResult = varPieces(1)
        Else
            varResult = Null
        End If
    End If
    FINDBREAK = varResult
End Function

But it is only returning the data between the first . and second . When I need the data from the start of the string to the end of the string, until there is not more data (could be number, could be alpha) in the string

 

[Joe4]

You need to loop through your array, from Lbound to Ubound.

I do not know how to write User Defined Functions that return Arrays. I know how to create a procedure to split them up, something like this:

Public Sub SplitString(ByVal pInput As Variant, _
        Optional pSplitChar As Variant = ".")
    
    Dim varResult As Variant
    Dim varPieces As Variant
    Dim i As Long


    If IsNull(pInput) Then
        varResult = Null
    Else
        varPieces = Split(pInput, pSplitChar)
        For i = LBound(varPieces) To UBound(varPieces)
            varResult = varPieces(i)
            MsgBox varResult
        Next i
    End If


End Sub

So then if you called this procedure like this, for example:

Call SplitString("aaa.bb.cccc")

It will return three separate message boxes, with "aaa", "bb", and "ccc".
So instead of the message box, you would just put that where you want.

Here are a few threads I found on creating Functions that return arrays. Though they are not specifically talking about Access, I think the concepts should be the same:
http://www.cpearson.com/excel/returningarraysfromvba.aspx
http://www.geeksengine.com/article/vba-function-multiple-values2.html

Note: If you are willing to write VBA code (which it looks like you are), I would be more inclined to write VBA code to convert my Excel file to a text file, and import the text file, like my original suggestion.

 

[westc4]

I am not the person that handles the raw data load, so there is no way for me to convert to a text file Trust me I would much rather work with the excel file!

I apologize I am very new to access. Could I take the raw data table (automatically loaded multiple times a day into access) and create a "new" table that would have the split up data? That way I don't have to "touch" the raw data I could just work with the "new" table. Since raw data is loaded multiple times a day, I don't want to overwrite, I would need to append to existing "new" table....