Highlighted

Yet another Excel question

LEGEND ,
Feb 23, 2018

Copy link to clipboard

Copied

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,

^ _ ^

Views

110

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more

Yet another Excel question

LEGEND ,
Feb 23, 2018

Copy link to clipboard

Copied

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,

^ _ ^

Views

111

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Feb 23, 2018 0
LEGEND ,
Feb 23, 2018

Copy link to clipboard

Copied

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,

^ _ ^

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Feb 23, 2018 0
Adobe Community Professional ,
Feb 23, 2018

Copy link to clipboard

Copied

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 (server troubleshooter, carehart.org)

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Feb 23, 2018 0
LEGEND ,
Feb 26, 2018

Copy link to clipboard

Copied

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,

^ _ ^

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Feb 26, 2018 0