Yet another Excel question

[WolfShade]

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,

^ _ ^

[WolfShade]

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]

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

[WolfShade]

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,

^ _ ^