Create Table script based on Excel file

• Jako de Wet

  Hall of Fame

  Points: 3123

  More actions

  November 7, 2011 at 3:01 am

  #247466

  Hi

  We receive product files from different customers and we update only relevant information.

  the current process is:

  - import excel file into a table (manual)

  - run various sql scripts (automatic)

  - extract appends into excel for our data team to load (manual)

  At the moment the importing and exporting is a manual task as each customer has different fields they send us.

  Is there a easy way to loop through a excel sheet's columns and generate a CREATE TABLE script based on the columns in the Excel?

  I am looking to change to process to

  - for each excel file create a temp table

  - loop through rows and insert into temp table

  - update fields based on user input

  - extract appends into a Excel sheet

  - mail appends sheet to data team

  This should all be automatic, I can do all of it except create the temp table based on the Excel sheet.

  I know I can loop through the 1st row for the table headings, but how can I extract the field type/size?

  Thank you.

• Mr. Holio

  Hall of Fame

  Points: 3314

  More actions

  November 7, 2011 at 3:23 am

  #1403825

  To be honest with you I would use PowerShell for this task.

  This URL will get you started

  http://newsqlblog.com/2011/08/05/dynamic-excel-posh/

  __________________________
  Allzu viel ist ungesund...

• Jako de Wet

  Hall of Fame

  Points: 3123

  More actions

  November 7, 2011 at 3:34 am

  #1403832

  Thanks, will have a look at it.

• Jako de Wet

  Hall of Fame

  Points: 3123

  More actions

  November 7, 2011 at 6:51 am

  #1403921

  mmm, it loads all the columns as VARCHAR(8000)

  I will just cast them when I do the updates.

  Thanks.

• ryvlad

  SSC Enthusiast

  Points: 162

  More actions

  November 7, 2011 at 8:50 am

  #1404007

  you can maybe use something like this to get all the files

  and them process therm in a loop

  SET ANSI_NULLS ON

  GO

  SET QUOTED_IDENTIFIER ON

  GO

  ALTER procedure [dbo].[GetFilesToProcessList]

  as begin

  EXEC master.dbo.sp_configure 'show advanced options', 1

  RECONFIGURE

  EXEC sp_configure 'xp_cmdshell', 1

  RECONFIGURE

  DECLARE @FilePath varchar(600)

  DECLARE @Command varchar(600)

  DECLARE @xp_cmdshelParam varchar(1000)

  set @FilePath = 'D:\factory\sys\dataTransferFiles\IN\'

  set @Command = ' dir /b '

  set @xp_cmdshelParam = @Command + @FilePath

  DECLARE @PROCESSMANIFEST_TEMP TABLE (data varchar(2000))

  insert into @PROCESSMANIFEST_TEMP

  EXEC master.dbo.xp_cmdshell @xp_cmdshelParam

  select * from @PROCESSMANIFEST_TEMP

  EXEC sp_configure 'xp_cmdshell', 0

  RECONFIGURE

  EXEC master.dbo.sp_configure 'show advanced options', 0

  RECONFIGURE

  end

  SET ANSI_NULLS OFF