VBA - Identifying alpha characters in CSV to import into arrays

[JakeB8]

I'm editing VBA code that was created by someone else several years ago. The end goal is to import data from a CSV file into multiple arrays using multiple VBA subs. The main driver for what to import is in column A of the CSV, which is labeled as "ID." There is one sub that searches the ID values for anything beginning with "L" or "H." That part works fine. There is another sub that searches the ID values for anything else, but if the values aren't strictly numerical then they aren't being recognized. We'd like to be able to have alpha-numeric values here, but right now we can't. Generally the alpha characters we need to recognize (other than L or H) would be at the end of the numeric string, but the numeric string isn't always the same length (ex. 1A, 11A, 111A). How can we guarantee the code would recognize the the alpha-numeric strings that don't start with L or H?

ID	PART​	LENGTH	result​
1A	A12B20-6	90	--> Is not recognized.
2	A12B20-6	1755	--> Recognized and sent to Array 1.
3	A12B20-6	90
4	A12B20-0	115
5	A12B20-0	1730
6	A12B20-0	115
L1	192-C2524	1590	--> Recognized and sent to Array 2.
H1	A71A46-6	1050	--> Recognized and sent to Array 3.

 

[alexos]

Could you not just see whether the last character is numeric or not and then do something (or not) as needed?
Some examples...

Sub Test()

    Dim mystring As String
    
    mystring = "111"
    
    ' Use IsNumeric to check whether the whole string is a number or not.
    If IsNumeric(mystring) = True Then
        MsgBox "Just numbers here: " & mystring, vbOKOnly
        ' Do your thing here
    End If
    
    mystring = "1A1B"
    
    ' Same again. IsNumeric to check whether the whole string is a number or not.
    If IsNumeric(mystring) = False Then
        MsgBox "Numbers and letters here: " & mystring, vbOKOnly
        ' Do your thing here
    End If
    
    mystring = "111B"
    
    ' Now just look at the right hand character and do something based on that.
    If IsNumeric(Right(mystring, 1)) = False Then
        MsgBox "ends with a letter: " & mystring, vbOKOnly
        ' Do your thing here
    End If

End Sub

 

[Fuji]

How can we guarantee the code would recognize the the alpha-numeric strings that don't start with L or H?

Use Like operator e.g.

If Your Data Like "[!HL]*" Then

 

[JakeB8]

I'm having trouble getting all of the data that I want when importing a csv file into a new document. Column A of the csv contains ID numbers, but if the ID happens to have text instead of just a number then the import function doesn't pick up that row of data. The code below was written by someone else, and I've been trying to figure out why it won't pick up text entries but I'm stumped. Does anyone see anything that is causing my problem?

Thanks.

'DECLARING VARIABLES READING DATABASE AND ARRAY
Dim whole_file As String
Dim fnum As Integer
Dim num_cols_i As Integer
Dim lines_i As Variant
Dim one_line_i As Variant
Dim r As Long
Dim c As Long

'OPEN FILE FOR VBA TO READ, AS INPUT TO ARRAY
fnum = FreeFile
Open txtfilename For Input As fnum
whole_file = Input$(LOF(fnum), #fnum)
Close fnum

' BREAK DATABASE LINE BY LINES
lines_i = Split(whole_file, vbCrLf)

' DIMENSION THE ARRAY DYNAMICALLY "UBOUND"
num_rows_i = UBound(lines_i)
one_line_i = Split(lines_i(0), ",")
num_cols_i = UBound(one_line_i)
ReDim IMPORT_ARRAY(num_rows_i, num_cols_i)

' GET DATA INTO ARRAY
    For r = 0 To num_rows_i
        If Len(lines_i(r)) > 0 Then
            one_line_i = Split(lines_i(r), ",")
            For c = 0 To num_cols_i
                IMPORT_ARRAY(r, c) = one_line_i(c)
            Next c
        End If
    Next r

 

[alansidman]

You may want to look at Power Query to import the csv file to Excel. Power Query is called Get and Transform Data and found on the Data Tab of the Ribbon. It is an add in on 2010 and 2013 but is part of Native Excel in later versions. It can be accomplished in the UI and no coding required

 

[Alex Blakenburg]

You had a previous thread to which you got replies but didn't respond. That thread indicates there is more to this code than what you have posted here. There is nothing in the code that you have posted that would skip rows that had text in the ID position.
Using the data in your previous thread you can see both the text ID and Numeric ID have loaded into the array IMPORT_ARRAY.

.

 

[JakeB8]

You had a previous thread to which you got replies but didn't respond. That thread indicates there is more to this code than what you have posted here. There is nothing in the code that you have posted that would skip rows that had text in the ID position.
Using the data in your previous thread you can see both the text ID and Numeric ID have loaded into the array IMPORT_ARRAY.

View attachment 122925.

My apologies! I haven't worked on this project in a while and totally forgot about my original post.

If the text and numeric values are both being imported then there's another process where the text is being ignored. I'll have to keep digging. I wasn't aware of the Watches box (while I'm not new to VBA, I'm certainly still a novice). I hope that can help me out.

 

[JakeB8]

Accidentally created a 2nd thread after forgetting about this post.

Trouble getting all data by importing from csv

 

[Joe4]

I have merged your two threads together.

In future, please do not post the same question multiple times. Per Forum Rules (#12), posts of a duplicate nature will be locked or deleted.