grabbing a csv from a csv file

I created this code, but haven't tested the values with a schema.ini file yet. My old programming co-worker had this working quite well.

----

I typically use a ssis package with a credential (previous blog) to do this, but my friend does it this way .. well, he is testing it.
He is having trouble initiating with a schema.ini file..

EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
EXEC
sp_configure 'Ad Hoc Distributed Queries', 1
GO
RECONFIGURE with
override
GO
IF OBJECT_ID('#MyTable') IS NOT NULL
BEGIN
DROP TABLE
#MyTable
END
SELECT *
INTO #MyTable
FROM
OPENDATASOURCE(
'Microsoft.Jet.OLEDB.4.0'
,'Data
Source="C:\Event_Leads\G2WAttendee_02125312";Extended
Properties="text;HDR=No;FMT=Delimited";'
)...[G2WAttendee#csv]
Select *
from #MyTable
DROP TABLE #MyTable
EXEC sp_configure 'Ad Hoc Distributed
Queries', 0
GO
RECONFIGURE with override
GO
EXEC sp_configure 'show
advanced options', 0
GO
RECONFIGURE
GO

Presenting a "Pretty" date

I typically date my items as

01/15/2008 for January 15, 2008.

Here is a pretty way of transforming this:

SELECT , CONVERT(VARCHAR(12), ExpDate, 1) AS Expires

-- create a #session table so it will be cleaned up when
you close out of the window
INTO [#Oneyear]
FROM Mytbl

Try Catch with RollBack

BEGIN
TRY
BEGIN TRANSACTION
-- Update blah blah blah
-- If we got to this point, we are good
COMMIT

END TRY

BEGIN

CATCH
-- Thank you GuysFromRolla for this code.

IF @@TRANCOUNT > 0
ROLLBACK

-- Raise an error with the details of the exception.

--note: * I have heard ASP.NET will give the Raiserror message...

DECLARE @ErrMsg nvarchar(4000), @ErrSeverity int
SELECT @ErrMsg = ERROR_MESSAGE(),@ErrSeverity = ERROR_SEVERITY()
RAISERROR(@ErrMsg, @ErrSeverity, 1)

END CATCH

END

OPENROWSET for Excel File.

1. Create a Linked Server
   - Server Objects --> Linked Servers --> Right-Click --> New Linked Server...
   - name the linked server MSDASQL
   - Provider is: Microsoft Jet 4.0 OLD DB Provider
   - Product Name: Jet 4.0
   - Data Source is the location of your excel file (include the file name? it has been a while...)
   - Provider String: Excel 8.0

Then if you run this:

SELECT *
INTO tmpRaw_tbl
FROM
OPENROWSET('MSDASQL', 'Driver=Microsoft Excel Driver
(*.xls);DBQ=C:\Company Folders\CorporateUpload\sub\subs.xls;', 'SELECT * FROM [Sheet1$] WHERE [MyCol] NOT LIKE ''blah%''')
AS derivedtbl_1

It will render this:
SELECT * FROM tmpRaw_tbl

There are a few reasons why you would get a NULL return string error.

1. the file may be open
2. file name is wrong (or the location)
3. you have to hae [Sheet1$] in brackets with the dollar sign in order for the Jet Engine to read the file.

Other interesting things

Look up schema.ini formatting. You can define what each column format is, according to Jet standards...