Long story short, I've had to use a workaround to importing data directly from SharePoint due to SSIS drivers not working with it. My current workaround is almost good enough, but it still has some issues. I started out with a regularly running report from SSRS that dumps into Excel 2003 files. Turns out sometimes data completely gets left off of certain columns when using the SQL Import Wizard, so I switched to.xlsx files.

The file is encrypted, I know the password, but I cannot pass it to QlikView in a regular LOAD - but I have heard from @flipside that it should be possible using an ODBC connection. That is not possible as yet - so i guess there is a driver missing or something. Unfortunately the guy from IT who was supposed. We receive the following message. SQL##f - SqlState: S1000, ErrorCode:, ErrorMsg: [Microsoft][ODBC Excel Driver] External table is not in the expected format. ODBC CONNECT32 TO [Excel Files;DBQ=D: migration_d klant Employee Dashboard Run Maart 2. Source data as used for.

Now the data, when the import actually works, shows up, but there's a unique quirk with this in the.xlsx files where the import wizard throws the 'External table not in expected format error' UNLESS the.xlsx files are manually opened, then immediately saved and closed. After that, the import works without issue. What could be causing this particular quirk, and is there any way to systematically get around it? If this were a one-time deal, I wouldn't be taking issue, but this data will have to be regularly refreshed. If it helps..

• the server this is on is a 2012 R2 Datacenter • running SQL 2014 • no Microsoft office programs are installed on this server, just drivers • SSRS is version 2012 (on a different server) There are two installations for Microsoft Office Access database engine; 2007 and 2010, which I needed to install to get the Excel -->SQL push to even work in the first place. Here are the possible reasons and solutions I can think of: • The Excel file instance may be getting opened in protected mode.

Opening, saving, and closing them is solving this issue temporarily. To fix it permanently, you need to add the Excel file location as a trusted one in the Trust Center. To do this, you need to go to excel>>file>>options>>Trust center>>trust center settings>>Trusted locations. And add the location of your Excel file there.

• Another thing that you can try is changing the file block settings in the same options window for Excel 2007, and later file types according to your needs. • The last reason that I can think of is Excel is unable to identify the type of value being inserted in the columns. This problem is resolved by entering some default sample values in the first row for each column. This workaround helps Excel in deciding what kind of value is getting entered and most of the time fixes the format issue.

For example: column1_has_to_get_integer_value then insert any integer value in first row. Column2_has_to_get_string_value then insert any string value in first row.

I have recently upgraded to Excel 2007 at my workplace, and I'm having trouble bringing in external data from Excel 2007. Here's what I do and the problem I'm having. Under the 'Data' menu, in the 'Get External Data' section, I select 'From Other Sources'. From the resultant menu, I select 'From Microsoft Query'. The 'Choose Data Source' window appears. 3d Игры Для Телефона Nokia.

Bhimbetka Cave Paintings Pdf Printer. I double click 'Excel Files*' from the selection list. This brings up a window titled 'Select Workbook' that allows me to browse my computer and network for Excel files to import.

In the bottom left corner of this window is a field called 'List Files of Type:' that has a drop-down box. The choices contained in the drop-down box are: 'Excel Files (*.xls)' and 'All Files (*.*)'. There is no option for.xlsx files. If I select 'All Files', of course I can choose an.xlsx file from the menu.

If I try this, however, I get the error message listed in the title box of this post. If I choose a.xls file, it works as expected...it only fails if I try to import data from.xlsx files.

This is a feature I frequently used in Excel 2003, so I'm very familiar with it. It appears to be a bug... I don't think MSQuery has been upgraded to accept Excel 2007 files.

I'm not an IT professional, but I've had help troubleshooting this from the IT guy here in my workplace. We've tried creating files in 2007 as well as converting them from 2003 to 2007. We get the same result either way. We've tried it on other computers with the same result. We've searched the web, and we haven't found another instance of this problem. He even reloaded Office for me, but nothing fixes it. I'm convinced (as I said), that MSQuery hasn't been upgraded to accept.xlsx files.

I've searched Any ideas?