CSV export escapement problem

[Bill Parker]

I need to do a CSV export to MySQL from DBF. The DBF memo fields contain anything you can imagine. I first created a CSV separated by ^ to avoid most character problems, but there were issues. Now I am trying to do a more traditional CSV.

If a text field contains a comma or quote, Alpha's export operation does not automatically escape them. I don't want to quote all character fields for other reasons. I am guessing "properties -> add filter rule" is supposed to be used, but I can't figure it out.

field1 = 123
field2 = my "quoted" text, with comma
field3 = plain text

desired csv = 123,"my ""quoted"" text, with comma",plain text

And this is the Alpha interface.
image.png

If I use an expression s_quote(","), then the entire field is replaced by ",", when what I want is the field to be quoted. And it could be any field.

Anyone used this?

Bill.

[Ronald Anusiewicz]

You do not say if the memo field is a field in the table or the memo field is in a stand alone child table linked to a parent table as Dr Wayne recommends. Just wondering if the later would help solve you problem.

[Bill Parker]

The memo field is in a separate table, but I am exporting from a mapped table that includes both - so it looks to Alpha like a single table.

[armasoft]

I can export quotes and commas to a csv if I use a semi-colon as the delimiter. No need for any filters. Result using your sample would be:

123;my "quoted" text, with comma;plain text

If you add a filter for quotes (") with the expression

quote(value,"'")

the output is:

123;'my "quoted" text, with comma';plain text

Neither of these plays nicely with newer versions of Excel as Microsoft insist the default separator is ',' for a csv file. LibreOffice Calc always prompts for values for delimiter when you try to open a csv which makes troubleshooting a lot simpler.

You don't mention what method you're using for the import to MySQL but it seems quite flexible as to what separators can be used.

[Bill Parker]

I made some progress, but not there yet. This is the xbasic view of an export operation with character filters.

a_tbl = table.current()
export.type = 0
export.names = .T.
export.file = "csvtest6.asc"
export.options = "LTS"
export.field_sep = ","
export.record_sep = "<CR><LF>"
export.string_filter_rules = <<%ftrul%
!,=quote(value)
/{cr}={CR}
/{lf}={LF}%ftrul%

This code changes carriage returns to {CR}{LF} which I can convert back to carriage return after sql import. If a field has an embedded comma, the field is escaped, and that is fine. Only if a field is escaped, any CRLF is converted instead to chr(13) +chr(10), which is only a problem if it is at the end of the field. In that case the import gets really screwed up. This occurs frequently and is not something that can be changed on the source side.

This is a test file.

CHAR1,CHAR2,MEMO1,NUM1
abc,bill,"simple memo, but it does have comma and ending CR"+chr(13)+chr(10),15
def,pipe | test,this has \ and cr{CR}{LF}to create second line.,
jkl,"comma, and double \"quote\"","various \"quoted\" specials and comma, ",6

The chr(13)+chr(10) in first line is what will cause a problem on import to MySQL, since it is at the end of the field. All would be OK if it came out as {CR}{LF}.

Bill.

[Bill Parker]

The road has continued to be winding. I had to ditch comma separated fields. Instead using ^ due to data issues. Below is the xbasic version of the operation, and the sequence of filter statements DOES make a difference. The remaining problem is that memo fields are truncated after export of 1024 characters. Not sure what is up with that?

a_tbl = table.current()
query.order = "Personid"
query.filter = ".t."
query.options = "I"
a_tbl.query_create()
export.type = 0
export.names = .T.
export.file = "sqlmaria_person.csv"
export.options = "LTSN"
export.field_sep = "^"
export.record_sep = "<CR><LF>"
export.string_filter_rules = <<%ftrul%
/{cr}={CR}
/{lf}={LF}
/\=\\
/^=\^%ftrul%​

Bill.

[Mike Wilson]

Hi Bill,
I have experienced these issues many times. I will give you my solution that has worked over the years. I don't try and export the data and fix the data in one event. I have used an intermediary temporary table to export the data, and fix the data in the temp table before moving the data to the final resting place. It sometimes requires an added field in the temp table to store the fixed data and the fixed data field is what is used moving forward. Just a thought for you.

[Bill Parker]

Thanks Mike. What worked for me was a mapped table in which I do all my conversions. For example, for date fields I define the mapped field as character and then set the value as chr(27) if the value is {}, otherwise yyyy-MM-dd.. The export converts the chr(27) to \N. The other lesson was to never specify an Enclosed By character for this particular set of horrible data.