I can successfully retreive data using:

Declare @Path varchar(100)
Declare @CommandString varchar(100)
Set @Path = ''
Set @CommandString = 'Select * from [myTable.csv]'

EXEC('SELECT *
from OpenRowset(''MSDASQL'', ''Driver={Microsoft Text Driver (*.txt; *.csv)};DefaultDir=\\myServer\myShare\db\' + @Path + ';'',''' + @CommandString + ''')')

Does anyone know an easy method to get column names into a table using OpenRowset (without using linkedserver)