0

I loaded a csv file as a 2-D array to memory with following code:

Sub readcsv()
Dim wbCSV As Workbook
Dim Data As Variant

Application.ScreenUpdating = False

Set wbCSV = Workbooks.Open(Filename:="C:\Users\File.csv")

With wbCSV
    Data = .Sheets(1).UsedRange.Value
    .Close
End With

End Sub

next step i want to search through the data and pick some. the point is that I want to do so without opening the csv itself. is there any way to search through the loaded array? I also know i-j of the intrested data in the csv. The csv uses ; as delimiter. with this code

data(i,j)

I expect to have the element in the i-j cell but it returns the nonsense string. So the question is, does vba recognise the ; as delimiter when the file is loaded in this way? or i need to arrange the loaded data into a form of a matrix and then call a specific cell?

update:

I have found that 

data(i,j)

returns strange string because vba considers , as the delimiter. So for example when in the 5th row I have a;b;1,2345;c data(5,2) returns 2345;c

thanks

Hamtash
  • 129
  • 9
  • "I also know i-j of the intrested data in the csv. so I may even do not need to search through the array." - then it seems you've solved your own question? – BigBen Apr 28 '20 at 13:47
  • 2
    If you know i-j of the data, refer to `data(i,j)` – Storax Apr 28 '20 at 13:47
  • "without opening the csv" in your next step? Confusing, since you just loaded it in your previous step. Maybe just unlucky phrasing. – JvdV Apr 28 '20 at 13:54
  • 1
    @bigben i just need the access to a specific element of that array. – Hamtash Apr 28 '20 at 14:08
  • @jvdv i mean without browing the csv into excel. – Hamtash Apr 28 '20 at 14:08
  • 1
    You can open the CSV file as a text file using File I/O, loop through the data line by line until you find what you are looking for, and then close the file. – Applecore Apr 28 '20 at 14:14
  • 1
    Are you talking about a [random-access file](https://www.engram9.info/excel-2002-vba-xml-asp/working-with-random-access-files.html)? – Storax Apr 28 '20 at 14:14
  • @Applecore how can I advance through the cells? does it recognise separator in the original csv? – Hamtash Apr 28 '20 at 14:25
  • What do you mean by "nonsense string"? Unless it is a string with many semicolons, an inability to use the delimiter is unlikely to be the problem. – John Coleman Apr 28 '20 at 14:27
  • 1
    Please post a sample of the data file, and also an indication of which piece of data you will be wanting to extract. – Applecore Apr 28 '20 at 14:29
  • @Applecore An example is added – Hamtash Apr 28 '20 at 14:32
  • @JohnColeman Explanation is added in Update – Hamtash Apr 28 '20 at 14:33
  • 1
    Does this answer your question? [Import semicolon separated CSV file using VBA](https://stackoverflow.com/questions/39890471/import-semicolon-separated-csv-file-using-vba) – John Coleman Apr 28 '20 at 14:34

1 Answers1

0

You can open the CSV file as a text file using File I/O, loop through the data line by line until you find what you are looking for, and then close the file

Anshul Tyagi
  • 2,076
  • 4
  • 34
  • 65