Incorrect Format on SQL Data Import

Question

We're using Excel 2013 with PowerPivot/PowerView to connect to a SQL DB to import data before playing with it in Excel and using features such as Timeline. The problem is that some (but not all) users have an issue when they pull in the data, all fields are formatted as text instead of importing the format that is being used in SQL, notably date. The user will then have to go and manually change the format for each affected column each time they pull in data. This is a fresh install of Office 2013 64-bit on Windows 7 Pro x64.

We have a handful of machines that have the same hardware/software specs and only three of them are reporting this issue. We've had one of the affected users try it on another PC to verify the process and they were doing it correctly. To make sure it wasn't something odd with the AD profile, we've tested an AD profile on one machine and it worked then tested it on one of the three that were having problems and it didn't work. All the machines have the same Windows Updates applied (confirmed both manually and via our WSUS server). I've re-installed Office 2013 but the problem persists.

Just to run through the steps...

1. Open Excel.
2. Select PowerPivot tab.
3. Click Manage button (PowerPivot worksheet opens).
4. Click Get External Data > From Database > From SQL Server.
5. Type in server name.
6. We've tried both Windows Authentication and a DB account.
7. Select the appropriate Database.
8. Click Next button.
9. Leave the first radio labeled "Select from a list of tables and views to choose the data to import" selected and click Next button.
10. Select appropriate table and click Preview & Filter button (this is a faster way to check rather than importing data).

When it works, it shows the date column formatted as MM/DD/YYYY HH:MM:SS AM/PM. When it doesn't work, it shows it formatted as only YYYY/MM/DD and if you check the format of the column, it's "Text" and not "Date". Again, we have used the same AD account and credentials on two separate PCs and tried pulling the same data from the same table in the same database on the same server. So it doesn't appear to be a problem with the data or permissions but perhaps some setting in Excel that I'm overlooking. Any ideas?

Answer 1

Okay thanks. I've posted the question there as well. Link is below.

http://social.technet.microsoft.com/Forums/office/en-US/83783812-0ad0-464d-9a91-5ad249e1a603/incorrect-format-on-sql-data-import?forum=excel

Answer 2

Hello,

For issues related to PowerPivot to connect to a SQL DB to import data in Excel, your query would be better addressed in the Office Excel IT Pro forum link below:

http://social.technet.microsoft.com/Forums/en-US/excel/threads

For any other Office related issues, post in this Community and we are happy to assist.

Thank you.