In this article, we would explore all the lists and libraries export options in detail covering in which scenarios which particular setting should be used to get the desired result.
You will notice that we have a separate section "Global Settings" in the sidebar menu. This section provides you with a varied set of settings to configure the way you want your SharePoint data to be exported to SQL Server tables.
The settings are further divided into groups:
- Basic export options
- Advanced export options
- SQL table naming options
- Backward compatibility options
- Developer options
Let's explore these groups one by one starting with basic export options.
Basic export options: This section covers up settings related to the export of type of data ( images, attachments, folders).
- Export Binary Content: This setting is required to be set to if you intend to export the binary data of a document library or a picture library. By default, if you are exporting a document library SQList will only export the metadata of the document library. But if you need to download the files in addition to the metadata then you need to set this setting to true.an extra column Document_Bytes is used to store the information, the binary data for each item of the list.
For example, We have a picture library named Customers in SharePoint and w exported this library to SQL Server table without setting this property to true. In this case, it will export only the metadata about the image but not the actual image.
You can see in the above screenshot only the metadata about the library is exported but not the actual data. We have exported the same library after setting the "Export binary data" to true., then in addition to the metadata, the actual binary data is exported as well. You can notice we have an additional column "DocumentBytes" on the table. This column stores the binary data of the image.
- Export attachments or Export attachments' binary data: Both of these properties are related to each other closely. The setting "Export attachments" is required to be set to true if you want to export the attachments within the list. If your list consists of attachments, then by default SQList will not export them. But if you want them to be exported along then you need to set this property to true. It will create a new table which will store the id of the item and the URL of the attachment.
For example, we have a list "Products" which contains attachments. So, if export this list using SQList without setting any of the two settings to true then only the list Products will be exported without attachments. You can see in the screenshot below that only one table has been created in the SQL Server database for the list.
But if set the export attachments property to true then a new table is created in addition to the main table having the data related to the attachments.
As you can see in the screenshot above a new table "Products_Attachments" has been created to store data about the attachments of the list.
The setting "Export attachments" differs from "Export attachments' binary data" in only one way that it does not export the actual data of the attachment meaning the binary data of the attachment. It will export the metadata like the URL of the attachment. In order to export the actual attachment along with the metadata, you need to set the second setting to true. In such case, the attachments table will have an additional column "Document_Bytes" with actual data of the attachment.
Include Folders: By default, SQList does not export the content type of folders as part of the standard export. It only exports the items on the list. But if your list is organized in such a manner that it consists of folders, then you need to set the setting to true. In such a case, the contentType Column will be folder instead of being an item. Also, if the folder has any metadata associated with it, then it will be exported as well and will be exported as a separate column.
For example, one of the lists "Products" contains some folders and you want to export the content type of them as well. Then you can set this property to true and once you export it, you can see the SQL Server table contains folder as the content type and also the metadata if any.
Now let's move on to the second group of setting.
Advanced export options: This section covers all the settings related to the export of special columns or system columns from SharePoint list. By default, SQList does not export the system special columns in the list as part of the standard exporting procedure, because if it was exported the exported tables will have lot many columns. Moreover, these columns contain mainly internal links and values which would be of no use from reporting and application prospect.
- Export "base type" fields: if you require exporting the base type columns in SharePoint list then you need to set the setting to true. But be aware that the SharePoint lists contain numerous base type fields so you may end up having that many columns in the destination SQL Server table.
- Export computed fields: If the SharePoint list which is being exported has some calculated columns. Basically, if the SharePoint lists have some sort of formula applied for any calculation and you would like the calculated field to be exported as well then you need to set the setting to true.
- Export hidden fields: If the SharePoint list has some hidden fields as well and you would like them to be exported along with other items of the list then you need to set this setting to true.
- Export lookup columns' additional fields: If you have defined lookup(s) in the SharePoint list and also defined additional fields on the list for which lookup has been created, then you need to set this setting to true.
For example, we have a list on which we have defined a look up "lookup2". This lookup field gets the required information from the list "Test list 2". Also, we have specified the additional columns ID, Title, Modified, Created, Version of the lookup. Now if we require exporting these additional columns as well, then we need to set this setting to true.
You can see that additional fields are included in the destination SQL Server table as a part of the export.
- Export "Unknown" data type: If you require exporting fields with unknown data types as well, then you can set this setting to true.
Use "Display name" for column naming: SQList generates column names on the basis of the internal names of SharePoint list. But if column names do contain special characters like Chinese characters for example then SharePoint escapes the internal names of the list into numbers so using those names will be of no use as it will not be user-friendly.
For example, we have a list whose name consists of special characters like Chinese characters. We have set this setting to true and then exported the list, and you can see the special characters are exported as it is.
Now let's explore the third group of settings i.e. "SQL table naming options". This section covers up the settings involved in configuring the naming conventions of objects in SQL Server. The objects mainly are the tables and the columns in SQL Server.
- Clean table's and column's names: This property defines if SQList needs to remove any special characters or extra spaces in the names of the columns and tables of the database table. For example, if the name of the column in the list is “Street Name” then if the property is checked the name of the exported column will be “StreetName”. Here point to note is that it can result in duplicate columns. By default, the property is set to true.
- Do not prefix table names: This property defines if you require SQList to avoid using any prefix (mostly Site Name) to the database tables. By default, it is set to false, as SQList uses the specified separator between the names. For example, if the SharePoint connection name is Portal and the list name is Employees, then SQList will use '_' as the separator and will name the table as 'Portal_Employees'. So, if you require to change the separator and use some other character for any reason, maybe backward compatibility with previous version or something else, you can specify here.
- Never drop tables: This setting lets you command SQList to never drop any table even if you have changed the replication in any way. For example, you have set up a replication with two lists as part of the replication. Thus, two tables have been created for the corresponding lists in the SQL Server. Later you modify the replication and remove one of the lists from the replication, now you don't want that the table created earlier should be dropped from SQL Server. So you can set this to true.
Now let's jump onto the next group of settings, i.e. "Backward compatibility options". This section covers up the settings which are relevant to offer backward compatibility with the previous versions of the SQList.
By default, SQList uses the latest types while exporting but if you are using an older version of SQList and upgrading to new SQList version 6, you might not want to change the types of columns in your existing replications. Then you can use the provided options to have backward compatibility.
Now cover the last group of settings i.e. "Developer options".
The developer options should be handled with utmost care and should be considered as the last option.
- Always accepted fields for list: This setting lets you specify a comma delimited lists of fields which would be unanimously accepted by SQList while exporting the SharePoint lists. These setting will override any setting set elsewhere in other group and will find precedence over others. So, it should be set with utmost care.SQList copies the structure of SharePoint List to the structure of SQL table. It implies that if any new column is added to the SharePoint List, the same column will be added to the SQL table and Vice Versa. It is advised that you should not change the structure of the SQL table, but this situation is unavoidable in many cases where you may need to add column(s) to the table. Also, with SQL 2016 you have the concept of temporal tables around the corner; you may need to add two columns to the table. With this property, you can ask SQList not to drop the set of columns when SQList is synchronizing the structure of tables. This property lets you handle the extra column required by SQL server versioning and you can extend it further.
- Always accepted fields for document libraries: This setting is same as the above setting except it is applicable for document libraries instead of lists.