2
0
When you create an Excel Web Query that points to a text file located in a web server, Excel doesn't ask for the decimal separator as it commonly does when using the Import Text File or Text to Columns functions. It correctly treats the data as text and splits the columns, but if it gets the decimal separator wrong, you can't fix it with formulas (because it discards the rightmost zeros).
Is it possible to specify the decimal separator on Excel refreshable web queries? I guess the answer is no, but I'd rather be sure.
Did you try using Get External-From Text, and specifying delimiters there? It should be able to target a web URL. – Madball73 – 2014-03-19T13:30:05.250
When you do that Excel downloads a copy of the URL to %TEMP%. Afterwards, if you try to refresh the query an Open File dialog comes up asking you to refresh the link by pointing to the source file. I entered once more the URL and it doesn't show the Text to Columns Wizard, it seems it stores that config. It might work if I put the URL right next to the query and manually enter it each time I refresh the query. You could put this as an answer so that it can be accepted. – s_a – 2014-03-19T15:32:20.110