dtswizard.exe Only one row returned importing .XLS

When importing data from an xls file, only first column of first row is returned? Am I missing something?Make sure that in the Source component you're selecting a worksheet name that ends in "$". Otherwise, what you're selecting is actually a range and it might only be defined as the first row.
|||Thanks a Million!|||

Hi JayH,

Can you tell me how to loop on one table. I have an input mapped to a column of this table. Is there a way to accomplish it within DFT ?

Will appreciate your help.

Thanks,

Lohan

|||Could you give a better description of your problem and what you want to accomplish? Typically, looping is not done inside a data flow, it is done with a For Loop container in the control flow.

DTS: import large database

Importing data from an Access database, I cannot overcome the limit of 1,000 records.
In DTS, I "copy one or more tables", select tables, run, and cannot see my 1,052 entries.
Where can I set a max size of ~1,500 in my sql target base?There's a limit on what you can copy? By default, I have been able to transfer (even from Access) more than 100,000 records at a time. Heck, from other db's, I have been able to transfer MILLIONS of records by default.

A quick workaround would be to do an export to text (preferrably .csv) and import that into SQL. You can even do that through SQL, I think, by selecting your source as Access and your target as Text file through DTS.

DTS vs Excel numeric conversion

I am having a problem importing an Excel spreadsheet. I have a column in
an Excel sheet with alphanumeric text and some of the cells are numeric.
Some of the cells contain numbers like 12345.6 and when DTS is done
importing it into a field that is nvarchar the results are
"12345.600000000001". I have tried:

1. Changing the format of the Excel column to text
2. Using the formula =text(a1,0) which only truncates the .6
3. Using the formula =t(a1) which will remove some numeric representations
4. Exporting the sheet to CSV or TXT first which will not enclose the cell
contents with ""
5. Beating the computer with a nine iron

None of these options work. Any idea anyone?

Don VonderBurgFormatting the cells as text after the data are there won't help.
Copy the cells to another location which is PREformatted as text, then
copy the copy back onto the original cells and try again. :)

On Thu, 13 May 2004 22:03:51 GMT, Don.Vonderburg@.nospam.com wrote:

>I am having a problem importing an Excel spreadsheet. I have a column in
>an Excel sheet with alphanumeric text and some of the cells are numeric.
>Some of the cells contain numbers like 12345.6 and when DTS is done
>importing it into a field that is nvarchar the results are
>"12345.600000000001". I have tried:
>1. Changing the format of the Excel column to text
>2. Using the formula =text(a1,0) which only truncates the .6
>3. Using the formula =t(a1) which will remove some numeric representations
>4. Exporting the sheet to CSV or TXT first which will not enclose the cell
>contents with ""
>5. Beating the computer with a nine iron
>None of these options work. Any idea anyone?
>Don VonderBurg|||Never thought of that one. Thank you.

Don|||Hi,

I vaguely remember that when you import the Excel file through DTS
that you can set the data type somewhere. Perhaps that helps.

When you look at the numbers that are wrongly imported in the Excel
formula bar, do you see the error as well? I guess when this is the
result in an Excel calculcated cell you may expect these rounding
errors. Remember that in Excel you never actually see the underlying
value. All values are always displayed using some kind of a display
mask. You can use the round function in Excel to round your results.
That should take care of it.

Just to make you aware of another way to pump your data into the
database. I wrote an addin for Excel called SQL*XL. Its goal is to
remove these hassles from the end user. You can use SQL*XL to get data
from the database into Excel or to pump data from Excel into the
database. It even lets you change retrieved data in Excel and post the
changes back.

If you are interested, have a look at SQL*XL at www.oraxcel.com

Best regards, Gerrit-Jan Linker
Linker IT Consulting Limited
www.oraxcel.com

DTS transformation question

Hello,
I am having problems with a quite simple DTS package. I guess the solution is quite simple, but I can't see it.

E.q: I am importing a Excel sheet with 1-3 columns into a predefined table in SQL 7.0 (default values). The number of columns might however change from time to time. I have designed the DTS package transformation using VB script.

Function Main()
DTSDestination("A") = DTSSource("A")
DTSDestination("B") = DTSSource("B")
DTSDestination("C") = DTSSource("C")
Main = DTSTransformStat_OK
End Function

but when I execute the package linked to a Excel file just containing column A and C I get an error claiming that the DTSDestination("B") is missing.

I am looking for a method that will either ignore the error or ignore the command under execution.

My real DTS package has several columns that might be missing.

ThanksPost some examples of your data. How are you able to map to columns a and c skipping b in your destination ?|||Originally posted by rnealejr
Post some examples of your data. How are you able to map to columns a and c skipping b in your destination ?

I was thinking of something like this

Function Main()
DTSDestination("A") = DTSSource("A")
IF ( DTSSource("B") EXIST) Then
DTSDestination("B") = DTSSource("B")
ELSE
DTSDestination("B") = "Mydefaultvalue"
END IF
DTSDestination("C") = DTSSource("C")
Main = DTSTransformStat_OK
End Function

, but the logical expression ( DTSSource("B") EXIST) is not valid. I am looking for some command or expression that will handle this.

I have seen postings where the expression is like:
IF isNull( DTSSource("B")) Then ....
,but isNull is just checking for empty cells. In my case the column "B" is not available.|||I don't think this is possible - I will check further - However, since you only have 3 possibilities you can create a path in dts to check the number of columns in the excel file and based on the answer (success or failure) have it execute the appropriate transformation task.|||Originally posted by rnealejr
I don't think this is possible - I will check further - However, since you only have 3 possibilities you can create a path in dts to check the number of columns in the excel file and based on the answer (success or failure) have it execute the appropriate transformation task.

Thanks rnealejr

I just got feedback from the SQL Development Team. They say:

The ActiveX Script task includes a list of functions that you can use in the
DTS scripts.
Instead of
IF ( DTSSource("B") EXIST) Then
DTSDestination("B") = DTSSource("B")
ELSE
...

Try
IF not DTSSource("B") is nothing Then
DTSDestination("B") = DTSSource("B")
ELSE
...
I have not been able to try this yet, but hopfully it will work. I will give you feedback if it does. Thanks again for your time.|||Interesting - If you are successful with this, then DTSSource is an object variable. You might try Empty/vbNull/Null as well.

Let me know if you are successful.|||Originally posted by rnealejr
Interesting - If you are successful with this, then DTSSource is an object variable. You might try Empty/vbNull/Null as well.

Let me know if you are successful.

Hi,

It did not work.

It seems that the Source columns are validated before the VBscript is executed.

Lasse

DTS Transform issue

I am importing data from Excel to a SQL table using a simple DTS. At times the DTS fails because one of the columns in the Excel file may have an invalid time date entry. Sometimes the time will be an invalid negative number and will cause an overflow error durring import to the SQL table column.

Is there a way to capture the data before writing it to the table and validate it and if it is invalid, or more specifically a negative nuimber, enter a default value or a null value?

If there is could you be specific in how to setup the DTS transformation script.

TIA
Jeffmoving thread to SQL Server forum (the SQL forum is for the SQL language itself)|||Import everything to a raw table first.

Then you can make all the validation you want before inserting the data into your system.|||I'd probably just suck the data from the external source into a working table that had pure Unicode (NVARCHAR) character columns. Once it was there, you can "sanitize" it any way you need to using Transact-SQL.

Another option that saves on disk and keeps the package conceptually "atomic" would be to handle the exceptions within the DTS package itself. Instead of using a default "flow" transformation within the DTS column mapping, you could use script to do whatever validation suited your needs.

-PatP|||HHmmm, well it seems like you both are saying the same thing.
"raw table" and "working table" are they the same thing?
All fileds are nvarchar correct?
and use a copy column to column transform correct?

JR

DTS to add a field on imported data

I am in the process of importing data that is in a text format to the
sql server, I want to add couple of fields and insert general data in
the fields added, this data is going to be similar to all the records
imported. Your help in this regard will be greatly appreciated.Hi

If you have created a DTS package to do this import you could add and
Execute SQL step to do an update statement. Alternatively, if the value is
to be the same for each record, then a default constraint on the column will
populate the initial values.

John

"John R" <admin@.wirelesscybercafe.com> wrote in message
news:a486f7fa.0409170810.3bdc4def@.posting.google.c om...
> I am in the process of importing data that is in a text format to the
> sql server, I want to add couple of fields and insert general data in
> the fields added, this data is going to be similar to all the records
> imported. Your help in this regard will be greatly appreciated.