Text To Column (1 Viewer)

[tejasdave]

Hello Friends,

So here is my question.

I have a table with data Name,Title,Products.
Produccs column has lots of products against each name seperated by ";"
Example : Name,Title,Product1;Product2
what i want is Row1 :Name,Title,Product1
Row2 :Name,Title,Product2 and so on.

i have a code but it does not seem to work.

here it is :

Function test()
Dim rstTable As DAO.Recordset
Dim i As Integer
Dim strNames() As String

Set rstTable = currentdb.TableDefs("source").OpenRecordset
Do While Not rstTable.EOF

'first check if the field holds an ; if not then assume we dont need any new rows
If InStr(1, rstTable( 0 ), ";") > 0 Then 'assumes names are in the 1st col of table

'split the values
strNames = Split(rstTable( 0 ), ";") 'assumes names are in the 1st col of the table
For i = 0 To UBound(strNames)
rstTable.Edit
rstTable.AddNew
rstTable( 0 ) = strNames( i )
rstTable.Update
Next i

End If
rstTable.MoveNext
Loop
End Function

Regards,
Tejas

 

[vbaInet]

So you're now trying to normalize your table?

Is there a Primary Key?

 

[tejasdave]

yes....Primary key is name

 

[vbaInet]

I can't see how Name will be a Primary Key. Can you show me three sample records.

 

[tejasdave]

Here you go :

Name Title Product
Tejas MO RAD;RAC;RAL
Dipen MO RAD;RAC;RAL
Kamal DO RAD;RAP;RAL;RAN

 

[vbaInet]

Can you write each record on separate lines with Column Headings separated by a semi-colon (

 

[tejasdave]

Done mate...Please refresh

 

[vbaInet]

You are not using the right field for a PK.

So tell me, where is the data coming from?

 

[tejasdave]

Its coming from my system and is auto generated.

 

[vbaInet]

So is it an import from one db to another?

Is this going to be a regular exercise?

 

[tejasdave]

its linked to an excel.yes it is a daily excercise.

 

[vbaInet]

It sounds like it's going to be a cumbersome exercise. Have you thought about migrating completely to Access?

I will give you the logic and you can try coding it:

1. Upload the Excel spreadsheet into a temporary table
2. For each record in the temp table, save Product into a variable
3. Split the variable using a semi-colon as the delimeter
4. For each item in step 3, create a new record.
5. Empty your temp table

It's either you use a temp table or you read your spreadsheet line by line.

Oh, wait did you say linked file? Run step 1 from a query based on this linked file.

 

[tejasdave]

step 1 done....rest im not sure how to do it as i am new in access n not good with vba.

help me out mate.

 

[vbaInet]

Ok tejasdave. You wrote code above so I was expecting you will be able to come up with some sort of code up to at least step 3.

Step 1, should be replaced by:

Oh, wait did you say linked file? Run step 1 from a query based on this linked file.

But before you proceed, you need to work on your Primary Key. What does Name signify? A name of a product or a name of a person?

Also, Name is not a good name for a field. Name is a reserved keyword in Access/VBA.

 

[tejasdave]

Step 1 : made query n works.
Changed Name to CName.

What next ?

Regards,

Tejas

 

[vbaInet]

What next? -->

But before you proceed, you need to work on your Primary Key. What does Name signify? A name of a product or a name of a person?

 

[tejasdave]

name of a person n it is unique.there will never be any duplicates as we have a specific list of customers which will never increase.

 

[vbaInet]

Ok, if that's how you want it

So, you wrote the code in post #1 right? Have you attempted Step 2? If you're not sure how to read from your table in code, here's a link that will help:

http://allenbrowne.com/ser-29.html