reading a csv - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

reading a csv

Author

Topic

richard102
Starting Member

25 Posts

Posted - 2010-12-07 : 09:01:09

hi all, I want to read a csv file and if it says 'success' to start a restore.

question - how can I read a csv file without importing it?

thanks

richard102

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-12-07 : 09:10:38

notepad?
You would need to import it into notepad though.
ditto excel, access, sql server, anything else.

Depends a bit on what you mean by import.

maybe
declare @t table (s varchar(100))
insert @t
exec master..xp_cmdshell 'type c:\mycsvfile.csv')
if exists (select * from @t where s like '%success%'
...

but that would be importing the file.
Thnk you need to distinguish between import and read.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-12-07 : 09:34:18

or use openrowset as shown in point 3
http://beyondrelational.com/blogs/madhivanan/archive/2010/03/17/bulk-insert-to-table-with-specific-columns.aspx

if (
SELECT 
	* 
FROM 
	OPENROWSET('Microsoft.Jet.OLEDB.4.0','text;HDR=NO;FMT=FixedLength;Database=g:\', test#csv)
) like '%success%'

assuming the file has a single column and single row. Otherwise you need to handle it differently

Madhivanan

Failing to plan is Planning to fail

richard102
Starting Member

25 Posts

Posted - 2010-12-07 : 09:41:47

Thanks fellas, the current solution (that works fine) has the one-line csv imported via SSIS into a table, in a database created especially for it.

With my DBA hat on - I'm appalled that a database has one table and that has just one row, just to use the value ('Success' or 'Error') in a sql-job.

Its unlikely that the customer will allow OPENROWSET.

thanks

richard102

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-12-07 : 09:47:09

I would leave it as it is.
To read data you should import it and it will be useful if it changes.
The only issue might be that ssis is quite resource hungry but if this doesn't happen very frequently then it's fine.

I would use bulk insert instead.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

richard102
Starting Member

25 Posts

Posted - 2010-12-07 : 09:53:55

Thanks Nigel, brr no to Bulk Insert (just thinking about identity-columns and format-files makes me shiver).

Looking at what Jobs can do - what about a command-prompt thing "findstr blah de blah".

thanks

richard102

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-12-07 : 10:01:38

Don't need an identity or format file.

can do a findstr but then you are geting into more complications and probably permissions.
You'll probably need to either create a bat file dynamicall or pass a parameter to it.

I think you should leave it as it is - you're spending more time than it's worth.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

richard102
Starting Member

25 Posts

Posted - 2010-12-08 : 05:47:12

FYI it was easy to add a step-1 'findstr "success" E:\somefolder\somefile.txt' using the default account (SQL-Agent). This step fails if the string isn't found and the job terminates.

Which allowed me to delete the 'control' database.

thanks

richard102

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources