Split with Double quote text qualifier

[SQLScholar]

Hey all,

I cant believe i am the first person to want to do this - but i am struggling to find code to help.

I have some code which uses the split function - but unfortunately the data it is splitting is from a CSV that is text qualified with ".

It also needs to be text qualified becuase it contains " and needs to have them.

Does anyone have a function i can use instead of split to split this correctly? I cannot visualise an easy way to sort this, but i also cant imagine i am the first person with this issue

Any help much appreciated,

Dan

----------------------------------------
Be who you are and say what you feel, because those who mind don't matter and those who matter don't mind - Dr. Seuss

Computer Science is no more about computers than astronomy is about telescopes - EW Dijkstra
----------------------------------------

 

[guitarzan]

Not sure if this is the best way, but I open csv files that contain text inside double-quotes as a recordset, something like below; This way, the double-quotes around the data are removed and you should get the results you are looking for.

Dim sRootPath, sFileName
sRootPath = "C:\Path_to_CSV"
sFileName = "MyFile.csv"

Dim Conn, rs
Set Conn = CreateObject("ADODB.Connection")
Conn.Open _
 	"Driver={Microsoft Text Driver (*.txt; *.csv)};" & _
	"DBQ=" & sRootPath & _
	";Extensions=asc,csv,tab,txt;HDR=YES;Persist Security Info=False;" 

Set rs = CreateObject("ADODB.Recordset") 
rs.Open "SELECT * FROM " & sFileName, Conn
Do While Not rs.EOF
	'do stuff here

	rs.MoveNext
Loop
rs.Close

 

[wvdba]

you can try this one:
text_items = split(the_string, (chr(34))
part1 = text_items(0)
part2 = text_items(1)...... etc.