Universal Data Exporter PHP Support Product Page


Export to Excel problem with long 'number' text

Asked 21 Jun 2013 09:55:08
has this question
21 Jun 2013 09:55:08 David Woolley posted:
I'm using UDE to export a recordset to Excel. Everything works except that 'number' text like phone numbers are exported as scientific notation numbers when they have more than 10 digits. They can be reformatted in Excel but this is a bother for the client. Is there a way round this?

Phone numbers with a zero in front are being scrambled with a *minus* in front!

Perhaps the fields causing problems in the recordset could have a CONCAT_WS operator so that a single quote is appended onto these "numbers-that-are-text" fields. Bit of a long way round though.



Replied 24 Jun 2013 07:11:07
24 Jun 2013 07:11:07 Miroslav Zografski replied:
Hello David,

A way around this is providing the numbers as text - placed inside quotation. And that is the convenient way to handle it.

Replied 24 Jun 2013 08:07:22
24 Jun 2013 08:07:22 David Woolley replied:
Hi Miroslav

The numbers are all in MySQL varchar format
I've tried wrapping the fields in the recordset like so:
SELECT *, CONCAT('"', postcode, '"') AS postcodex , CONCAT('"', tel, '"') AS telx, ...etc

When I test the recordset in Dreamweaver it wraps the numbers in quotes e.g. "021 1234567" but if I test the page on my local dev server XAMPP, it breaks with an error:
Parse error: syntax error, unexpected '', postcode, '' (T_CONSTANT_ENCAPSED_STRING) ...etc

Replied 24 Jun 2013 11:25:19
24 Jun 2013 11:25:19 Miroslav Zografski replied:
Hello David, set one echo to check the exact query you are sending to the database.

Replied 24 Jun 2013 12:36:42
24 Jun 2013 12:36:42 David Woolley replied:
Hi - this works:
SELECT *, CONCAT('\"', postcode, '\"') AS postcodex , CONCAT('\"', tel, '\"') AS telx FROM applicants ORDER BY lastName ASC

Forgot to escape the double quotes!

All the 'number' text fields are now being exported to Excel correctly, but with the surrounding quotes. I suppose we can live with that.


Reply to this topic