Skip to main content
WolfShade
WolfShade
Legend
February 23, 2018
Question

Yet another Excel question

  • February 23, 2018
  • 1 reply
  • 301 views

Hello, all,

I have a form that when submitted to CF will perform validation, then insert all the information into 2 or 3 (depending) sheets in Excel, drop it into an email and send it off.

The recipient of the email then puts focus on one of the sheets in Excel and does a "Save As.." into a tab-delimited txt file.  Easy peasy, right?

The recipient then takes that txt file and submits it to ACT (I have no idea what ACT is, but I'm guessing it's another application that I don't have access to.)

One minor problem, though, is that a few of the columns in the txt file have all associated values inside double quotes, which is screwing up inserting the data into ACT.

Here is a txt file example:

BOOKING     TYPE     ORIGIN     DESTINATION

D2D     DOD     "Charleston, SC"     "Destin, FL"

D2D     NDOD     "St. Louis, MO"     "Chicago, IL"

P2P     FMS     "Jupiter, FL"     "Los Angeles, CA"

Any thoughts on why Excel is encasing some values in double quotes?

V/r,

^ _ ^

    This topic has been closed for replies.

    1 reply

    WolfShade
    WolfShadeAuthor
    Legend
    February 23, 2018

    I may have just figured it out, by trial-and-error.

    Comma.  It appears as though saving to a tab-delimited txt file is very similar to saving as a comma-separated-values csv file, and Excel is inserting the value into double quotes as a way of preventing any application from thinking that the comma delineates the end of a value.

    I created an Excel file, filled some cells with text, and some cells with text containing a comma.

    Then, I did a "Save As.." to a tab-delimited txt file.  The txt file had double quotes around the values that contained a comma, and none of the values that didn't.

    So, it's not a scientific approach, but I am assuming that commas are the culprit.

    V/r,

    ^ _ ^

    Charlie Arehart
    Community Expert
    Charlie ArehartCommunity Expert
    Community Expert
    February 23, 2018

    I suspect if you formatted the columns in Excel (or in CFML building the XML) to indicate if a column was a string, it would then put the double quotes around all values in that column.

    /charlie

    /Charlie (troubleshooter, carehart. org)
    WolfShade
    WolfShadeAuthor
    Legend
    February 26, 2018

    Hi, Charlie,

    I am not formatting the type, which I believe defaults to "General".  Right-click the associated cell in the Excel and selecting "Format cells.." shows that it is General.  Same thing for the columns that aren't getting the double quotes.  As far as I can tell, it's just fields that have value that contains a comma.  So far, that's the only common denominator.

    V/r,

    ^ _ ^

    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices