FTD to excel

Message Topic Search Topic Options Post Reply Create New Topic Printable Version Translate Topic

   I consistenty battle exports from our system to Excel where as Excel does a translation of our information into date formats or other non-text formats.  This always introduces more steps to import the data using their wizard and force it to treat the column as text.  Is there any way to modify FTD to treat this data as text directly when exporting to an .xls file?

Author	Message Topic Search Topic Options Post Reply Create New Topic Printable Version Translate Topic
khess Members Profile Find Members Posts Newbie Joined: August 14 2006 Status: Offline Points: 4	Post Options Post Reply Quote khess Report Post Thanks(0) Quote Reply Topic: FTD to excel Posted: December 03 2007 at 3:37am
	I consistenty battle exports from our system to Excel where as Excel does a translation of our information into date formats or other non-text formats. This always introduces more steps to import the data using their wizard and force it to treat the column as text. Is there any way to modify FTD to treat this data as text directly when exporting to an .xls file?

CALIDORE Members Profile Find Members Posts Beta Tester Joined: May 10 2004 Location: United States Status: Offline Points: 139	Post Options Post Reply Quote CALIDORE Report Post Thanks(0) Quote Reply Posted: May 21 2009 at 8:26pm
	Is there a solution to this problem. When I export data that contains a date field, excel tries to convert it to american format. I end up with some dates in English format and some in US format (UK dates with a day <13 get formatted to US by excel everything else is ignored).
	KMW

PSchellenbach Members Profile Find Members Posts Admin Group Moderator Joined: December 15 2003 Location: United States Status: Offline Points: 2150	Post Options Post Reply Quote PSchellenbach Report Post Thanks(0) Quote Reply Posted: May 22 2009 at 3:34am
	Hi Calidore - AccuTerm uses Excel to import the data, so you would need to convice Excel that it needs to honor your locale settings. Here's a random idea: add a leading dummy record to your export that contains text in the date column - something that could not be confused with any numeric, time or date format that Excel might recognize. That should trick the file converter used by FTD to assume that the column is text and pass that hint to Excel when it imports the data. Thanks, Pete

Calidore_Ben Members Profile Find Members Posts Newbie Joined: November 12 2009 Location: United Kingdom Status: Offline Points: 6	Post Options Post Reply Quote Calidore_Ben Report Post Thanks(0) Quote Reply Posted: October 20 2011 at 4:39am
	We have followed your advice with this and unfortunately the problem persists. The first record that is exported contains column headers which are all text but excel still interprets some dates in UK format and others in US format (if the day > 12 it is formatted as US). Is there anything else to try?

wwf_admin Members Profile Find Members Posts Admin Group Joined: October 01 2003 Location: United States Status: Offline Points: 22	Post Options Post Reply Quote wwf_admin Report Post Thanks(0) Quote Reply Posted: October 27 2011 at 9:55am
	Hi Ben - The column headers are not used to determine data format when loading the data into Excel. Try adding another dummy record, after the column headers, that is pure text, and see if that causes Excel to treat the entire column as text. Thanks, Pete

Shrek59 Members Profile Find Members Posts Senior Member Joined: December 04 2006 Location: New Zealand Status: Offline Points: 208	Post Options Post Reply Quote Shrek59 Report Post Thanks(0) Quote Reply Posted: October 28 2011 at 1:07am
	Here is another option: Instead of passing the date as a date string, pass it as a number, and then format the Excel colum as a date. In general, an Excel date is a Pick date + 24837 so it is a pretty trivial matter to change the date when creating the csv output. HTH, Brian

Calidore_Ben Members Profile Find Members Posts Newbie Joined: November 12 2009 Location: United Kingdom Status: Offline Points: 6	Post Options Post Reply Quote Calidore_Ben Report Post Thanks(0) Quote Reply Posted: November 01 2011 at 2:20am
	Thanks I'll give it a try.

Calidore_Ben Members Profile Find Members Posts Newbie Joined: November 12 2009 Location: United Kingdom Status: Offline Points: 6	Post Options Post Reply Quote Calidore_Ben Report Post Thanks(0) Quote Reply Posted: November 14 2011 at 2:27am
	I've tried adding another dummy record but that hasn't changed the way excel is interpreting the data. As for the suggestion from Brian I'm afraid that our user base wouldn't accept a report where they had to reformat a number to a date. I've managed to do a workaround by creating a dictionary to use with the FTD Export that inserts an apostrophe before the date making excel interpret the cell as text.