Sometimes a direct connection to any system is not available. In this case you can export text files and pull this data into a SharePoint list on a regular base, e.g. once a day. If a primary key is available in your text file, then changed data is updated only.
Here is our sample data source:
Fig.: Sample text file with a text column and some quantity at fixed positions.
First you have to configure your ODBC data source:
- Run ODBC Manager 64-Bit (or 32-Bit on a 32-Bit SharePoint)
- Run it as Administrator (otherwise no access to System DSN)
- Create new System DSN
- Add a new Data Source based on Microsoft Access Text Driver named “Textsample”
- Select Options, specify *.txt
- Disable “Use Current Directory”
- Select your Directory
- Define Format
- Select your Text File, e.g. myfile.txt
- Select Format Fixed Length
- Define your Columns by Name (min 4 letters), Data Type and Width
- Name one column Title to be the primary key later on (or use any other column for that)
- Close all Dialogs
Fig.: ODBC settings to define a text data source with fixed column length
Then you can access this data source in the BDLC Settings Dialog:
- Create a custom list and go to list settings
- Select “Connect to External Data Source”
- Select ODBC as Connection Provider
- Enter “DSN=Textsample” without “ as Connection String and validate
- Enter “select * from myfile.txt” without “ as select statement and validate
- Show preview of your first lines and check
- Correct ODBC configuration if required
- Enter primary key Title and Validate
- Add Columns
- Check Mapping – All fields should be mapped to columns
- Update – All items should be created
- Update again – There should be no changes if primary key works
- Enable Background updates if required (by default every hour)
- Close with OK
Fig.: BDLC Settings to connect a text data source seamlessly to a SharePoint list
And thats the result list:
- Display the result list
- Update list manually or in background
- write-back changes to data source (if supported by driver)
- Create views
- Add additional columns and attachements if required
- Rename column titles if required
- Modify column types, e.g. text to numbers, long text etc. if required
- Create subscriptions per RSS or E-Mail if required
- Add business workflows on data change, if required
- Use search feature as usual
- Tag or auto-tag the content for use with Knowledge Management and Social Networking
- ... Just do ALL what you can do with native SharePoint lists ;-) ...
Fig.: That's the resulting native SharePoint list - connected to a text file via BDLC
This sample is with SharePoint 2010. You can do the same with WSS 3.0 / 4.0 or MOSS 2007. The Business Data List Connector for SharePoint 2007 und 2010 shareware you find here.