Hello all,I have a stored procedure that has been working until just recently and I am stuck on why it's not working. The function of the SP is to import data from an excel spreadsheet into a table, then I select the data using unpivot to manipulate it and insert it into another table.First I read the file into table called Book1$IF EXISTS(SELECT name FROM MyDatabase..sysobjects WHERE name = N'Book1$' AND xtype='U')DROP TABLE Book1$SELECT * INTO Book1$ FROM OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0', 'Data Source=C:\FileLocation\Book1.xls;Extended Properties=Excel 8.0')...[Book1$]If I do a select * from book1$, I can see the data is there.
Next I get a list of the column names and separate them with a comma:DECLARE @list varchar(8000)DECLARE @temptable table (width varchar(10))INSERT INTO @temptable (width)SELECT '['+column_name+']' FROM information_schema.columns WHERE table_name = 'Book1$' AND column_name != 0SELECT @list = coalesce(@list+',','')+width FROM @temptable
Finally, I execute some dynamic sql to insert the data from Book1$ into importholding:DECLARE @sql nvarchar(max)SET @sql = 'INSERT INTO importholding (prodcode, width, height, price) select CAST(CAST([00] AS varchar(6)) + header AS varchar(6)) AS PrdtCode, CAST(header AS varchar(6)) as width, CAST([00] AS varchar(6)) AS height, CAST(val AS money) as valfrom Book1$unpivot(val for header in ('+@list+N'))u order by height'EXEC sp_executesql @sqlThis used to work just fine, but now I am getting an error that says "Incorrect syntax near the keyword 'for'.Any ideas?![]()
![]()
Importing Excel with Dynamic Columns
Posted - 2014-01-23 : 15:41:30
