Import from Sql to Excel 1

[TonyFoo]

Hi all,

I am using this code to pull in data from multiple excel spreadsheets, I am fairly new to SQL and have never pulled data from excel files before. The code here works (may not be efficient or secure, but in this case it does not need to be) but I have pull in the row number from the excel spreadsheet that the record belongs to.

DROP TABLE Prorate_Analyzer
Declare @Files Table ([File] varchar(250) null)
INSERT @Files ([File])
EXEC master..xp_cmdshell 'DIR C:\current\*.xls /b/s'

DECLARE @FILENAME VARCHAR(250), @FILEPATH varchar(250), @FullFileName varchar(250)

DECLARE file_cursor CURSOR FOR
SELECT [File] FROM @Files
WHERE [File] NOT IN ('NULL','File Not Found') 

Create  TABLE Prorate_Analyzer 
(
--RowNumber		INT,
JOB#			VARCHAR(50),
CUSTOMER		VARCHAR(50),
DESCRIPTION		VARCHAR(MAX),
PROV			VARCHAR(10),
INVOICE#		VARCHAR(30),
DATE			DATETIME,
UNIT#			VARCHAR(max),
DIVISION		VARCHAR(30)
)

DECLARE @SQL              NVARCHAR(4000)
DECLARE @Provider         NVARCHAR(100)
DECLARE @FIL              NVARCHAR(256)
DECLARE @ConnectionString NVARCHAR(1000)
DECLARE @Query            NVARCHAR(1000)
DECLARE @TABLE			  NVARCHAR(50)

OPEN file_cursor
FETCH NEXT FROM file_cursor
INTO @FILENAME 

WHILE @@FETCH_STATUS = 0
BEGIN 
	SET @Provider          = N'Microsoft.ACE.OLEDB.12.0'
	SET @FIL               = N'' + @Filename
	SET @ConnectionString  = N'Excel 8.0;DATABASE=' + @FIL
	SET @Query             = N'SELECT * FROM [Current$]'
	SET @TABLE			   = N'@DATA_TABLE'

	SET @SQL = N'SELECT * into #DATA
				 FROM OPENROWSET(' + QUOTENAME(@Provider, N'''') + N', ' 
								   + QUOTENAME(@ConnectionString, N'''') + N', '
								   + QUOTENAME(@Query, N'''') + N')
									INSERT INTO Prorate_Analyzer
									select /*RowNumber,*/ [JOB #], CUSTOMER, DESCRIPTION, PROV, [INVOICE #], DATE, [UNIT #], [DIV #]
									from #DATA where "Job #" is not null '
	
	EXEC sp_executesql @SQL
	if @@Error <> 0  
		raiserror(@FileName,16,1)
	FETCH NEXT FROM file_cursor
	INTO @FILENAME
END
CLOSE file_cursor
DEALLOCATE file_cursor
SELECT * FROM Prorate_Analyzer

Thanks in advance for all of your help!

 

[ousoonerjoe]

Have you looked at the data import tools (DTS packages) does most of the work for you.

"If I were to wake up with my head sewn to the carpet, I wouldn't be more surprised than I am right now.