0

I am using MS query to pull data from sql server and all is good. Problem starts when data comes from the server I am stuck with data type general for everything, and no way to change the data type in excel. Main issue is numbers, where in database datatype is decimal yet i can do no calculations on it in excel. Any help would be appreciated.

I am using excel to execute a stored procedure on server enter image description here

This pulls the data into the following table enter image description here

Even though the data in the sql server for column price is formatted as decimal it becomes a general data type after getting to excel. Changing it to number/currency etc. does not change anything. Also no errors appear. Simply data comes down and no matter what changes in excel I apply nothing changes it all is treated as text.

ChefJ
  • 27
  • 7
  • have you tried exporting data from sql in an excel sheet? – Bilal Bin Zia Sep 17 '21 at 09:54
  • You can try this https://www.wikihow.com/Embed-a-SQL-Query-in-Microsoft-Excel – Bilal Bin Zia Sep 17 '21 at 09:57
  • @BilalBinZia that's essentially the method I'm using with slight variation but all in all that's the way. But i haven't tried to export query results as spreadsheet, only problem with that is need this to be dynamic. Exporting it every time someone want's to look at the data is not an option. – ChefJ Sep 17 '21 at 10:10
  • we had like more information about the problem. the error or exception problem can prove helpful in unerstanding the issue – Bilal Bin Zia Sep 17 '21 at 10:13
  • @BilalBinZia added some screenshots etc hope this explains the issue better. – ChefJ Sep 17 '21 at 10:49
  • 1
    Re "data in the sql server for column price is formatted as decimal" - is it text, formatted as decimal or does it have decimal data type? Then, what is your locale decimal separator? If it is comma, then Excel may just not understand dot as decimal separator. – Arvo Sep 17 '21 at 11:03
  • it is a decimal data type in the database. Just to clarify I have queried this same table multiple times before and this issue never occurred before. – ChefJ Sep 17 '21 at 11:28
  • @BilalBinZia I was able to use =VALUE(TRIM(CLEAN(A1))) formula to convert the data from string to number. As I am rather amateur in excel Is there a way this helps to solve my issue and convert all the required columns to numbers? – ChefJ Sep 17 '21 at 14:10

2 Answers2

0

You can do these things.

  1. Select Column
  2. Click Data-> Text to Columns
  3. Follow the wizard
  4. Set the format

Use this official support ticket from Microsoft

Bilal Bin Zia
  • 596
  • 3
  • 12
  • While I greatly appreciate the effort. If it was that basic I would not post the question here. I have already tried every regular method available in excel. So unfortunately this does not solve my issue. – ChefJ Sep 21 '21 at 07:26
0

Problem in this case was created by myself. But I suppose it could easily happen to others who are just starting on their path with sql and excel.

Here is what happened as I established after few days of going in circles. as there was load of trailing spaces in the data coming down from the server I have decided to tidy things up. Without considerring implications I have stuck an RTRIM() on everything. This caused excel to treat everything as strings as string RTRIM is a built in string function.

What made things worse is the fact that when using power query I was able to transform the data to the desired, formats.

Unfortunately MS query does not seem to be quite as clever as power query hence the issues.

ChefJ
  • 27
  • 7