Migration!

This blog has been migrated and merged at http://abrahamdybvig.blogspot.com/.

"The specified file is not a valid spreadsheet or contains no data to import"

Here's the problem:

You try to create a custom SharePoint list using the Import Spreadsheet function and get an error, "The specified file is not a valid spreadsheet or contains no data to import"

The problem is that IE doesn't have access to your file system, and in this instance, it needs it. The solution is to add the SharePoint site to your Trusted Sites zone.

Go to Tools / Internet Options

Click on the Security Tab, hightlight Trusted Sites and click the Sites button.

Enter the URL of the SharePoint site and click the Add button.

Close and Save your settings. You can now impor the spread sheet.

SharePoint View Filter order of precedence.

SharePoint Queries order of precedence.

WSS has the ability to build complex ad-hoc views of list data. But, configurators often run into strange results when mixing “and” and “or” operations in the filter section of the view.

Consider this example:

I want to create a view called “Overdue Tasks” which will display only those tasks that do not have a “Completed” status. I also want to limit the view to items with a due date that is either null or before today.

In a logical form, I’m looking for:
(Status <> Completed) AND ( (DueDate = null) OR (DueDate < Today) )

If I here to hand-write a CAML query (see http://msdn.microsoft.com/en-us/library/ms467521.aspx) it might look like this:

I used the following data set to make sure I configured the view correctly:

Given my logical conditions and the above dataset, I can expect to see two entries returned from the query: “Not Complete and Late” and “Not Complete no Due Date”

Initially, I tried to configure this query like so:

And got these incorrect results:

Reviewing examples above you can see that the order of precedence is important. I want WSS to consider the OR operation before considering the AND operation. It turns out the Edit View tool makes this simple if we remember a couple simple rules.

1. All logical operations occur from top to bottom.
2. Earlier logical operations are nested within later logical operations.

If I examine my logical form for the previous filter settings, I can immediately see my error.
( (Status = Completed) AND (Due Date = null) ) OR (Due Date = Today)

I need to restructure my logical form with the above rules in mind. Doing so produces this:
( (DueDate = null) OR (DueDate < Today) ) AND (Status <> Completed)

Which leads me to configure the view like this:

And get the expected results:

Indeed, pulling the CAML query returns the following:

Notice that the only real difference between the initial hand-written query and the WSS-generated one is the Neq clause moved from the beginning of the query to the end. The Neq clause is still nested within the And clause, but after the Or clause (which doesn’t cause any problems because the “And” operation is commutative. See http://en.wikipedia.org/wiki/Commutativity).

Happy view building!

~ED

Configuring FTP on Windows 2008

Earlier this week, I set up a new server with Windows 2008 x64 Enterprise to use with Windows SharePoint Services (WSS 3.0). In order to upload our custom solutions I had to enable FTP through IIS v6. Everything seemed straight forward. I could connect to FTP with appropriate credentials but could not list contents or upload new items.

The problem was that random ports used by FTP could not get through Windows Firewall. An article at http://support.microsoft.com/kb/832017 explains how FTP uses the default dynamic ports from #49152 to #65535.

One solution would be to manually open each and every port in the Windows Firewall exceptions. The tedium of this solution would surely lead to madness.

The solution I implemented was to limit the number of passive connections available, specify which ports are to be used, and allow those ports through the firewall.

In IIS v6 Manager, I brought up the properties for my FTP server.

I ensured that “Enable Direct Metabase Edit” is checked and clicked OK.

Next I ran a short script that designated which ports FTP Passive will use. The script also automatically added the ports to the Windows Firewall exceptions.

I pasted something like this script into a .bat file and ran it (I changed the port ranges to protect the innocent). It’s an adaptation of a script found at http://www.tino.nl/index.php/2008/12/19/passive-ftp-on-windows-server-2003-with-windows-firewall/

[begin code]
ECHO Udating FTP Registry Values
C:\Inetpub\AdminScripts\adsutil.vbs set /MSFTPSVC/PassivePortRange "42000-42199"
ECHO OPENING FIREWALL PORTS
FOR /L %%I IN (42000,1,42199) DO NETSH FIREWALL ADD PORTOPENING TCP %%I FTPPort%%I
iisreset
ECHO FINISHED
Pause
[end code]

Upon inspecting Windows Firewall, I confirmed that the ports had been correctly added and I was able to connect with an FTP client.

This process has some security benefits:
1. It allows you to designate a non-standard port for FTP if you wish
2. You can control exactly which FTP Passive ports to use, requiring fewer ports to be open through the firewall.

Happy FTPing!