try: Replace string([table]text_field;Char(34);"\\"+Char(34))
On Mon, 6 Aug 2018 20:07:39 +0000, Perkins, Bradley D via 4D_Tech wrote: > 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" <[email protected]> > Date: Monday, August 6, 2018 at 1:50 PM > To: Bradley Perkins <[email protected]>, "[email protected]" > <[email protected]> > 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:[email protected]] > Sent: Monday, August 06, 2018 12:34 PM > To: Benedict, Tom; [email protected] > 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" <[email protected]> > Date: Monday, August 6, 2018 at 1:26 PM > To: Bradley Perkins <[email protected]>, "[email protected]" > <[email protected]> > 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:[email protected]] > Sent: Sunday, August 05, 2018 1:26 PM > To: Benedict, Tom; [email protected] > 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 <[email protected]> > Sent: Friday, August 3, 2018 2:19:44 PM > To: Perkins, Bradley D; [email protected] > 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:[email protected] > ********************************************************************** --------------- Gas is for washing parts Alcohol is for drinkin' Nitromethane is for racing ********************************************************************** 4D Internet Users Group (4D iNUG) Archive: http://lists.4d.com/archives.html Options: https://lists.4d.com/mailman/options/4d_tech Unsub: mailto:[email protected] **********************************************************************
