need help with custom import specification

[NotGiven]

I have a CSV file that was exported from MySQL. I can customize the
delimiters on export from MySQL but it escapes apostrophes with a:
\

I have worked a while with the advanced tab of the import wizard but it
assumes a end-of-line is the the end of the record. The data contains
several fields with paragraph-like formatting, thus containing several
"end-of-lines" within one field.

Does anyone know a way around this? A way to truly customize the import
process?

Any help is much apprreciated!

 

[Joe Fallon]

Typically you would modify the file to meet the fairly simple requirements
of a true CSV file.
You would use a text editor program or Perl or something to tweak it.

If the wizard fails you can always write code.
But this might suffer from the same problem (extra embedded CRLFs).

Sample code:
Public Sub ImportFile(strPath As String)

Dim db As Database, rs As Recordset
Dim sLine As String, sTrimmed As String
Set db = CurrentDb
Set rs = db.OpenRecordset("TableName", dbOpenTable)

Open strPath For Input As #1

'Read a single line from an open sequential file and assign it to a String
variable.
Line Input #1, sLine
'Trim the leading blanks
sTrimmed = LTrim(sLine)

Do While Not EOF(1)
'read the next line of the file
Line Input #1, sLine
sTrimmed = LTrim(sLine)

'manipulate the string if necessary, then add it to the rs table.
If rs.BOF = True Then
rs.AddNew
Else
rs.Edit
End If
rs.Update
Loop
End Sub

 

[John Nurick]

Access can handle newlines within data in textfiles if (and I suspect
only if) it's a classic CSV file:
- fields separated by commas
- records separated by CR + LF
- any data that contains a comma or newline
is enclosed in quotes "..."
- newlines within data are represented by CR + LF

Quote marks within quoted data are not escaped but doubled "".