Had to solve one of those tricky little problems today... a user uploading a CSV and splitting the fields into a database.
Played around with the ADO CSV driver but... it's very picky about the quality of the data and ended up being harder work than a simple CSV import function should be...
A quick search (Google is easier than thinking after all) revealed a bunch of regex to handle csv input... the only trouble being that most of the solutions where partial...
So... here's my contribution... handles CSVs and copes with quoted items.
1: <%
2: ' Split a line of a CSV into array cells
3: ' support quoted (") entries with internal commas
4: ' Hybrid ASP.NET (VB) Solution
5:
6: dim csvRegEx As Regex = New RegEx(",(?=(?:[^\""]*\""[^\""]*\"")*(?![^\""]*\""))")
7: dim source_str
8: dim csvArray, loopArray
9:
10: ' load source_str - this can be from a text file, or an uploaded file
11:
12: csvArray = csvRegEx.Split(source_str)
13: ' Strip off quotes (if the entry was quoted)
14: For loopArray = 0 To UBound(csvArray)
15: if csvArray(loopArray) <> "" Then
16: If Left(csvArray(loopArray),1) = Chr(34) And Right(csvArray(loopArray),1) = Chr(34) Then
17: csvArray(loopArray) = trim(Mid(csvArray(loopArray),2,Len(csvArray(loopArray))-2))
18: End if
19: End if
20: Next
21: %> |