InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 25 May 2013 08:39:42 GMT20http://www.sqlservercentral.com/Forums/Topic854865-338-1.aspxThank you WayneThis was exactly what I needed to solve an issue I had with an SSIS package that had dynamic columns in a csv file.I needed to check for a columns existence and route accordingly.Every other solution I found involved complicated script tasks that didn't solve my problem directly.I used your sp_oa code in a execute sql task to see if a column existed in a csv file.Then i used the precedence constraint to route the package to use a different data flow with a different connection.Here is the code I used to check for the columns existencedeclare @FileName varchar(100)set @FileName = 'C:\myfile.csv'declare @oFSO int, @oTSO int, @line varchar(500)-- get the first line from the fileexecute sp_OACreate 'Scripting.FileSystemObject', @oFSO OUTPUTexecute sp_OAMethod @oFSO, 'OpenTextFile', @oTSO OUTPUT, @FileNameexecute sp_OAMethod @oTSO, 'ReadLine', @line OUTPUTexecute sp_OAMethod @oTSO, 'Close'execute sp_OADestroy @oTSOexecute sp_OADestroy @oFSO --SELECT @line IF PATINDEX('%ExtraColumn%',@line)> 0 SELECT CONVERT(BIT,1) AS ExtraColumnExists ELSE SELECT CONVERT(BIT,0)AS ExtraColumnExistsTue, 22 May 2012 00:17:54 GMTJason-368451http://www.sqlservercentral.com/Forums/Topic854865-338-1.aspxCheck this article out. It shows how to use opendatasource() function against text files. I cannot test this as I am running 64bit SQL Server 2008 and it has some issues per MS support site.[url=http://www.sqlservercentral.com/articles/OpenDataSource/61552/]http://www.sqlservercentral.com/articles/OpenDataSource/61552/[/url]In the example instead of tes1#txt, you should be able to use yourfile#csv. Also in the article it is setting HDR=NO. You probably should use HDR=YES. And "." in the file name is to be replaced with "#". This is because as we know period "." is special character in SQL server and used for 4 part object naming.Anyways try and see if this works.select * fromOpenDataSource('Microsoft.Jet.OLEDB.4.0', 'Data Source = C:\; Extended Properties = "Text;HDR=YES;"')...file#csvPlease let us know if this works.ThanksThu, 28 Jan 2010 23:23:24 GMTvstittehttp://www.sqlservercentral.com/Forums/Topic854865-338-1.aspxI haven't used OpenRowSet to open up a csv file before. You may need to use OpenDataSource instead.In the meantime, here's the sp_OA method:[code="sql"]declare @FileName varchar(100)set @FileName = '<Put your filename here>'declare @oFSO int, @oTSO int, @line varchar(500)-- get the first line from the fileexecute sp_OACreate 'Scripting.FileSystemObject', @oFSO OUTPUTexecute sp_OAMethod @oFSO, 'OpenTextFile', @oTSO OUTPUT, @FileNameexecute sp_OAMethod @oTSO, 'ReadLine', @line OUTPUTexecute sp_OAMethod @oTSO, 'Close'execute sp_OADestroy @oTSOexecute sp_OADestroy @oFSO;with CTE AS(select Col = substring(@line, Number, 1) from dbo.Numbers where Number <= len(@line))select ColumnCount = count(*) + 1, @line from CTE where Col = ','[/code]Thu, 28 Jan 2010 20:12:48 GMTWayneShttp://www.sqlservercentral.com/Forums/Topic854865-338-1.aspxThanks guys for ur quick reply.. but i get the following error code when i tried running the query [code="other"]OLE DB provider "MSDASQL" for linked server "(null)" returned message "[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified".Msg 7303, Level 16, State 1, Line 1Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "(null)".[/code]i just substitued my file name and my folder name in ur query.. am i missing something here?Wed, 27 Jan 2010 23:52:31 GMTColdCoffeehttp://www.sqlservercentral.com/Forums/Topic854865-338-1.aspxI prefer the SQL Wayne has printed. That is really cool and simple.Just refining his first query. The OpenRowSet will look something like this:[b]select * into #temp from openrowset('MSDASQL', 'driver={Microsoft Text Driver (*.txt; *.csv)}; defaultdir=C:\MyFolder;','select * from file.csv')[/b]where <file.csv> is your csv fileOn the road. I don't have my SQL instance up at the moment. But give it a try and see if it works. I will be able to test it tomorrow. Let us know if it works.Wed, 27 Jan 2010 22:40:00 GMTvstittehttp://www.sqlservercentral.com/Forums/Topic854865-338-1.aspxThe can think of of two ways:[code="sql"]select * into #temp from OpenRowSet(...) where 1=2select count(*) from tempdb.sys.columns where object_id = object_id('tempdb..#temp')drop table #temp[/code]and use sp_OA procedures to launching Scripting.FileSystemObject. Open the file, and read the first line. # columns = # of commas + 1.Wed, 27 Jan 2010 21:38:21 GMTWayneShttp://www.sqlservercentral.com/Forums/Topic854865-338-1.aspxHi people..Is it possible to Count number of columns in a CSV file from a T-SQL code? i think we can do that using Log Parser.. but from T-SQL code i guess OPENROWSET can do that, but i cant find how to do that??If u guyz know how, please post some sample code here :)Thanks in advance!!Wed, 27 Jan 2010 19:42:54 GMTColdCoffee