2

I am a programmer but recently new to both powershell and vba. I found this simple but effective command in powershell to read in a csv file and you can then immediately access "columns" by their heading in the csv file. in the example below there was a heading called "Location Description" and I could immediately access a column by that heading. Does VBA have something like for reading in a csv file and would that also be similar to reading in a worksheet if the macro was run from within the spreadsheet?

Snippet of Code

$InputData = import-csv $InputFile
foreach ($row in $InputData)
{
    $locdesc = $row."Location Description"
ChrisF
  • 41,540
Alessandra
  • 41

1 Answers1

0

There's nothing built in to Excel VBA that would allow you to address a column in a CSV file by it's header/field name.

There is, however, ADODB in the Microsoft ActiveX Data Objects Libraries. In there you can open a connection to a CSV by using a JET/CSV connection string to point to the file, define your delimiter, and tell it you have headers. Then you can pull open the content of the CSV into a recordset and refer to the fields by name within the recordset object. You can also throw whatever SQL you want at the CSV and do some of your data manipulation before getting the recordset back form JET.

That's a bit round-about, but if all you know is the name of a field in a CSV and not the ordinal, then it's probably your best bet.

JNevill
  • 1,241
  • 6
  • 12