January 27, 2010 at 7:42 pm
Hi 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!!
January 27, 2010 at 9:38 pm
The can think of of two ways:
select *
into #temp
from OpenRowSet(...)
where 1=2
select count(*)
from tempdb.sys.columns
where object_id = object_id('tempdb..#temp')
drop table #temp
and use sp_OA procedures to launching Scripting.FileSystemObject. Open the file, and read the first line. # columns = # of commas + 1.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
January 27, 2010 at 10:40 pm
I prefer the SQL Wayne has printed. That is really cool and simple.
Just refining his first query. The OpenRowSet will look something like this:
select * into #temp from openrowset('MSDASQL', 'driver={Microsoft Text Driver (*.txt; *.csv)}; defaultdir=C:\MyFolder;','select * from file.csv')
where <file.csv> is your csv file
On 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.
January 27, 2010 at 11:52 pm
Thanks guys for ur quick reply.. but i get the following error code when i tried running the query
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 1
Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "(null)".
i just substitued my file name and my folder name in ur query.. am i missing something here?
January 28, 2010 at 8:12 pm
I 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:
declare @FileName varchar(100)
set @FileName = '<Put your filename here>'
declare @oFSO int,
@oTSO int,
@line varchar(500)
-- get the first line from the file
execute sp_OACreate 'Scripting.FileSystemObject', @oFSO OUTPUT
execute sp_OAMethod @oFSO, 'OpenTextFile', @oTSO OUTPUT, @FileName
execute sp_OAMethod @oTSO, 'ReadLine', @line OUTPUT
execute sp_OAMethod @oTSO, 'Close'
execute sp_OADestroy @oTSO
execute 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 = ','
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
January 28, 2010 at 11:23 pm
Check 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.
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 *
from
OpenDataSource('Microsoft.Jet.OLEDB.4.0',
'Data Source = C:\;
Extended Properties = "Text;HDR=YES;"')...file#csv
Please let us know if this works.
Thanks
May 22, 2012 at 12:17 am
Thank you Wayne
This 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 existence
declare @FileName varchar(100)
set @FileName = 'C:\myfile.csv'
declare @oFSO int,
@oTSO int,
@line varchar(500)
-- get the first line from the file
execute sp_OACreate 'Scripting.FileSystemObject', @oFSO OUTPUT
execute sp_OAMethod @oFSO, 'OpenTextFile', @oTSO OUTPUT, @FileName
execute sp_OAMethod @oTSO, 'ReadLine', @line OUTPUT
execute sp_OAMethod @oTSO, 'Close'
execute sp_OADestroy @oTSO
execute sp_OADestroy @oFSO
--SELECT @line
IF PATINDEX('%ExtraColumn%',@line)> 0
SELECT CONVERT(BIT,1) AS ExtraColumnExists
ELSE
SELECT CONVERT(BIT,0)AS ExtraColumnExists
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy