[AccessD] Transfer Text Problem

To deal with quoted strings containing commas, I pass the line through a function like the 
following and then Split() on Tabs


Function TabLine(strLine As String) As String
'Changes a comma delimited line to a 
'tab delimited line to avoid problems with
'quoted text containing commas

Dim lngX As Long
Dim strChar As String
Dim flgInQuotes As Boolean

flgInQuotes = False
For lngX = 1 To Len(strLine)
  strChar = Mid$(strLine, lngX, 1)

  If strChar = Chr$(34) Then
     flgInQuotes = Not flgInQuotes
  End If

  If Not flgInQuotes And strChar = "," Then
  ' convert unquoted comma to tab 
     Mid$(strLine, lngX, 1) = Chr$(9)
  End If

Next
TabLine = strLine
End Function


On 24 May 2010 at 14:01, Rocky Smolin wrote:

> Well, since it is a CSV file TransferText worked a hell of a lot better than
> TransferSpreadsheet.  Substitutes # for .
> 
> I actually abandoned the Transfer approach and went straight code until I
> got to a record that had commas in a field and then Split on comma delimiter
> failed.
> 
> Went back to TransferText and all seems to be OK for the moment.  I'm sure
> there other gotchas here somewhere.
>