How to connect LOB text file data sources to native SharePoint lists via ODBC and BDLC

1. Run ODBC Manager 64-Bit (or 32-Bit on a 32-Bit SharePoint)
2. Run it as Administrator (otherwise no access to System DSN)
3. Create new System DSN
4. Add a new Data Source based on Microsoft Access Text Driver named “Textsample”
5. Select Options, specify *.txt
6. Disable “Use Current Directory”
7. Select your Directory
8. Define Format
9. Select your Text File, e.g. myfile.txt
10. Select Format Fixed Length
11. Define your Columns by Name (min 4 letters), Data Type and Width
12. Name one column Title to be the primary key later on (or use any other column for that)
13. 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:

1. Create a custom list and go to list settings
2. Select “Connect to External Data Source”
3. Select ODBC as Connection Provider
4. Enter “DSN=Textsample” without “  as Connection String and validate
5. Enter “select * from myfile.txt” without “ as select statement and validate
6. Show preview of your first lines and check
7. Correct ODBC configuration if required
8. Enter primary key Title and Validate
9. Add Columns
10. Check Mapping – All fields should be mapped to columns
11. Update – All items should be created
12. Update again – There should be no changes if primary key works
13. Enable Background updates if required (by default every hour)
14. Close with OK

Fig.: BDLC Settings to connect a text data source seamlessly to a SharePoint list

And thats the result list:

1. Display the result list
2. Update list manually or in background
3. write-back changes to data source (if supported by driver) 
4. Create views
5. Add additional columns and attachements if required
6. Rename column titles if required
7. Modify column types, e.g. text to numbers, long text etc. if required
8. Create subscriptions per RSS or E-Mail if required
9. Add business workflows on data change, if required
10. Use search feature as usual
11. Tag or auto-tag the content for use with Knowledge Management and  Social Networking
12. ... 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.