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:

 

Sample Text File to integrate into SharePoint List

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 

 

ODBC Settings to integrate a text file into SharePoint list

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 

 

BDLC Settings to integrate a text file into SharePoint list

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 ;-) ...  

 

Text file as native SharePoint List

 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 SharePoint 2007 or 2013 / 2016. The Business Data List Connector for SharePoint shareware you find here.

READY TO GO NEXT STEPS?

Register for free download.

Keep your Sharepoint in sync. Download and try today.

Questions? Contact us.

We are here to help. Contact us and our consulting will be happy to answer your questions.