Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Count number of columns in a CSV file Expand / Collapse
Author
Message
Posted Wednesday, January 27, 2010 7:42 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, December 11, 2014 5:03 PM
Points: 2,262, Visits: 5,427
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!!
Post #854865
Posted Wednesday, January 27, 2010 9:38 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:58 AM
Points: 5,370, Visits: 9,010
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
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links: For better assistance in answering your questions, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings
Post #854892
Posted Wednesday, January 27, 2010 10:40 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, January 18, 2012 7:43 AM
Points: 351, Visits: 406
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.



Post #854913
Posted Wednesday, January 27, 2010 11:52 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, December 11, 2014 5:03 PM
Points: 2,262, Visits: 5,427
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?
Post #854947
Posted Thursday, January 28, 2010 8:12 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:58 AM
Points: 5,370, Visits: 9,010
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
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links: For better assistance in answering your questions, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings
Post #855730
Posted Thursday, January 28, 2010 11:23 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, January 18, 2012 7:43 AM
Points: 351, Visits: 406
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/

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



Post #855783
Posted Tuesday, May 22, 2012 12:17 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, October 9, 2014 12:54 PM
Points: 5, Visits: 344
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
Post #1303881
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse