November 7, 2011 at 3:01 am
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.
November 7, 2011 at 3:23 am
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...
November 7, 2011 at 3:34 am
Thanks, will have a look at it.
November 7, 2011 at 6:51 am
mmm, it loads all the columns as VARCHAR(8000)
I will just cast them when I do the updates.
Thanks.
November 7, 2011 at 8:50 am
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
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy