Tom, Actually in this case the problem is that the text field for that row is larger than the 32,767 character limit. All of the text after that blows up the import until we start on the next valid row. I think the original replace string was working as intended There are only 11 cases where we have texts larger than 32,767 characters. I'll substring the output to be safe and we can manually update those 11 records to include the truncated text.
Chip Scheide wrote: Try Replace string([table]text_field;Char(34);"\\"+Char(34)) I'll do that if limiting the text field output to 32767 characters doesn't solve the problem. Thanks, Brad From: Bradley Perkins <bperk...@lanl.gov> Date: Monday, August 6, 2018 at 2:07 PM To: "Benedict, Tom" <tom.bened...@optum.com>, "4d_tech@lists.4d.com" <4d_tech@lists.4d.com> Subject: Re: Export text columns containing carriage returns to Excel? My initial tests using this technique look pretty good. I exported 100 rows and all but one imported into Excel OK. Most of the text fields don't contain any "s, but one that does caused the import to fail. How are you doubling the "s when using Replace String? I tried Replace string([table]text_field;Char(34);Char(34)+Char(34)) Thanks, Brad From: "Benedict, Tom" <tom.bened...@optum.com> Date: Monday, August 6, 2018 at 1:50 PM To: Bradley Perkins <bperk...@lanl.gov>, "4d_tech@lists.4d.com" <4d_tech@lists.4d.com> Subject: RE: Export text columns containing carriage returns to Excel? I didn’t have to escape the embedded commas. I think the quotes around all the fields took care of them. You will likely need to experiment. I found that Excel is fussy about CSV. It definitely doesn’t like importing CSV via the import wizard unless there are no embedded delimiters. Tom From: Perkins, Bradley D [mailto:bperk...@lanl.gov] Sent: Monday, August 06, 2018 12:34 PM To: Benedict, Tom; 4d_tech@lists.4d.com Subject: Re: Export text columns containing carriage returns to Excel? Tom, That sounds extremely useful. In addition to "" any "s in the text do I also need to \, any commas? I doubt the data source will be too large, but if it is can break the export into a couple of files. We'll only need to do this once. Thanks, Brad Perkins From: "Benedict, Tom" <tom.bened...@optum.com> Date: Monday, August 6, 2018 at 1:26 PM To: Bradley Perkins <bperk...@lanl.gov>, "4d_tech@lists.4d.com" <4d_tech@lists.4d.com> Subject: RE: Export text columns containing carriage returns to Excel? Brad, Here’s a hint that you may (or may not) find useful. I used a single column in the QR Editor to hold the full ‘record’ I was exporting. I wrapped all the fields with double-quotes and used commas as field delimiters. I also had to escape any embedded quotes by replacing them with double-double quotes. The result was a CSV file which can be opened in Excel (double-clicked on Desktop, not via File>Open. Excel is strange). Use CSV avoided the didn’t need to restore any of the embedded characters. My data source ended up being too large to use this approach, as the output file exceeded 4GB, so I end up writing similar code to generate the CSV. But if your data is modest sized the QR approach avoid the code-test-release cycle (unless your system is running interpreted). Tom Benedict Optum From: Perkins, Bradley D [mailto:bperk...@lanl.gov] Sent: Sunday, August 05, 2018 1:26 PM To: Benedict, Tom; 4d_tech@lists.4d.com Subject: Re: Export text columns containing carriage returns to Excel? Hi Tom, I've been working this with the QR Editor with some success. Another challenge I've found is that the 4D text fields have carriage returns, carriage return/new line pairs, and even some tabs. Therefore I'll need to nest the calls to Replace string in the proper order to place markers for all three. Thanks for pointing out the limitations I will need to see if any of the text fields are larger than 32,767 characters. This is an older system that started life in v3. The output file size will not be an issue. Best, Brad ________________________________ From: Benedict, Tom <tom.bened...@optum.com> Sent: Friday, August 3, 2018 2:19:44 PM To: Perkins, Bradley D; 4d_tech@lists.4d.com Subject: Re: Export text columns containing carriage returns to Excel? Brad Perkins writes: >I've been asked to provide an export from a legacy 4D system delivered as an >.xlsx file. >The challenge I'm facing is that this table contains a number of text fields >and >the customer requires the imported column cells to retain the returns or line >feeds >in the original text fields. If I do a straight tab-delimited export with >carriage returns >as the end of line marker, the imported result does not preserve the original >row >because any returns encountered in the text fields are interpreted as end of >row. >My other challenge is that I need to do this with out-of-box end user >capabilities >from a client (v15.4 remote) against a compiled server. I can't install >plugins, create new methods, etc. >It has been many years since I've had to do this type of thing in 4D. >Can I replace the carriage returns in the text fields with a special marker >during export? >I could replace the Excel cells containing the any carriage return markers >with actual returns after import. Use the Quick Report Editor. You can put formulas in the columns which include a call to Replace string. There are some limitation which I’ve encountered: - The Quick Report Editor is 32bit (evidently) which limits the size of the output file to 4GB. Hopefully you don’t run in to that limit. (I did recently). - Any given cell in Excel can only hold 32,767 characters. If you have more than that you’ll have to do some fancy segmenting into another column in the output. HTH, let me know if you have further questions. I have very recent experience doing just this. Tom Benedict Optum This e-mail, including attachments, may include confidential and/or proprietary information, and may be used only by the person or entity to which it is addressed. If the reader of this e-mail is not the intended recipient or his or her authorized agent, the reader is hereby notified that any dissemination, distribution or copying of this e-mail is prohibited. If you have received this e-mail in error, please notify the sender by replying to this message and delete this e-mail immediately. This e-mail, including attachments, may include confidential and/or proprietary information, and may be used only by the person or entity to which it is addressed. If the reader of this e-mail is not the intended recipient or his or her authorized agent, the reader is hereby notified that any dissemination, distribution or copying of this e-mail is prohibited. If you have received this e-mail in error, please notify the sender by replying to this message and delete this e-mail immediately. This e-mail, including attachments, may include confidential and/or proprietary information, and may be used only by the person or entity to which it is addressed. If the reader of this e-mail is not the intended recipient or his or her authorized agent, the reader is hereby notified that any dissemination, distribution or copying of this e-mail is prohibited. If you have received this e-mail in error, please notify the sender by replying to this message and delete this e-mail immediately. This e-mail, including attachments, may include confidential and/or proprietary information, and may be used only by the person or entity to which it is addressed. If the reader of this e-mail is not the intended recipient or his or her authorized agent, the reader is hereby notified that any dissemination, distribution or copying of this e-mail is prohibited. If you have received this e-mail in error, please notify the sender by replying to this message and delete this e-mail immediately. This e-mail, including attachments, may include confidential and/or proprietary information, and may be used only by the person or entity to which it is addressed. If the reader of this e-mail is not the intended recipient or his or her authorized agent, the reader is hereby notified that any dissemination, distribution or copying of this e-mail is prohibited. If you have received this e-mail in error, please notify the sender by replying to this message and delete this e-mail immediately. ********************************************************************** 4D Internet Users Group (4D iNUG) Archive: http://lists.4d.com/archives.html Options: https://lists.4d.com/mailman/options/4d_tech Unsub: mailto:4d_tech-unsubscr...@lists.4d.com **********************************************************************
