asked on
Create excel files from SQL Server 2000 DTS for separate records
Hi all.
I would like to create a DTS that creates separate excel files for records in a SQL Server 2000 table.
The table looks like this:
ACD---ID---Name
10------A----Bob
10------B----Sally
20-----C-----Mark
10-----D-----John
30----E------Mary
30----F-----April
The DTS should then get all those records and place them in separate excel files based on ACD.
So, when the DTS is completed there should be 3 excel files created (in a shared folder of my choosing)
Excel File 1 would have: 10------A----Bob
10------B----Sally
10-----D-----John
Excel File 2 would have: 20-----C-----Mark
Excel File 3 would have: 30----E------Mary
30----F-----April
Ideally, I would like to name the excel file a specific format, for example, ExcelFile_ACD (i.e. ExcelFile_10 or ExcelFile_20, etc.)
Hopefully it's something simple, but I can't seem to figure it out.
Thank you in advance!
I would like to create a DTS that creates separate excel files for records in a SQL Server 2000 table.
The table looks like this:
ACD---ID---Name
10------A----Bob
10------B----Sally
20-----C-----Mark
10-----D-----John
30----E------Mary
30----F-----April
The DTS should then get all those records and place them in separate excel files based on ACD.
So, when the DTS is completed there should be 3 excel files created (in a shared folder of my choosing)
Excel File 1 would have: 10------A----Bob
10------B----Sally
10-----D-----John
Excel File 2 would have: 20-----C-----Mark
Excel File 3 would have: 30----E------Mary
30----F-----April
Ideally, I would like to name the excel file a specific format, for example, ExcelFile_ACD (i.e. ExcelFile_10 or ExcelFile_20, etc.)
Hopefully it's something simple, but I can't seem to figure it out.
Thank you in advance!
Microsoft SQL Server
Last Comment
printmedia
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
You'd need to do several things...
1) Update the template file to include the extra field if it is to be exported.
2) The following line determines what is put into the excel file. The columns in the excel file must match the result set returned by the second of these lines.
SET @Insert = 'INSERT INTO OPENDATASOURCE( ''Microsoft.Jet.OLEDB.4.0'
SELECT * FROM Test WHERE ACD = ' + CAST(@ACD as varchar)
This must therefore change to
SELECT * FROM Test WHERE STATUS = 0 AND ACD = ' + CAST(@ACD as varchar)
Or
SELECT ACD, ID, Name FROM Test WHERE STATUS = 0 AND ACD = ' + CAST(@ACD as varchar)
if the status is not to be included.
3) Also the change you suggest if you want to avoid creating empty files.
Make sense?
1) Update the template file to include the extra field if it is to be exported.
2) The following line determines what is put into the excel file. The columns in the excel file must match the result set returned by the second of these lines.
SET @Insert = 'INSERT INTO OPENDATASOURCE( ''Microsoft.Jet.OLEDB.4.0'
SELECT * FROM Test WHERE ACD = ' + CAST(@ACD as varchar)
This must therefore change to
SELECT * FROM Test WHERE STATUS = 0 AND ACD = ' + CAST(@ACD as varchar)
Or
SELECT ACD, ID, Name FROM Test WHERE STATUS = 0 AND ACD = ' + CAST(@ACD as varchar)
if the status is not to be included.
3) Also the change you suggest if you want to avoid creating empty files.
Make sense?
ASKER
Yes it does, I don't want to create empty files so I put that condition after line 10 in the code snippet.
One last question, I added to fields to my table: Path and Filename
After I create the files I want to also enter the path, which in this case will be: @FolderPath
and the Filename, which will be: 'ExcelFile_' + CAST(@ACD as varchar) + '.xls'
How do I do it in Dynamic SQL?
One last question, I added to fields to my table: Path and Filename
After I create the files I want to also enter the path, which in this case will be: @FolderPath
and the Filename, which will be: 'ExcelFile_' + CAST(@ACD as varchar) + '.xls'
How do I do it in Dynamic SQL?
If you want to be able to control the path and file name, you might consider wrapping this in a stored procedure and allowing these to be passed as parameters... see snippet.
After the procedure is create it you can then use this like
EXEC ExportExcelMulti 'OutputFolder', 'TemplateFilePath', 'Base name of exported files files'
with the last parameter being optional.
Is that what you wanted?
After the procedure is create it you can then use this like
EXEC ExportExcelMulti 'OutputFolder', 'TemplateFilePath', 'Base name of exported files files'
with the last parameter being optional.
Is that what you wanted?
CREATE PROCEDURE
ExportExcelMulti
(
@OutputFolderPath varchar(255) = NULL,
@TemplateFilePath varchar(255) = NULL,
@FileBaseName varchar(255) = 'ExcelFile_'
)
AS
IF LEN(ISNULL(@OutputFolderPath, '')) = 0 BEGIN RAISERROR('@OutputFolderPath is required', 16, 1) RETURN END
IF LEN(ISNULL(@TemplateFilePath, '')) = 0 BEGIN RAISERROR('@TemplateFilePath is required', 16, 1) RETURN END
DECLARE
@ACD int
DECLARE c_MyCurs CURSOR LOCAL FAST_FORWARD FOR
SELECT DISTINCT ACD FROM dbo.Test
OPEN c_MyCurs
FETCH NEXT FROM c_MyCurs INTO @ACD
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @OLEDBInitString varchar(1000),
@FilePath varchar(1000),
@CopyCmd varchar(1000)
SET @FilePath = @OutputFolderPath + @FileBaseName + CAST(@ACD as varchar) + '.xls'
SET @CopyCmd = 'COPY /Y "' + @TemplateFilePath + '" "' + @FilePath + '"'
EXEC master.dbo.xp_cmdshell @CopyCmd, no_output
SET @OLEDBInitString = 'Data Source="' + @FilePath + '";Extended properties=Excel 5.0;'
DECLARE @Insert varchar(1000)
SET @Insert = 'INSERT INTO OPENDATASOURCE( ''Microsoft.Jet.OLEDB.4.0'', ''' + @OLEDBInitString + ''')...[Sheet1$]
SELECT * FROM Test WHERE ACD = ' + CAST(@ACD as varchar)
EXEC (@Insert)
FETCH NEXT FROM c_MyCurs INTO @ACD
END
CLOSE c_MyCurs
DEALLOCATE c_MyCurs
SET NOCOUNT OFF
ASKER
I tried adding an INSERT after the EXEC(@Insert), but what it does is create new rows with the PATH field having the @FolderPath value. I can't get it to do it for the individual record (ACD).
Do I have to create a stored procedure (I have no experience with those), as the Dynamic SQL creates the file I would simply update the record's PATH and FILENAME fields.
Do I have to create a stored procedure (I have no experience with those), as the Dynamic SQL creates the file I would simply update the record's PATH and FILENAME fields.
DECLARE
@ACD int,
@FolderPath varchar(255),
@TemplateFilePath varchar(255)
SET @TemplateFilePath = 'D:\ACCN EXCEL FILE TEST\Template.xls'
SET @FolderPath = 'D:\ACCN EXCEL FILE TEST\'
DECLARE c_MyCurs CURSOR LOCAL FAST_FORWARD FOR
SELECT DISTINCT ACD
FROM dbo.Test
WHERE (STATUS = 0)
OPEN c_MyCurs
FETCH NEXT FROM c_MyCurs INTO @ACD
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @OLEDBInitString varchar(1000),
@FilePath varchar(1000),
@CopyCmd varchar(1000)
SET @FilePath = @FolderPath + 'ExcelFile_' + CAST(@ACD as varchar) + '.xls'
SET @CopyCmd = 'COPY /Y "' + @TemplateFilePath + '" "' + @FilePath + '"'
EXEC master.dbo.xp_cmdshell @CopyCmd, no_output
SET @OLEDBInitString = 'Data Source="' + @FilePath + '";Extended properties=Excel 5.0;'
DECLARE @Insert varchar(1000)
SET @Insert = 'INSERT INTO OPENDATASOURCE( ''Microsoft.Jet.OLEDB.4.0'', ''' + @OLEDBInitString + ''')...[Sheet1$]
SELECT ID, Name FROM Test WHERE ACD = ' + CAST(@ACD as varchar)
EXEC (@Insert)
INSERT INTO [Test](PATH)
VALUES (@FolderPath)
FETCH NEXT FROM c_MyCurs INTO @ACD
END
CLOSE c_MyCurs
DEALLOCATE c_MyCurs
Sorry, my fault, I misunderstood your request.
The code you want to add after the EXEC (@Insert) is
UPDATE
Test
SET
PATH = @FolderPath,
FILENAME = 'ExcelFile_' + CAST(@ACD as varchar) + '.xls'
WHERE
ACD = @ACD
The code you want to add after the EXEC (@Insert) is
UPDATE
Test
SET
PATH = @FolderPath,
FILENAME = 'ExcelFile_' + CAST(@ACD as varchar) + '.xls'
WHERE
ACD = @ACD
ASKER
Great! Thanks for all your help and patience!
ASKER
Just out of curiosity, could I do something similar by exporting to Microsoft Word instead of excel?
Not easily. Ie I'm sure something could be built by someone, but it'd cost you ;-)
ASKER
Haha...got it
What if I wanted to add a field to the table: STATUS, and I only wanted to export records where the status was = 0.
Would I put that after line 10:
SELECT DISTINCT ACD FROM dbo.Test
WHERE STATUS = 0