4

The Situation

I have a button that call http://mywebsite.com/subsite/_vti_bin/owssvr.dll?CS=109&Using=_layouts/query.iqy&List={LIST_ID_REMOVED}&View={VIEW_ID_REMOVED}&CacheControl=1

In the SharePoint 2007 View itself, I only have 2 columns - Title and Description. However, when I click the button, it will open Excel with 2 more extra columns - Item Type and Path.

Question

What should I do to the link such that when I click the button, it only show the 2 columns - Title and Description rather than 4 columns (which include Item Type and Path column)

Larry Morries
  • 1,047
  • 1
  • 14
  • 42

2 Answers2

3

Follow these steps:

1) Create a new file in notepad and copy in it:

http://mywebsite.com/subsite/_vti_bin/owssvr.dll?XMLDATA=1&List={LIST_ID_REMOVED}&View={VIEW_ID_REMOVED}

2) Save the file as owssvr.iqy (please note the extension)

3) Upload this file to a document library and use its link in the button instead.

Amit Kumawat
  • 6,689
  • 2
  • 22
  • 33
  • I tried the same but it is giving me the error, "data could not be retrieved from the database check the database server or contact your database administrator. Make sure external database is available, and then try operation again. Sharepoint list export error" – Niranjan Kulkarni Jul 23 '15 at 09:43
  • How can i implement the same for sharepoint 2013 list – Niranjan Kulkarni Jul 23 '15 at 14:30
0

The way Sharepoint defines the content of the .iqy file is the below:

WEB
1
${base}/_vti_bin/owssvr.dll?XMLDATA=1&List=${params.List}&View=${params.View}&RowLimit=0&RootFolder=${encodeURIComponent(params.RootFolder)}

Selection=${params.List}-${params.View}
EditWebPage=
Formatting=None
PreFormattedTextToColumns=True
ConsecutiveDelimitersAsOne=True
SingleBlockTextImport=False
DisableDateRecognition=False
DisableRedirections=False
SharePointApplication=${base}/_vti_bin
SharePointListView=${params.View}
SharePointListName=${params.List}
RootFolder=${params.RootFolder}

I tried to change several of those parameters, and the only way I found to remove columns path and item type is to remove the last 4 lines:

// to remove
SharePointApplication=${base}/_vti_bin
SharePointListView=${params.View}
SharePointListName=${params.List}
RootFolder=${params.RootFolder}

However, by doing it, the columns are not formatted anymore. For example, a people picker columns will return ID;#Name instead of just the Name.

On the web we can find various parameters to add in the URL but none of them as produced the correct format.

To make the things easy to deal with, and to remove the path and item type, and if you don't have any lookup column, then the best approach is to use the below URL provided by Amit:
http://mywebsite.com/subsite/_vti_bin/owssvr.dll?XMLDATA=1&List={LIST_ID_REMOVED}&View={VIEW_ID_REMOVED}

After additional research, I found a Microsoft Doc about the parameters in the URL: https://docs.microsoft.com/en-us/previous-versions/office/developer/sharepoint-2010/ms478653(v=office.14)

The columns item type and path are probably automatically added by Excel because it gets a XML feed.

AymKdn
  • 1,189
  • 8
  • 20