Exporting Double Quotes Within a Text File

Posted under features on November 19 , 2019 by Shubham Thakkar


When dealing with automatic messaging systems, we often have to use some conventions in our input files to signify the type of data we are entering into the system. For instance, text values need to be enclosed within double quotes to denote a string of characters. While using SheetKraft to generate the input files for such a system, we may have problems on generating double quotes due to absence of any escape sequence(s) / characters.

To generate the desired output file, we must go through the following steps:

  1. Generating visible double quotes
  2. Appending double quotes to the start and end of our character strings
  3. Exporting the data

1. Generating visible double quotes

As double quotes are used as an identifier for beginning and end of strings, they are not printed / shown readily in Excel unless kept solitary. However, if we require to show them in the resultant calculation, we can display them in various ways:

  1. """"
  2. '"
  3. CHAR(34)
  4. Inserting double quote character as a symbol

2. Appending double quotes to the start and end of our character strings:

To append the double quote character to the start & end of our strings, we can use a variety of techniques, few of which are mentioned here:

  1. CONCATENATE function
  2. & operator
  3. Using SheetKraft's JoinText.SK function

The CONCATENATE function and & operator work similarly in that, they simply append data provided to them so they can be used to add the double quotes generated within one cell to the start and end of any other cell. Usage example: Suppose our data exists in cells A4 and we have generated double quotes as calculation result in cell B1, we can use either of the following to reach the required result:

1. CONCATENATE($B$1,A4,$B$1)
2. $B$1&A4&$B$1

Whichever one we use, it can later be filled down using FillDown.SK. This is useful if we would like to encapsulate some data with double quotes. However, for a larger range, it is preferable to use JoinText.SK.

The JoinText.SK function combines the text with the given delimiter. This is useful for appending double quotes to encapsulate multiple columns. It is recommended to be wary of data within columns while using this function as the existence of delimiter character (being used in JoinText.SK) in column data can cause the function to go astray from the desired output.

3. Exporting the data

Depending on how we export the data, we might face either of the following scenarios:

  1. No double quotes exported
  2. Multiple double quotes (generally 3 for each one specified) generated within the output file.

To deal with this, we can concatenate data from all the desired columns within a single column and then export that particular column using Export To Text while selecting the as-is option to allow the data to maintain its form.



TAGGED:ExcelExportTextExport To Text


Let's talk

Address

7th floor, Unit No. 701, C Wing,
Kailas Business Park, S. Veer Savarkar Marg,
Park Site, Vikhroli (W), Mumbai-400079.

 

For General Queries & Technical Support

contactus@quantumphinance.com

 

For Sale and Demos

sales@quantumphinance.com