SQL Server: Use a CSV as a linked server

Question

I need some help in configuring an SQL Server csv linked server. I already have an Excel linked server working without any problems, but each time I try to connect to my csv, I get varying error messages but the first is usually:

Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "CSV". (Microsoft SQL Server, Error: 7303)

To be clear, I'm looking to resolve the linked server issue that I'm currently facing, rather than consider alternatives such as Openrowset.

• SQL Server uses the source meta to define field data which is already a great help for data sets that can change structure and even data-type within defined row, which breaks most etl-tools.
• Much of the data I am loading includes comma-separated values within comma-delimited fields such as address data: 1234 Oak Tree Drive, Some Village This has a tendency to break data querying with OpenRowset and without going through the rigmarole to switch the BIG 15GB+ files to text just to negate this, linked servers can help get around this.
• And Openrowset does have some limitations such as it's 8000 character maximum limit (although we can get around this by using parameters and table variables etc

SQL Server Dev edition is installed locally to my Windows 11 OS

My environment:

• OS: Windows 11 Home (Microsoft Windows [Version 10.0.22621.1702])
• SQL Server: Microsoft SQL Server 2019 (RTM) - 15.0.2000.5 (X64) Sep 24 2019 13:48:23
• Provider: Microsoft.ACE.OLEDB.12.0 (Microsoft Access Database Engine 2010 14.0.7015.1000 With options set: Dynamic Parameter; Allow inprocess
• User account: Windows Authent

Excel wouldn't link without setting the two options, so I'd rather not adjust these.

Finally, my two linked server configs:

Excel

EXEC master.dbo.sp_addlinkedserver @server = N'EXCEL', @srvproduct=N'Excel', @provider=N'Microsoft.ACE.OLEDB.12.0', @datasrc=N'C:\datasources\excel.xlsx', @provstr=N'Excel 12.0'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'EXCEL',@useself=N'False',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL
GO
EXEC master.dbo.sp_serveroption @server=N'EXCEL', @optname=N'collation compatible', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'EXCEL', @optname=N'data access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'EXCEL', @optname=N'dist', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'EXCEL', @optname=N'pub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'EXCEL', @optname=N'rpc', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'EXCEL', @optname=N'rpc out', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'EXCEL', @optname=N'sub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'EXCEL', @optname=N'connect timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'EXCEL', @optname=N'collation name', @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=N'EXCEL', @optname=N'lazy schema validation', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'EXCEL', @optname=N'query timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'EXCEL', @optname=N'use remote collation', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'EXCEL', @optname=N'remote proc transaction promotion', @optvalue=N'true'
GO

CSV

EXEC master.dbo.sp_addlinkedserver @server = N'CSV', @srvproduct=N'CSVFLATFILE', @provider=N'Microsoft.ACE.OLEDB.12.0', @datasrc=N'c:\datasources\flat.csv', @provstr=N'Text;HDR=Yes'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'CSV',@useself=N'False',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL
GO
EXEC master.dbo.sp_serveroption @server=N'CSV', @optname=N'collation compatible', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'CSV', @optname=N'data access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'CSV', @optname=N'dist', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'CSV', @optname=N'pub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'CSV', @optname=N'rpc', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'CSV', @optname=N'rpc out', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'CSV', @optname=N'sub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'CSV', @optname=N'connect timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'CSV', @optname=N'collation name', @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=N'CSV', @optname=N'lazy schema validation', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'CSV', @optname=N'query timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'CSV', @optname=N'use remote collation', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'CSV', @optname=N'remote proc transaction promotion', @optvalue=N'true'
GO

Any ideas what I've missed? I know linked servers can be problematic in their setups, though this is the first time I've experienced such a problem.

Knowing that flat file linked servers use a near identical config as Excel, I've successfully configured this first, before scripting the server and updating to CSV and then TXT for a text-based server just in case. Excel is working as expected, but csv (and txt) aren't

Answer 1

This one was an obvious spot from @AlwaysLearning: CSV and text files are treated as tables from the same db, with the file-system folder being taken as the "DB". Therefore, instead of directing the linked server to the file like you would for Excel, this time, just connect to the folder, let the linked server do the rest.

Steve