Hi
I want to create a job for copying all data from one
excel file containing many worksheets into only one text
file.

I have been experimenting with using Import Wizard in SQL
Server to create a Transform Data Task in DTS. This is
working, but my problem is that the Excel file contains
several worksheets. I want to repeat the procedure for
each worksheet, but I do not want one text file for each
worksheet. I want one text file containing data from all
the worksheets. The problem is that my text file is
emptied each time I execute the job, so I am not able to
add data to the existing text file. There are about
20000 rows in each worksheet, so I do not want to end up
using a solution that gives slow performance.

I have an alternative solution using Excel macros, and
this contains:

Open TextFile For Output As 1
Print #1
Loop

This will print one by one row to the text file.
I think I can make this work, but my question is:
Is there a better way to do this in Excel macros, in
command prompt, VBS, DTS?

Best regards
Helge Arntsen

One alternative would be to get all the data on one sheet - however, if
there are more than 3+ worksheets, then you would run out of room.

Another would be to write one textfile for each sheet, then combine them

in DOS/command window or using the shell command

copy a.txt+b.txt+c.txt d.txt

or copy *.txt d.txt

--
Regards,
Tom Ogilvy

"Helge Arntsen" wrote in message
...
Hi
I want to create a job for copying all data from one
excel file containing many worksheets into only one text
file.

I have been experimenting with using Import Wizard in SQL
Server to create a Transform Data Task in DTS. This is
working, but my problem is that the Excel file contains
several worksheets. I want to repeat the procedure for
each worksheet, but I do not want one text file for each
worksheet. I want one text file containing data from all
the worksheets. The problem is that my text file is
emptied each time I execute the job, so I am not able to
add data to the existing text file. There are about
20000 rows in each worksheet, so I do not want to end up
using a solution that gives slow performance.

I have an alternative solution using Excel macros, and
this contains:

Open TextFile For Output As 1
Print #1
Loop

This will print one by one row to the text file.
I think I can make this work, but my question is:
Is there a better way to do this in Excel macros, in
command prompt, VBS, DTS?

Best regards
Helge Arntsen

Hi Helga,

This is an adaptation (for each sheet) of something I posted a while ago:

Sub Range2TxtWB()
Dim MyData As DataObject, ws As Worksheet

Set MyData = New DataObject

Open "C:\Temp\Range2Txt_Test2.txt" For Output As #1

For Each ws In ActiveWorkbook.Worksheets
ws.UsedRange.Copy
MyData.GetFromClipboard
Print #1, "Start of " & ws.Name 'optional
Print #1, MyData.GetText(1)
Application.CutCopyMode = False
Next

Close #1

End Sub

You will need to set a reference (tools references) to "Microsoft
Forms 2.0 Library" unless already set by default.

Dave Peterson mentioned in a follow-up that he had a problem if he had a
"pretty large usedrange". It's worked fine and fast for me with a lot of
data. If it works for you it might be worth including a count of rows
copied and comparing with the rows of data in the final text file, also
some error handling.

Regards,
Sandy


Helge Arntsen wrote:
Hi
I want to create a job for copying all data from one
excel file containing many worksheets into only one text
file.

I have been experimenting with using Import Wizard in SQL
Server to create a Transform Data Task in DTS. This is
working, but my problem is that the Excel file contains
several worksheets. I want to repeat the procedure for
each worksheet, but I do not want one text file for each
worksheet. I want one text file containing data from all
the worksheets. The problem is that my text file is
emptied each time I execute the job, so I am not able to
add data to the existing text file. There are about
20000 rows in each worksheet, so I do not want to end up
using a solution that gives slow performance.

I have an alternative solution using Excel macros, and
this contains:

Open TextFile For Output As 1
Print #1
Loop

This will print one by one row to the text file.
I think I can make this work, but my question is:
Is there a better way to do this in Excel macros, in
command prompt, VBS, DTS?

Best regards
Helge Arntsen

"Helge Arntsen" wrote ...

I want to create a job for copying all data from one
excel file containing many worksheets into only one text
file.

I have been experimenting with using Import Wizard in SQL
Server to create a Transform Data Task in DTS. This is
working, but my problem is that the Excel file contains
several worksheets. I want to repeat the procedure for
each worksheet, but I do not want one text file for each
worksheet. I want one text file containing data from all
the worksheets.

The OLEDB provider for Jet 4.0 may be used to query/create both Excel
and text data sources. Therefore, you could do something like this in
T-SQL:

INSERT INTO
OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0','Data
Source=C:\Tempo\;Extended Properties=Text')...[db#txt]
(MyDateTimeCol, MyIntCol, MyTextCol)
SELECT
MyDateTimeCol, MyIntCol, MyTextCol
FROM
OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0','Data
Source=C:\Tempo\db.xls;Extended Properties=Excel 8.0')...[Sheet1$]
;

(BTW the '...' is a literal string of three periods, not an ellipsis.)

If you wanted to do it all in one hit, you could extend the inserted
dataset (and eliminate duplicates) by appending some SELECT clauses
using the UNION keyword.

Jamie.

--