Web Query Formatting not ready for Production
 

I do web queries from several internal systems and I continually run into
problems where the returned data is formatted to Microsoft's wishes instead
of my formatting requirements. 2 Sample a

1) one column is cage code, a 5 digit alphanumeric number (sometimes
starting with a zero). System want to remove the first zero even if I set
the column to text.
2) Part Numbers column comes in all shapes and formats as a string, some
starting with multiple zeros, some having all numbers with "E" in the middle,
etc. When importing, even if column set to text, web query replaces filed
with scientific notation and/or removes leading zeros.

How can I force all returned rows to be formatted as returned and not
conferted. Cannot change source data - (that is not the problem). And/or
Need a setting that when I say everything is text, everthing is text.