You are viewing limited content. For full access, please sign in.

Question

Question

Free-text fields causing problems with field delimiters when exporting to Excel

asked on January 6, 2016

Good afternoon, all.

We often get requests to view large amounts of submitted Forms content as an Excel file; our usual method of doing this is to insert the Forms content into metadata templates within a repository, then use Export -> List Contents in Laserfiche Web Access to generate the file.

However, we're encountering a problem with longer free-text fields:

If the submitter enters a comma into such a field, it will count as a field delimiter when exporting the list contents if "comma" is selected as the delimiter character, "breaking" the resulting Excel.  Selecting Tab as the delimiter prevents this with shorter free-text fields.

Unfortunately, if the free-text field is longer (e.g. a multi-line Forms input), we find that a paragraph break is interpreted during export as a Tab character...again "breaking" the resulting Excel file.

Is there any way to prevent line/paragraph breaks from being interpreted as Tab characters when performing an export of this kind? 

0 0

Replies

replied on January 7, 2016

When export list of contents from Web Access, it will export as CSV file and comma and line break in the text will cause problem. There seems no solution for this. You can use the download function from Forms results page instead to download form field variable values as XLSX file type instead.

0 0
replied on January 8, 2016


Lynette - It is a bit of a long shot but you could try 'massaging' the text using a workflow Script activity to replace the offending characters in a text string before you export the text.  You might have to call String.Replace a couple of times to remove the offending characters.  The horizontal tab (HT) is an ASCII 9, vertical tab (VT) is an ASCII 11, line feed (LF) is ASCII 10 and a carriage return (CR) is ASCII 13.  I would try replacing them with the space character and see how that impacts the export to CSV.  In retrospect you might even be able to massage the text using a regex expression?

0 0
replied on January 8, 2016

Commas shouldn't be a problem because Web Access quotes any strings with commas. I can open one of these CSVs in excel but i have to go through the "Text Import Wizard" and choose the correct delimiter.

Newlines are more of a problem, Web Access 10 just strips them out. I don't see how it could be converting them to tab characters.

0 0
You are not allowed to follow up in this post.

Sign in to reply to this post.