Normal Topic How to keep blank lines exporting long text to CSV (Read 1871 times)
Rick_R
Full Member
***
Offline



Posts: 243
Joined: Jan 29th, 2010
How to keep blank lines exporting long text to CSV
Jan 24th, 2011 at 3:40am
Print Post Print Post  
Anyone know how to keep blank lines in a text field when exporting to CSV? We're using QA 4. We have 10 large "History" text fields that I need to export to other applications. The various dBASE, etc., formats all output fields as type CHAR, limited to 254 characters, so the only export type that works is CSV.

CSV export converts the entire field but removes control characters. The result is blank lines separating individual entries disappear, making a huge mess.

In other applications I have used the source application's @Replace type function to replace the EOL string with ^, then export to CSV. After the CSV is imported, you use search-and-replace in the destination application to replace the ^ with that application's EOL string.

I checked the DTF file and it uses CHR(255) and CHR(1) as end of line. Mass update for @chr(1) or @chr(255)+@chr(1) just deletes those, giving the same result QA CSV export gives (except, of course, now QA's text field has no blank lines). I tried replacing @chr(255)+@chr(1) with the same string and it replaced them with two smiley characters. I tried replacing those two with "^" plus those two and that didn't work either.

In cleaning up the data, I did replace all the double quotes with back-apostrophes, so my @Replace syntax is correct.
  
Back to top
 
IP Logged
 
Rick_R
Full Member
***
Offline



Posts: 243
Joined: Jan 29th, 2010
Re: How to keep blank lines exporting long text to
Reply #1 - Feb 1st, 2011 at 5:18am
Print Post Print Post  
This is sort of a half-way solution, but so far it's the only one I have found.

I tried Sesame's QA Translate and was very surprised at how simple it is. The only caution is that before the translation you MUST remove ALL double quotes or the CSV records will get screwed up.  That is a CSV "thing", not a QA or Sesame "thing".

This can be done using a Mass Update for each text box field.  (I am assuming short fields such as School won't have double quotes.)

In each field that needs to be sanitized use this as the update spec: #1=@Replace(#1,@chr(34),@chr(96))

The number after the number sign must be different for each field.  That will replace all double-quote characters with a back-apostrophe (the one under the tilde on a U.S. keyboard), which is normally unused.  If you really want to keep the double-quotes, you can run the same update in reverse in Sesame. It probably would be better, however, to replace the back-apostrophes with two regular apostrophes.

Then translate to Sesame.  Sesame preserves the blank lines.  Then export the records in CSV format from Sesame.  The difference is that QA simply removes the control characters that create blank lines but Sesame replaces them with \n.  Once the Sesame CSV records are imported into some other program (e.g., Excel), the \n can be converted to whatever that program uses as EOL.

Now that I have worked a bit with Sesame, I can see that it is MUCH easier than other database programs, such as FoxPro (that I am familiar with) and Access (which I flat-out gave up on after spending $163 on a bunch of manuals trying to figure out how to do some fairly simple programming.)  It looks, however, like I will only be updating parts of our QA application, which will result in bi-directional transfers between parts of the QA application that will be kept and Sesame replacements for others.  I don't know whether I will have to transfer large text fields back to QA but if so I don't know yet whether there is a way to @Replace the \n once the data is back in QA.
  
Back to top
 
IP Logged
 
Rick_R
Full Member
***
Offline



Posts: 243
Joined: Jan 29th, 2010
Re: How to keep blank lines exporting long text to CSV
Reply #2 - Nov 11th, 2011 at 5:39pm
Print Post Print Post  
I found this in the August 2001 issue of The Quick Answer

Q&A removes any hard carriage returns in a field exported to Standard ASCII

This might or might not be desirable. If your database contains one or more fields with lengthy text values that have been formatted with hard carriage returns (such as dated notes on follow-up calls), and you export that field, Q&A will remove the line breaks and you’ll wind up with a sloppy “running narrative” interspersed with double spaces or strings of spaces. This makes sense in that Q&A always uses a carriage return as the end-of-record delimiter. But isn’t it interesting, then, that you can also select a carriage return as a field delimiter? If you do, each exported field will print in the export file on it’s own line, followed by two carriage returns at the end of each record.

(John Dow’s DTFDATA has an option that extracts Q&A data to a file and inserts a placeholder character for any carriage returns found, on the assumption that you’ll be able to turn those placeholders back into carriage returns in the target program.)

============
Of course, you would then have to take the text file and manually process it by converting the double hard returns to a temp placeholder, then converting the single hard returns, then converting the placeholder to a single return.  (Importing to Sesame and then exporting from that is definitely easier, even if Sesame isn't going to be the ultimate target.)
  
Back to top
 
IP Logged