0

I have loaded a csv file in memory. My csv file uses ";" as the field delimiter.

It seems vba default delimiter is "," because when I try to access certain row and column of the loaded csv file, vba advances through the elements with refrence to number of "," used.

example:

In the 10th row of my data there are five columns: aa 12,34 bb 5,678 (here "," is decimal separator)

in the csv file which the delimiter is ";" it looks like this:

aa;12,34;bb;5,678

so when I write 

MyData(10,2)

I am expecting to get 12,34 but vba returns 34;bb;5 because it uses "," as field delimiter.

So my question:

How can I tell vba to search through the loaded csv file with respect to ";" as delimiter instead of ","?

Thanks.

Hamtash
  • 129
  • 9
  • 1
    Did the comment from @JohnColeman in your previous question https://stackoverflow.com/questions/61481669 not help? – Applecore Apr 29 '20 at 10:10
  • Have a look at [this answer](https://superuser.com/a/686415/64675) on the SE SuperUser site. – collapsar Apr 29 '20 at 10:15
  • What do you mean by "I have loaded csv file in memory"? Could you include the code where you "load the file to memory"? – Prebsus Apr 29 '20 at 10:44

1 Answers1

2

Instead of trying to change the delimiter which excel uses to load a csv file it might be more straightforward to do that on your own

First you use a function to load the lines of a text file into a collection and then you access the wanted line in that collection and go to the wanted column.

Code for this

Option Explicit

Function txtfileinCol(filename As String) As Collection
' loads the content of a textfile line by line into a collection
    Dim fileContent As Collection
    Set fileContent = New Collection

    Dim fileNo As Long
    Dim txtLine As String

    fileNo = FreeFile
    Open filename For Input As #fileNo
    Do Until EOF(fileNo)
        Line Input #fileNo, txtLine
        fileContent.Add txtLine
    Loop

    Close #fileNo

    Set txtfileinCol = fileContent

End Function

Sub Testit()
    Const DELIMITER = ";"

    Dim filename As String
    Dim col As Collection
    Dim vdat As Variant
    Dim colNo  As Long
    Dim rowNo As Long

    filename = "C:\Temp\FILE.csv"
    Set col = txtfileinCol(filename)

    colNo = 2
    rowNo = 10

    vdat = col.Item(rowNo)  'here you get the line you want
    vdat = Split(vdat, DELIMITER) ' now you split the line with the DELIMITER you define

    Debug.Print vdat(colNo - 1)  ' now you print the content of the column you want


End Sub

Update: For accessing the row and column you could also use a function. The code would look like that

Option Explicit

Function txtfileinCol(filename As String) As Collection
' loads the content of a textfile line by line into a collection
    Dim fileContent As Collection
    Set fileContent = New Collection

    Dim fileNo As Long
    Dim txtLine As String

    fileNo = FreeFile
    Open filename For Input As #fileNo
    Do Until EOF(fileNo)
        Line Input #fileNo, txtLine
        fileContent.Add txtLine
    Loop

    Close #fileNo

    Set txtfileinCol = fileContent

End Function
Function getColRow(fileLines As Collection, rowNo As Long, colNo As Long, Optional delimiter As String) As String

    Dim vdat As Variant

    On Error GoTo EH:

    If Len(delimiter) = 0 Then
        delimiter = ";"
    End If

    vdat = fileLines.Item(rowNo)    'here you get the line
    vdat = Split(vdat, delimiter)   'now you split the line with the delimiter

    getColRow = vdat(colNo - 1)     'now you retrieve the content of the column
    Exit Function
EH:
    getColRow = ""

End Function

Sub Testit()

    Dim filename As String
    Dim col As Collection

    filename = "C:\Temp\FILE.csv"
    Set col = txtfileinCol(filename)   

    Debug.Print getColRow(col, 10, 2, ";") 

End Sub
Storax
  • 11,158
  • 3
  • 16
  • 33
  • Thanks. It works. Is it possible to look up this built array for a specific value and return the corresponding row number? – Hamtash May 04 '20 at 11:43
  • Yes, certainly. But the search could give you more than one hit. – Storax May 04 '20 at 12:17
  • I know that the desired values are unique in the whole file. – Hamtash May 04 '20 at 12:20
  • Then just go for the first hit which should be the only one then. You need to loop through the collection which contains the single lines of the file, you look for your specific value and if you find it you automatically have the row number. Try something on your own and if you get stuck create a new post with a reference to this one. – Storax May 04 '20 at 12:55