Unanswered: Split files names in one field to multiple fields. Can it be done?
I have one final thing to accomplish (that I know of) and I'm hoping someone knows how to do this. I searched everywhere for a solution and haven't found one so I'm back to pick your masterfully skilled minds. But this one is tricky!
In one of my fields, I have a list of image names, each of which are separated with a ; and a space (or) "; " to be exact.
So here's the challenge. I need to put these in their own fields. There can be as few as "0" or as many as "20" images to split up. If this were excel, I would simply copy to notepad, find and replace the "; " with a tab (ya know the blank long space lol) and then past back into excel. However this isn't excel and that method won't work here.
Here's the breakdown (if you're up to it)
This is an example of the images that need to be split up
Note: the field name is "ALT_IMG" in table name "ONE BIG TABLE"
The desired result would be to have each image in its own field.
The first image would be in its original home of "ALT_IMG" then for each extra image it would go into "ALT_IMG_2" then "ALT_IMG_3" and up to "ALT_IMG_20". Finally if there were no images in the "ALT_IMG" then it would remain blank.
I can go in after and remove the spaces and ; characters so it doesn't need to be built in.
Your help or statement of "it can't be done" will be VERY appreciated. Or an alternate approach. I'm somewhat familiar with Queries, Update Queries, Make Table Queries, Append Queries and SQL View. I have a basic knowledge of Access 2003 as well (the version I'm running).
Thank you but I am not skilled enough to know what that is. It looks like something I'd put in SQL view of a query! But what kind! What do I change? I'm REALLY new to this. I've learned alot but I'm still scraping to survive with this stuff. lol
I did get the data split up in Excel / NoteTab but the order is lost when I try to import it to Access.
I'm trying any way I can because this is my last step and I, well, just want to be done. lol I just don't understand all the code!
It looks like something I'd put in SQL view of a query! But what kind!
What's the final destination of the file names (i.e. what do you intend to do with them once they're split)? Note: A query cannot have a variable number of columns, nor does a table.
Originally Posted by RBusiness
I just don't understand all the code!
In this case, "all the code" precisely consists in two lines of VBA code (a variable declaration and the splitting function itself) . If you intend to use Access efficiently, I would advise you to learn at least the fundamentals about VBA and SQL. You'll quickly come to understand that you cannot work with Access (beyond very basic tasks) without having a minimal knowledge of these languages.
My intention is to split the file names up so that they can be imported into an eCommerce shopping cart dB, of which wants each type of data in its own field for import.
I noticed in the admin import settings of the cart that there is a "Multi-value separator" which is ";" (without quotes). The sad part is that though the setting is there, I can't for the life of me, or by way of support figure out how to tell the import utility to split up the data in the field and put it in X,Y,Z and so on.
So long story short, I'm importing it in unique columns.
I ended up splitting the data up in excel and have successfully imported it to a new table, now I just need to get it from the new table to the sandbox table. But that's another post. If you want to chime in on that one, it will be completely appreciated.
If you intend to use the multivalue fields feature that was introduced in Access 2007 (also called "Lookup field), I (among many people) advise you not to do so. This feature causes more problems than it seems to solve: a database using it is not normalized, cannot be converted to another database format (2000, 2003), nor can the tables be easily exported to another database engine such as SQL Server, MySQL, Oracle, etc. Even worst, you cannot execute most of the basic SQL commands (INSERT UPDATE, etc.) in a standard way on such fields.
The classic way for handling such values consists in creating a second table that will store the different elements (file names in this case) and to create a One-to-Many relationship between the main table (One side) and this second table (Many side), see for instance: Microsoft Access Tips: The One to Many Relationship). In your case, it means that you'll have a main table with a unique Id (its primary key), and a secondary table where to store the file names. The basic structure of this table has 2 columns: one for storing the Id of the parent row from the main table and one column for storing the file name.
Notice that you store one file name per row, not per column. If you store the file names in column, it implies that the number of file names you can store in one row is fixed (limited) which means that:
a) you're wasting space when the number of files for one row is less that the number of columns.
b) you cannot handle records that have more file names than the maximum you allocated when defining the table, except by changing the table definition.
This is why I wrote that a table or a query cannot have a variable number of columns. This is a fundamental difference between a flat model (such as used in Excel) and a relational model.
In a normalized database, storing the file names into the secondary table is rather easy:
Function StoreFileNames(ByVal ParentId As Long, ByVal FileNames As String) As Long
' Input: ParentId is the Id of the parent table (long integer).
' ----- FileNames is the string containing the list of file names separated by a semicolon (;).
' Output: The function returns the number of rows created in the table (= the number of file names found in the string parameter FileNames).
Const c_SQL As String = "INSERT INTO TableName ( Id, FileName ) VALUES ( @Id, '@Fn' );" ' Replace TableName by the actual name of the table.
Dim varFileName As Variant
Dim i As Long
varFileName = Split(Replace(FileNames, "; ", ";"), ";")
For i = 0 To UBound(varFileName)
CurrentDb.Execute Replace(Replace(c_SQL, "@Id", ParentId), "@Fn", varFileName(i))
StoreFileNames = i