Post reply

Importing Excel with Dynamic Columns

  • January 23, 2014 at 7:58 am

    #303341

    I am in need of importing data from an excel spreadsheet. The columns will be different each time. The only thing that is static is the file name and the sheet name. I am using a linked server to the file and reading the data into a table like so:

    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=Book1.xls;Extended Properties=Excel 8.0')...[Book1$]

    The result of this is:

    I need to take this raw data and import it into another table called "importholding" This table contains the following fields: prodcode, width, height, price.

    The first row in the spreadsheet is the value of width. The first column of the spreadsheet is the value of height. The values in the middle of the spreadsheet are the values of price. The value of prodcode would be: ZZ + height + width.

    So the first record would look like: Prodcode = ZZ030012, width = 012, height = 030, price = 34.50.

    The second record would look like: Prodcode = ZZ030024, width = 024, height = 030, price = 29.40 and so on and so on.

    I am trying to achieve this with the following dynamic sql statement:

    INSERT INTO @temptable (width)

    SELECT '['+column_name+']' FROM information_schema.columns WHERE table_name = 'Book1$' AND column_name != 0

    SELECT @list = coalesce(@list+',','')+width FROM @temptable

    SET @sql = 'INSERT INTO importholding (prodcode, width, height, price)

    select CAST(CAST([000] AS varchar(6)) + header AS varchar(6)) AS PrdtCode, CAST(header AS varchar(6)) as width, CAST([000] AS varchar(6)) AS height, CAST(val AS money) as val

    from Book1$

    unpivot([val] for [header] in ('+@list+'))u order by height'

    EXEC sp_executesql @sql

    This keeps giving me an "Incorrect syntax near val" error.

    Can someone point me in the right direction?

  • January 23, 2014 at 5:22 pm

    #1682975

    Well after spending all day trying to figure this out, it turns out compatibility_level was altered on the server. After changing that back to 100, it my stored procedure works fine.

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply