Looking for a vlookup type query to convert numbers to text

[speedhunt3r]

Hi guys,



I have a few .csv imports and one column in the .csv file is a series of numbers separated by comma

eg, 1,2345,417,435

I have a saved import that is a very simple table that has the two columns, name & ID.

I want to be able to:

1) Separate (or delimit) the series of numbers by comma

2) vlookup each number with the saved import table by ID and grab the name

3) replace the ID with the name

4) If the ID does not have a corresponding name & ID (vlookup fails), then remove that ID from the .csv import file.

5) Eventually replace all IDs with names. Remove all IDs with no matching name, and have 2 columns at the end of the query : ID in one column + name in another

I hope you understand what I'm trying to do.

Eventually I want to create a macro so it would happen by a click of a button.

If you need any clarifications please let me know.

[recyan]

I have a few .csv imports and one column in the .csv file is a series of numbers separated by comma

eg, 1,2345,417,435

Can you elaborate as to how the data in the CSV file looks when imported in to an access table.
Is it

MyNumbers
1,2345,417,435
2,452,23,4751
......

or

MyNumbers
1
2345
417
435


If it is the first case,
is it always 4 nos separated by commas
or
it could be variable number of numbers.

Thanks

[speedhunt3r]

It's the first type, it's a series of numbers seperated by comma, and they can vary between 1 to 4 number of numbers.

[recyan]

Sorry, more questions

Let us say, you have from the CSV files the below table:

tblNumbers

ID TheNumbers
1 1,2,3,4
2 5,6,7
3 8,9
4 10

and
another table containing the numbers with the names

tblNumberNames

TheNumber_1 TheName
1 A
2 B
3 C
4 D
5 E
6 F
7 G
8 H
9 I
10 J
11 K
12 L
13 M
14 N
15 O

Do you want the result to look something like below ?

TheNumber TheNumber_1 TheName
1 1 A
2 2 B
3 3 C
4 4 D
5 5 E
6 6 F
7 7 G
8 8 H
9 9 I
10 10 J


Thanks

[speedhunt3r]

Sorry, more questions

Let us say, you have from the CSV files the below table:

tblNumbers

ID TheNumbers
1 1,2,3,4
2 5,6,7
3 8,9
4 10

and
another table containing the numbers with the names

tblNumberNames

TheNumber_1 TheName
1 A
2 B
3 C
4 D
5 E
6 F
7 G
8 H
9 I
10 J
11 K
12 L
13 M
14 N
15 O

Do you want the result to look something like below ?

TheNumber TheNumber_1 TheName
1 1 A
2 2 B
3 3 C
4 4 D
5 5 E
6 6 F
7 7 G
8 8 H
9 9 I
10 10 J


Thanks

Yes, that is how the output should be, but TheNumber is not the same as TheNumber_1 . TheNumber would be the parent ID and the TheNumber_1 would be sub-category ID. However, yes the output should look like that.

[recyan]

Not sure if I have understood things correctly,
but till another option comes along, check out below for guidelines ( based on table structures in my previous post ) :

The source for the function ( as far as I can see, should work without any problem. There is another one in the same thread below the function - check it out also)

http://www.pcreview.co.uk/forums/spl...-t1172256.html

Save below function :

Public Function fNthElement(KeyString As Variant, Delimiter As String, ByVal ElementNo As Integer) As Variant
    Dim arrSegments As Variant

    If Len(Trim(KeyString & "")) > 0 Then
        arrSegments = Split(KeyString, Delimiter, -1, vbTextCompare)
        If ((ElementNo - 1) <= UBound(arrSegments)) And (ElementNo > 0) Then
            fNthElement = arrSegments(ElementNo - 1)
        Else
            fNthElement = Null
        End If
    Else
        fNthElement = Null
    End If
End Function 

Save the below first subquery :

qrySplitTheNumbers

SELECT 
    tblNumbers.ID, 
    tblNumbers.TheNumbers, 
    Int(fNthElement([TheNumbers],",",1)) AS TheFirstNumber, 
    Int(fNthElement([TheNumbers],",",2)) AS TheSecondNumber, 
    Int(fNthElement([TheNumbers],",",3)) AS TheThirdNumber, 
    Int(fNthElement([TheNumbers],",",4)) AS TheFourthNumber
FROM 
    tblNumbers;

Save the below second subquery :

qryGetAllTheNumbers

SELECT ID, TheFirstNumber as TheNumber FROM qrySplitTheNumbers WHERE TheFirstNumber is not null
UNION  
SELECT ID, TheSecondNumber as TheNumber FROM qrySplitTheNumbers WHERE TheSecondNumber is not null 
UNION  
SELECT ID, TheThirdNumber as TheNumber FROM qrySplitTheNumbers WHERE TheThirdNumber is not null 
UNION 
SELECT ID, TheFourthNumber as TheNumber FROM qrySplitTheNumbers WHERE TheFourthNumber is not null;

The final query to run :

qryGetTheNumberNames

SELECT 
    qryGetAllTheNumbers.ID, 
    qryGetAllTheNumbers.TheNumber, 
    tblNumberNames.TheNumber_1, 
    tblNumberNames.TheName
FROM 
    qryGetAllTheNumbers 
    INNER JOIN 
    tblNumberNames 
    ON 
    qryGetAllTheNumbers.TheNumber = tblNumberNames.TheNumber_1;

Note :
1) Another option could be, doing a "Text To Columns" in the Excel itself & then importing it to Access table (this would eliminate the use of split function query)
2) For all that I know, there might be a much much easier way.

Thanks