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

The Business Layer2 Data List Connector for SharePoint (BDLC) can be used to connect SharePoint to 100+ external data sources, even to text / csv files via ODBC. This could be especially helpful in case your external system does not allow direct connections or you want to connect more complex query results. Please also note the option to connect via XML.​​​​​

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 and screenshot is with SharePoint 2010. You can do the same with all newer SharePoint Editions: 2010, 2013, 2016, 2019 and 2022. The Business Data List Connector for SharePoint shareware is free for download and evaluation here.

READY TO GO NEXT STEPS?