Search code examples
vbacsvconnectionadorecordset

How to get specific column from csv in Recordset ADO

I am using csv named like Data1.csv that having data like below.

Name;Age
Datta;20
Suraj;30
Raman;20
Sujit;35

I want only the records from column Name i.e. 

Datta
Suraj
Raman
Sujit

I used below code get only record from name field

Set cN = CreateObject("ADODB.Connection")
Set RS = CreateObject("ADODB.Recordset")
cN.Open ("Provider=Microsoft.Jet.OLEDB.4.0;" & _
               "Data Source=E:\VBScript Exersize;" & _
               "Extended Properties=""text; HDR=Yes; FMT=Delimited; IMEX=1;""")

RS.Open "select [Name] from Data1.csv", cN   'Got error here
For Each itm In RS.getrows
    MsgBox itm
Next

Error:No value given for one or more required parameters

Problem is when when i am dealing with csv the columns fields consider only 1 i.e (Name;age) i want 'Name ' only.

Could anyone please help on this...

Thanks in Advance

Solution

  • CSV stands for "Comma-Separated Values", so normally a valid delimiter for CSV format is comma.

    There are several options for you:

    1. Use comma(,) instead of semicolon(;) in your CSV file to separate fields.

    2. Refer to https://www.connectionstrings.com/textfile/ , under the "Microsoft Jet OLE DB 4.0" "Delimited Columns" section, there is a particular entry in your Registery that you can change to specify the delimiter.

    3. On the same page above, under the ".NET txtReader for Text Files" section, you could use .NET txtReader as the ADODB connection engine and specify column types and delimiter.

    Good Luck :)