Read a CSV File Delimited with Hash

Question

I have a CSV export from an LDAP DB and I have to read data with a Classic ASP page.

The format of the CSV file is like

CRDLVR47E67L781V#1653#CORDIOLI#ELVIRA#658#[e-mail]#SI

I can read the file line by line, and have to split the line manually. If I change the # value to a comma I can access the file by column. Can I make the asp page able to access the file by column, in order to obtain the single value keeping the # separator?

My connection string is

Set oConn = Server.CreateObject("ADODB.connection") 
oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=http://export/caselle.csv;Extended Properties='text;HDR=Yes;FMT=Delimited'"

and I can read line from the CSV file with the query

Set RS=Server.CreateObject("ADODB.recordset")
RS.open "SELECT * FROM utenti_aospbo.csv", oConn 

now I can only read rs.fields(0), that output the entire line, like

CRDLVR47E67L781V#1653#CORDIOLI#ELVIRA#658#[e-mail]#SI

I'd like to have

response.write rs.fields(0) 'CRDLVR47E67L781V
response.write rs.fields(5) '[e-mail]

Answer 1

While I can't rule out that there is some version of OLEDB that does HTTP or accepts FMT and FORMAT in the connection string, I'm sure that the Data Source property of an ADODB Text connection needs to be a folder.

Instead of trying to specify a global separator in the connection string or the in the registry, I'd use a schema.ini file to describe the meta info in a file specific way.

All in one:

cscript 41224005.vbs
.\41224005.vbs
Option Explicit

Dim oFS  : Set oFS = CreateObject("Scripting.FileSystemObject")
Dim sF
For Each sF In Split(".\41224005.vbs ..\data\schema.ini ..\data\data1.txt")
    WScript.Echo sF
    WScript.Echo oFS.OpenTextFile(sF).ReadAll()
    WScript.Echo "---------------"
Next
Dim sDir : sDir    = oFS.GetAbsolutePathName("..\data\")
Dim sCS  : sCS     = Join(Array( _
                          "Provider=Microsoft.Jet.OLEDB.4.0" _
                        , "Data Source=" & sDir _
                        , "Extended Properties='" & Join(Array( _
                               "text" _
                           ), ";") & "'" _
                     ), ";")
Dim oDb  : Set oDb = CreateObject("ADODB.Connection")
oDb.Open sCS
'WScript.Echo oDb.ConnectionString
Dim oRS  : Set oRS = oDb.Execute("SELECT * FROM [data1.txt]")
WScript.Echo oRS.Fields(0).Value
WScript.Echo oRS.Fields(6).Value
oRS.Close
oDb.Close

---------------
..\data\schema.ini
[data1.txt]
FORMAT=Delimited(#)
ColNameHeader=False

---------------
..\data\data1.txt
CRDLVR47E67L781V#1653#CORDIOLI#ELVIRA#658#[e-mail]#SI

---------------
CRDLVR47E67L781V
SI