Count number of columns in a CSV file

Question

Post reply

Count number of columns in a CSV file

ColdCoffee

SSC-Dedicated

Points: 39972
More actions
January 27, 2010 at 7:42 pm

#209483

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!!

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

WayneS SSC Guru Points: 95460 More actions · Answer 1

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

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
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

vstitte SSCarpal Tunnel Points: 4475 More actions · Answer 2

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.

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 3

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?

WayneS SSC Guru Points: 95460 More actions · Answer 4

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

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
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

vstitte SSCarpal Tunnel Points: 4475 More actions · Answer 5

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

Jason-368451 SSC Enthusiast Points: 127 More actions · Answer 6

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