Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Count number of columns in a CSV file


Count number of columns in a CSV file

Author
Message
ColdCoffee
ColdCoffee
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2317 Visits: 5545
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 Smile

Thanks in advance!!
WayneS
WayneS
SSCertifiable
SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)

Group: General Forum Members
Points: 6230 Visits: 10398
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, 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

vstitte
vstitte
Old Hand
Old Hand (351 reputation)Old Hand (351 reputation)Old Hand (351 reputation)Old Hand (351 reputation)Old Hand (351 reputation)Old Hand (351 reputation)Old Hand (351 reputation)Old Hand (351 reputation)

Group: General Forum Members
Points: 351 Visits: 407
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
ColdCoffee
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2317 Visits: 5545
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
WayneS
SSCertifiable
SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)

Group: General Forum Members
Points: 6230 Visits: 10398
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, 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

vstitte
vstitte
Old Hand
Old Hand (351 reputation)Old Hand (351 reputation)Old Hand (351 reputation)Old Hand (351 reputation)Old Hand (351 reputation)Old Hand (351 reputation)Old Hand (351 reputation)Old Hand (351 reputation)

Group: General Forum Members
Points: 351 Visits: 407
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



Jason-368451
Jason-368451
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 347
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search