How do I Split Up File Names in One Column Field to Multiple Column Fields

RBusiness · #1 (**permalink**) 01-31-12, 15:54

Hello,

Thanks for checking this out. This is one of my last things to do with Access for now! I can't find anything remotely close to this online. Maybe a pro can chime in.

Here's the challenge
In a column of data, I have a field, the field contains image names. Sometimes 1 and sometimes 2 or more, each separated by a ", " (comma and a space). Sometimes there are as many as twenty images in the field. God only knows why this was setup this way.

Ultimately, I'd like to make an update query that puts each image in their own column. For instance, if there is 1 image, then it would stay where it is, if two, then the first stays, but the second get put into column2. If three, then 2 goes in column2 and 3 goes in columns 3 and so on up to 20 total columns.

If this were possible, I'd also have to clear out any commas and spaces and add a "/" before each of the image names. But I can do that in a different update query if need be.

Example 1
Data: IMAGENAME1.JPG
What happens? image stays where it is because there's just one.

Example 2
Data: IMAGENAME1.JPG, IMAGENAME2.JPG
Result: IMAGENAME1.JPG stays where it is, IMAGENAME2.JPG moved to "Column2"

EXAMPLE 3
Data: IMAGENAME1.JPG, IMAGENAME2.JPG, IMAGENAME3.JPG
Result: IMAGENAME1.JPG stays where it is, IMAGENAME2.JPG moved to "Column2", IMAGENAME3.JPG moved to "Column3".

As a backup plan. If this is not possible, is there a way to make an update query that says "Anything after the first comma, including the first comma is deleted"? This way I can use at least one alternate image.

Thoughts?

healdem · #2 (**permalink**) 01-31-12, 16:14

use the split() function to break a string apart. into an array
you wil have to do this using a VBA.

how do you propose storing the resultant values?

as you are aware I have reservations about your table design., calling it [one big table] fires off warning bells in my mind. I fear your design is not normalised.

RBusiness · #3 (**permalink**) 01-31-12, 16:26

I know you have reservation about "ONE BIG TABLE". It was a collection of other tables. I merged all the data in one spot because separated I had 40,000 records and combined it was 59000 records which all need tweaks and such that could not be done separately since there was data that was needed to combined associations (for a related item type of thing) as well as individual descriptions that vary depending on multiple factors. Believe me, the Access dB that I was provided with is both very nicely done and extremely illogical at the same time. Many times I wonder why on earth things were done.

If you explain what normalized is, I can tell you if it is or not. I basically ran a query to join multiple tables into one to work with it easier!

As for VBA. I don't know anything about that or even where to begin! Why is it always at the end of a project that one finds out they have to learn a new language to complete something. LOL

I guess since I don't know how to, then can you provide an update query that says to delete everything from "comma" or , onward? I'll at least have one image to work with. I can figure out VBA another time I guess.

healdem · #4 (**permalink**) 01-31-12, 16:53

OK I'd suggest you stop doing for now and start thinking
read up on normalisation
The Relational Data Model, Normalisation and effective Database Design
Fundamentals of Relational Database Design -- r937.com

I fear you have take a normalised database and denormalised it.

from what you have said so far I think you are creating a product file for a web shop
I guess due to your lack of knowledge on how db's work you've assumed that doens't look right and proceeded to stuff every thing into one table, that means duplicating data.
SQL is a specialised data manipulation language, its optimised to merge / manipulate data.

so I'd expect your design to have something like
a table for manufacturers, including logos
a table for product types (or category_name if you prefer)
a table for products
...linked to product types in product type id
...linked to manufacturer on manufacturer id
a table for product images linked to products by the product ID

if you start repeating columns such as image1, image2, column1,column2 then thats a sign the design is not normalised.

if you start storing the same data in the same column across many rows then thats a sign of a problem (eg you shouldn't have a manufacturers logo associated with a product but with a manufacturer). you can always retrieve the logo as required fromt he manufacturers table.

you don't need to store everything in one table you can retrieve stuff from a table in a query such as

Code:

SELECT my, column, list FROM mytable

you can limit rows using a where clause

Code:

SELECT my, column, list FROM mytable
WHERE manufactuerID = 1

you can set a sort order

Code:

SELECT my, column, list FROM mytable
WHERE manufactuerID = 1
ORDER BY Prodcut_Description

you can bring together the values from several tables into one query

Code:

SELECT Product_Description, products.ID, price, Manufactuers.name, Manufacturers.Logo FROM Products
JOIN Manufactuers on Manufacturers.ID = Products.ManufacturerID
WHERE manufactuerID = 1
ORDER BY Prodcut_Description

RBusiness · #5 (**permalink**) 01-31-12, 17:11

I understand and appreciate your instruction but each row / record of data needs to be in a specific format for import. It's got to be this way (at this time).

The way the import utility works on the cart, each field needs data, otherwise it won't associate anything correctly. It works off MySQL but the dB I've been provided with is Access. My task it to edit the heck out of the Access dB and make it import friendly to the cart import utility which requires data in every row and field (or blank in some).

So that means for me to remove conflicting characters, preface certain things with things like a "/", make other things lowercase, clean characters out of other fields, combine multiple fields into single fields, delete some original data (because it was merged) and so on. It's complicated and I'm really not a dB guy! (can you tell?) LOL

I know you're right that I should only assign a "Brand Logo" once and so on, but it would actually be harder to do because of each one of these records is filling a blank in a product / category field in the dB and I don't have the skills to reinvent the cart's dB structure. I have to follow their way! Their entire system works off this data, including item relationships, page specific data calls (like the brand image), product images, alternate images. Heck there are 190 fields here and it's a 140MB .mdb file with 59000 records.

At this time, I cannot redo! I'm supposed to be done today!

Looking more like tomorrow though!

Hey, maybe once this gets rolling, you can do it in the future

it would probably be faster and better. Actually I know it would be. lol

healdem · #6 (**permalink**) 02-01-12, 04:54

OK its fine to bring together all the data into one row if thats required by another application. BUT that doesn't mean you mangle all your data into one table int he Access DB. you can present everything int he form required using a query when you need to export the data to the outside world, but leave your data normalised.

why is this an issue?
well say one of your suppliers changes their logo
in your approach you have to write a query, repalce every occurance of the logo text. not difficult, but possibly prone to error. if you associate the manufacturer logo with a manufacturer and change it via a form, when you extract your data using a query it automatically appears changed. it also means that the users of the data are responsible for maintaining their data. that means they don't come to you at the last minute demanding x,y & z are changed right now (because its their data, their responsibility). you can build in error checking in the form to ensure that the correct text is specified.

seriously in my books you need to stop thinking about 'one big table' right noe. tell your boss that you've made an error and you need to rethink the whole strategy. yes its going to affect the delivery time of the project but its going to hgave a serious maintenance issue over time.

healdem · #7 (**permalink**) 02-01-12, 05:02

if all you want to do is chop off remaining characters then you can do this via an update statement

Code:

update mytable
set mycolumn = left(mycolumn,instr(mycolumn,",")-1)
where mycolumn like ("*,*")

the onlynew bit here is
left(mycolumn,instr(mycolumn,",")-1)
where we are saying use the value of mycolumn upto buyt not including the "," symbol.
the instr function searches for the first occurance of the specified characters in the sepcified string. in this case we are lookign for "," in mycolumn. as we don't want to include the comma in the column we subtract one from the value. so thats telling the SQL engine to set the new value of mycolumn to be the same value as the first blockl of text minus the column.

the where clause is used to process rows which have a comma in them. you wouldnt' want to run the update without the where clause as instr returns 0 if it cannot find the specified characters in the string
]
remember I suggested you should read up on Access string functions.....

Internet Services

Web Development

Digital Marketing

Consumer Tech