in what format can I write a flat file so it is easily imported by MS Access? delimiting field & re

  • Thread starter Thread starter NotGiven
  • Start date Start date
N

NotGiven

I have tried everything except writing the MySQL database data to a flat
file using custom code so I can add whatever field & record
delimiters/terminators I choose.

I need to accommodate for fields containing carriage returns

What field and record terminators would work?
 
I use | delimiters myself.
Others are common: ; , ~ : Tab

Each record in the ASCII file has to end with vbCRLF which is also known as
Chr(13) & Chr(10).
I am pretty sure you can't have those characters in your data so you may
need to replace them with just Chr(10).
 
If there are linebreaks in the data, the only text file format that
seems to work reliably is "CSV" with the following rules:

1) Use CR + LF (Chr(13) & Chr(10)) to separate records.

2) Use , (comma) to separate fields.

3) If a field contains comma, CR, LF (or, I think, other control
characters) it *must* be delimited with quotes, e.g.
"John, Patrick and Simon"

4) Quote marks within a field delimited by quotes must be doubled, e.g.
"He said, ""Goodbye,"" and died."

5) To display linebreaks within data in Access textboxes, use Chr(13) &
Chr(10) for newlines within the data, not just CR or LF.

6) Any text field *may* be delimited with quotes.
 
Back
Top