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:

[TABLE="class: grid, width: 150, align: left"]
<tbody>[TR]
[TD]111.222.aaa.bbb.ccc[/TD]
[/TR]
[TR]
[TD]ee.22.11.9855.daed.fre2[/TD]
[/TR]
[TR]
[TD]degt.ee2.222.444.qqqq.aaa[/TD]
[/TR]
[TR]
[TD]222.ddd.ffff.2222.6666.eee[/TD]
[/TR]
</tbody>[/TABLE]







What I am looking to accomplish is:

[TABLE="class: grid, width: 150, align: left"]
<tbody>[TR]
[TD]111[/TD]
[TD]222[/TD]
[TD]aaa[/TD]
[TD]bbb[/TD]
[TD]ccc[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ee[/TD]
[TD]22[/TD]
[TD]11[/TD]
[TD]9855[/TD]
[TD]daed[/TD]
[TD]fre2[/TD]
[/TR]
[TR]
[TD]degt[/TD]
[TD]ee2[/TD]
[TD]222[/TD]
[TD]444[/TD]
[TD]qqqq[/TD]
[TD]aaa[/TD]
[/TR]
[TR]
[TD]222[/TD]
[TD]ddd[/TD]
[TD]ffff[/TD]
[TD]2222[/TD]
[TD]6666[/TD]
[TD]eee[/TD]
[/TR]
</tbody>[/TABLE]








Thank you in advance for your help!

 

[Joe4]

I am not the person that handles the raw data load, so there is no way for me to convert to a text file

You wouldn't have to, the VBA code could! You could make it part of the import process. A well-designed Access database is all Form driven, so you would just have a Form with a button where they select which file they want to import, and click the button, and the VBA code handles all the rest!

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....

You have taken on quite a task for a newbie to Access. Do you have any relational database or programming background?

You could do those things, however, they might get a little tricky.
Who is importing the data and how are they doing it?
Is there a limit to how many different fields there might be (in your example, some had 5 and others had 6)?

I am thinking there are a few ways to approach this (if you want to use this approach), such as:

Option 1
- Have the person import into an existing temporary table
- Create an Append Query that runs on that temporary table, splitting up the fields as needed, and then writes the records to the final table
- Create a Macro that runs the Append Query you just created, and then some code to delete the data out of that temporary table (so it is blank again and ready for the next run)
So the person would just need to run this macro after importing the file into the temporary table

Option 2
- Have the person import into an existing temporary table
- Create VBA code which looks through the whole table, and writes the record to the new table, and then delete the table from the temporary table

I am thinking that Option 1 might be the option you want to go. You would just need to create a bunch of calculated fields that extra each value. And you should give them a name (alias) that matches field name in your final table.

 

[Joe4]

If you want help writing your query, then please let us know the following (in addition to the other questions I asked in the previous post):
- What is the name of the two tables (the temporary one and the final one)?
- What is the structure of the final table we are writing to? Namely, what is each field name and data type?
- What is the field name what gets created when the table is imported to the temporary table (if Excel has a header row, it will probably be this value)?

 

[westc4]

Thank you so much for your help! Yes I have program experience with Excel VBA code and have written a number of different macros in Excel. Access, I have basic query design, import and export of excel files, table and report creations and maintenance, not much else. Yes, I know over my head with this project... but trying to swim

A different department is uploading the data (I believe they are gathering the raw data from an Outlook inbox, grabbing the subject lines of incoming emails) The number of fields should not be more than 5 based on the template being used to create the subject lines (but that may/could change).

Option 1 would be the best way to go.

-Data will be from a direct link to an excel file that is updated multiple times a day
-Name of temporary table is "Inbox"
-Name of final table "WorkLoad"
-Final table structure:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]PO Number[/TD]
[TD]Account Number[/TD]
[TD]CustName[/TD]
[TD]ID1[/TD]
[TD]ID2[/TD]
[/TR]
[TR]
[TD]Short Text (mix number and alpha)[/TD]
[TD]Number[/TD]
[TD]Short Text[/TD]
[TD]Short Text (number, but could have alpha)[/TD]
[TD]Short Text (number, but could have alpha)[/TD]
[/TR]
</tbody>[/TABLE]

-Field name (header of import) of temporary table "Subject Line" there is only one column in the uploaded excel file

Thank you!!!

 

[Joe4]

OK, first, we are going to build a UDF function to do the splitting. Here it is here:

Public Function SplitString(pInput As Variant, pNum As Long, Optional pSplitChar As Variant = ".") As Variant

    Dim varPieces As Variant

    On Error GoTo err_fix
        
    varPieces = Split(pInput, pSplitChar)
    SplitString = varPieces(pNum)
    
    On Error GoTo 0
    
    Exit Function
    
err_fix:
'   Set value equal to null if error
    SplitString = Null
    Err.Clear

End Function

Then, we are going to create our Append Query to split the data and write it to our final table. The SQL code is below. You should just be able to create a new Query, switch to SQL View, and copy and paste this code there.

INSERT INTO WorkLoad ( [PO Number], [Account Number], CustName, ID1, ID2 )
SELECT SplitString([Subject Line],0) AS PO_Number, SplitString([Subject Line],1)+0 AS Account_Number, SplitString([Subject Line],2) AS CustName, SplitString([Subject Line],3) AS ID1, SplitString([Subject Line],4) AS ID2
FROM Inbox;

Then, you can create a two step macro that will write this data to your table and clear the InBox table.
For the first step, simply choose the OpenQuery Action and select this Query name (whatever you named it).
For the second step, choose the RunSQL Action and enter this line of code:

DELETE Inbox.* FROM Inbox

(if you do not see this Action, be sure to click the "Show All Actions" button in the ribbon).
Then save the Macro.

Now, after they import the data, all the have to do is run this Macro by clicking on it.

 

[westc4]

Awesome! Worked like a charm!! Thank you so very much, it was great to have a patient teacher on this!

 

[Joe4]

You are welcome!