DTS from Excel to SQL?

I tried to use DTS for copying a sheet from Excel to SQL. For some
reason, the last column shows up error saying source's column 5 buffer
is too big.. and failed it.. is there anything that I need to watch
out?ebug@.hotmail.com (Kelvin) wrote in message news:<191e0546.0404261422.4bad806@.posting.google.com>...
> I tried to use DTS for copying a sheet from Excel to SQL. For some
> reason, the last column shows up error saying source's column 5 buffer
> is too big.. and failed it.. is there anything that I need to watch
> out?

Does this apply to your case?

http://support.microsoft.com/defaul...kb;EN-US;281517

Simon

DTS from Excel problems

I am trying to import an Excel spreadsheet into SQL Server. Why can I impor
t
an empty Excel column only if it is a text field? If the field is a number
or date field, I get a conversion error.
i.e.
create table labresults
(
analyte varchar(50),
sampledate datetime,
result numeric(19,6)
)
The excel worksheet has 3 columns:
analyte (formatted as text)
sampledate (formatted as date)
result (formatted as number)
If the first 7 rows in the analyte column are empty, the file imports fine,
but if the first 7 rows of sampledate or result are empty, I get a
"Conversions invalid for data types" error.
Anybody have any good suggestions on how to fix this issue?
Thanks.
ArcherHi
Sounds like it is related to:
http://www.sqldts.com/default.aspx?254
John
"bagman3rd" wrote:

> I am trying to import an Excel spreadsheet into SQL Server. Why can I imp
ort
> an empty Excel column only if it is a text field? If the field is a numbe
r
> or date field, I get a conversion error.
> i.e.
> create table labresults
> (
> analyte varchar(50),
> sampledate datetime,
> result numeric(19,6)
> )
> The excel worksheet has 3 columns:
> analyte (formatted as text)
> sampledate (formatted as date)
> result (formatted as number)
> If the first 7 rows in the analyte column are empty, the file imports fine
,
> but if the first 7 rows of sampledate or result are empty, I get a
> "Conversions invalid for data types" error.
> Anybody have any good suggestions on how to fix this issue?
> Thanks.
> Archer
>sql

DTS fails coes it refuses to fill the unique id colum

THe unique Id column is set to identity seed. Yet when transfering 1500 records it fails to stuff them all becuase it says there is a NULL in unique id column.

"Cannot insert the value NULL into column UID column does not allow"

Yet in the transformation screen I am setting the UID column to <ignore> meaning I do not have that value in my source and I am wanting the destination table to do it's thing and incrememnt by 1 the identity seed.

Is this common?

THe source and destination tables are in the same databes.

When you click on "Transform" uncheck where it says "Enable Identity Insert"

|||

Thank you very much.

It worked.

I am not sure I "logically" understand that though.

|||

When you have enable Identity Insert checked in your DTS wizard what it does is says "I want you to insert the identity field that is coming from the table I'm copying from". This is useful when you have a truncated table and you want to keep your identity field values for data integrity.

Now comes the however.

When you set to Ignore the Identity column in your transformation you were essentially telling it to insert null values, and Identity columns can't be null. Had you not selected that then you would have most likely received the error that says you can't insert duplicates into your identiy column as it violates the unique constraint.

DTS excel file

Hi,

I have to DTS a excel file to sql server 2000. The DTS package works fine. I have issue with one column that has value

450

800

900

45TH

23SI

800

390

100

30SI

If given the cell format general/text only that have alphanumeric characters are upload and not others. What should be done to upload all the values for this column.

thanks in advance.

Are you using DTS or SSIS?

There is a forum specific to DTS issues: http://groups.google.com/group/microsoft.public.sqlserver.dts?lnk=srg

DTS Error: Importing from Excel file to SQL Server 2000

Data for Source Column 15 'Notes' is too large for the specified buffer size.

How do I get around this, I can see some of the notes entries are beyond 255 chars so I changed the destination datatype totext

I have never seen this error when importing before. What do I do?

An error I've gotten way too much. Some things you can try:

- The Excel provider (JET) often makes assumptions based on the first 8 lines of the file, and sometimes that is the problem).
- Or you can save to comma separated or tab separated file format, and import that way.

Oftentimes, I cannot figure out the problem as well. I've tried posting before, but no answer. The only thing I come up with is to save to tab-delimited, and import that way.

Sorry I can't be more help; hopefully someone has the answer.

DTS error too many columns found

Trying to import a text file with a 11 columns, column delimiter is ;
record delimiter is CR+ LF.
Using a DTS package (bulk copy)
Then I got erros with these discription
------
SYMPTOMS
When you import a text file into Microsoft SQL Server by using the Transform Data task, if no text qualifier is specified and a row with too many columns is encountered, the load of the text file may fail with the following error message:

Error Source: Microsoft Data Transformation Services Flat File Rowset Provider
Error Description:Too many columns found in the current row; non-whitespace characters were found after the last defined column's data.
Error Help FileTSFFile.hlp
Error Help Context ID:0
The preceding error message repeats in the DTS exception log N times, where N is the value of the Max Error Count setting on the Transform Data tasks options property page.

CAUSE
This problem occurs because of a malformed source text file in which the DTS Data Pump encounters a row in the source file that contains too many columns.

http://support.microsoft.com/default.aspx?scid=kb%3ben-us%3b300181

--------
I tryed the fix of Microsoft but no result yet.Did you get the specified hotfix from MS Support?
For information refer to thisSQL MAG (http://www.sqlmag.com/Forums/messageview.cfm?catid=11&threadid=7326) thread.|||Originally posted by Satya
Did you get the specified hotfix from MS Support?
For information refer to thisSQL MAG (http://www.sqlmag.com/Forums/messageview.cfm?catid=11&threadid=7326) thread.

Yes I got the fix en installed but I have still the errors.|||Then better to report back to MS Support.

DTS error on GUID column

I am doing a DTS from table "source" to table "destination". However
"destination" has a primary key of data type uniqueidentifier and the source
doesn't have a corresponding map field. So I thought setting the
"destination" primary key to have a default value of newId() will
automatically insert a guid for every record inserted (just like a regular
identity key) but unfortunately this doesn't work. I get a message that says
"cannot insert null into the field fieldName" which makes sense because it i
s
the PK but why doesn't the newId() generate an automatic GUID? Or is it even
possible? or could I change the transformation script to make it work and if
so, how? Thanks for any help.On guid primary key column undid primary key constraint, set allow null, set
default value to newId() and DTS worked fine and new guids got inserted.
After DTS set all constraints back as original. So everything's good.
"Naveen" wrote:

> I am doing a DTS from table "source" to table "destination". However
> "destination" has a primary key of data type uniqueidentifier and the sour
ce
> doesn't have a corresponding map field. So I thought setting the
> "destination" primary key to have a default value of newId() will
> automatically insert a guid for every record inserted (just like a regular
> identity key) but unfortunately this doesn't work. I get a message that sa
ys
> "cannot insert null into the field fieldName" which makes sense because it
is
> the PK but why doesn't the newId() generate an automatic GUID? Or is it ev
en
> possible? or could I change the transformation script to make it work and
if
> so, how? Thanks for any help.

DTS- Destination Column Name has spaces causing error

I am using Advantage ODBC 6.2 to connect to a Advantage Data Server to
push data from a SQL table into this server. I can view the data from
the ADS with DTS and I can insert data by using "Insert into TempTable ("Last
Name","First Name") values ('test','test)" from a Execute SQL Statement. The issue is when I build
up a DTS package to pull from SQL into the Advantage ODBC, I get a
"missing closing ")"" error. I have narrowed it down to the column
names in the destination table having spaces (Last Name, First Name,
and a bunch of others. DBA 101 here). How do I beat this?

I have tried editing the destination column names in disconnected edit
by adding the double quotes but get a "Column Name '"Last Name"' not
found" error. Wrapping them with [] doesn't work. I think this is a
limit on Advantage.

When I try to use Advantage OLE DB, I get an 'Ace32.dll must be newer
then the other dll" error. I am afraid of upgrading Ace32.dll and not
break other things.

So with this said my two questions:

How do I get the destniation column names wrapped in double quotes when using transformations?

or

Is there a way I can do an Insert into Advantage connection(column names) values (Select Values from SQL Server connection)?

Use square brackets ( [ ] ) , instead of double quotes.|||

I tried this in a sql query against the Advantage ODBC and it failed. I believe this is a limitation of the ODBC drivers for Advantage.

The issus that the column names are causing this to fail inside the DTS Transformations themselves. Anyone know where I can edit the Destination column names so that I can wrap them with quotes or brackets.

Thanks

Donnie

|||

Have you tried enclosing the column names in single quotes instead of Microsoft brackets?,

i.e. SELECT 'My Column' FROM MyTable

|||

Yes tried single quotes.

If I try Insert into TableName ("Last Name") values ('Test') with the Advantage ODBC, it works. Issue is, I need to pull from SQL and push into Advantage. When I try with with transformations between the source and desitination, I get the missing closing ")" error.

So with this said my two questions:

How do I get the destniation column names wrapped in double quotes when using transformations?

or

Is there a way I can do an Insert into Advantage connection(column names) values (Select Values from SQL Server connection)?

Thanks for the help

Donnie

|||

This forum is for SSIS, not DTS.

You'll probably get better assistance on the DTS newsgroup.

|||Thanks DD. Got it over there as well as Google forums.|||Bumpsql

DTS csv import fails when using double quotes as the Text Qualifier, the last column is a

I am using DTS to import csv files that I receive from a third party.
Each day I receive many file which I import into many different SQL
Server tables depending on the name of the file. I am doing this as
part of a larger automation process written in C#. Due to the csv
file names differing each day (they contain a date) and the large
number of DB Table schemas, I am programmatically/dynamically
creating/building a generic DTS package for each file and calling it
to import the csv file into the appropriate table.
The data contains commas for the column delimiters, double quotes for
the Text Qualifier and CR LF for the row delimiter. I have set the
appropriate DTSFlatFile connection properties and successfully
imported many of the csv files except files which meet the following
criteria:
1) the last column of the SQL Server table that corresponds to this
file is of type varchar or char
2) the data value for the last column of the record is always null
For these file I get the following error message when I try to execute
the package: "A DTSTransformCopy must specify no columns (signifying a
sequential 1-to-1 mapping of all columns) or the same number of source
and destination columns."
e.g.
File 1 -- imports fine:
--
"1","a, b","03/03/2004 00:00:00","01/17/2038 23:59:59","03/04/2004
06:25:58","0088288574800","dd"
"2","cd","03/03/2004 00:00:00","01/17/2038 23:59:59","03/04/2004
06:25:58","0088288574800",
"3","e, f","03/03/2004 00:00:00","01/17/2038 23:59:59","03/04/2004
06:25:58","0088288574800",
File 2 -- imports fine:
--
"1","c, d","03/03/2004 00:00:00","01/17/2038 23:59:59","03/04/2004
06:25:58","0088288574800",
"2","ab","03/03/2004 00:00:00","01/17/2038 23:59:59","03/04/2004
06:25:58","0088288574800","dd"
"3","c, d","03/03/2004 00:00:00","01/17/2038 23:59:59","03/04/2004
06:25:58","0088288574800",
File 3 -- imports fine:
--
"1","a, b","03/03/2004 00:00:00","01/17/2038 23:59:59","03/04/2004
06:25:58","0088288574800",
"2","cd","03/03/2004 00:00:00","01/17/2038 23:59:59","03/04/2004
06:25:58","0088288574800",
"3","e, f","03/03/2004 00:00:00","01/17/2038 23:59:59","03/04/2004
06:25:58","0088288574800","dd"
File 4 -- fails:
--
"1","a, b","03/03/2004 00:00:00","01/17/2038 23:59:59","03/04/2004
06:25:58","0088288574800",
"2","cd","03/03/2004 00:00:00","01/17/2038 23:59:59","03/04/2004
06:25:58","0088288574800",
"3","e, f","03/03/2004 00:00:00","01/17/2038 23:59:59","03/04/2004
06:25:58","0088288574800",
Table schema:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Table1]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Table1]
GO
CREATE TABLE [dbo].[Table1] (
[Field1] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[Field2] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[Field3] [datetime] NOT NULL ,
[Field4] [datetime] NULL ,
[Field5] [datetime] NULL ,
[Field6] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[Field7] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
I am using SQL 2000 sp3a:
Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Developer Edition on Windows NT 5.0 (Build 2195: Service Pack 3)
BTW I found an 1.5 year old posting about this issue but no solution
was ever posted:
http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&threadm=%23XiCPwG3CHA.1624%40TK2MSFTNGP12.phx.gbl&rnum=1&prev=/groups%3Fhl%3Den%26lr%3D%26ie%3DUTF-8%26selm%3D%2523XiCPwG3CHA.1624%2540TK2MSFTNGP12.phx.gbl
Thanks,
DavidSo why you can't process this text file manually in your C# program
It will be a simple loop with output to the table
"David E Herbst" wrote:
> I am using DTS to import csv files that I receive from a third party.
> Each day I receive many file which I import into many different SQL
> Server tables depending on the name of the file. I am doing this as
> part of a larger automation process written in C#. Due to the csv
> file names differing each day (they contain a date) and the large
> number of DB Table schemas, I am programmatically/dynamically
> creating/building a generic DTS package for each file and calling it
> to import the csv file into the appropriate table.
> The data contains commas for the column delimiters, double quotes for
> the Text Qualifier and CR LF for the row delimiter. I have set the
> appropriate DTSFlatFile connection properties and successfully
> imported many of the csv files except files which meet the following
> criteria:
> 1) the last column of the SQL Server table that corresponds to this
> file is of type varchar or char
> 2) the data value for the last column of the record is always null
> For these file I get the following error message when I try to execute
> the package: "A DTSTransformCopy must specify no columns (signifying a
> sequential 1-to-1 mapping of all columns) or the same number of source
> and destination columns."
> e.g.
> File 1 -- imports fine:
> --
> "1","a, b","03/03/2004 00:00:00","01/17/2038 23:59:59","03/04/2004
> 06:25:58","0088288574800","dd"
> "2","cd","03/03/2004 00:00:00","01/17/2038 23:59:59","03/04/2004
> 06:25:58","0088288574800",
> "3","e, f","03/03/2004 00:00:00","01/17/2038 23:59:59","03/04/2004
> 06:25:58","0088288574800",
> File 2 -- imports fine:
> --
> "1","c, d","03/03/2004 00:00:00","01/17/2038 23:59:59","03/04/2004
> 06:25:58","0088288574800",
> "2","ab","03/03/2004 00:00:00","01/17/2038 23:59:59","03/04/2004
> 06:25:58","0088288574800","dd"
> "3","c, d","03/03/2004 00:00:00","01/17/2038 23:59:59","03/04/2004
> 06:25:58","0088288574800",
> File 3 -- imports fine:
> --
> "1","a, b","03/03/2004 00:00:00","01/17/2038 23:59:59","03/04/2004
> 06:25:58","0088288574800",
> "2","cd","03/03/2004 00:00:00","01/17/2038 23:59:59","03/04/2004
> 06:25:58","0088288574800",
> "3","e, f","03/03/2004 00:00:00","01/17/2038 23:59:59","03/04/2004
> 06:25:58","0088288574800","dd"
> File 4 -- fails:
> --
> "1","a, b","03/03/2004 00:00:00","01/17/2038 23:59:59","03/04/2004
> 06:25:58","0088288574800",
> "2","cd","03/03/2004 00:00:00","01/17/2038 23:59:59","03/04/2004
> 06:25:58","0088288574800",
> "3","e, f","03/03/2004 00:00:00","01/17/2038 23:59:59","03/04/2004
> 06:25:58","0088288574800",
> Table schema:
> if exists (select * from dbo.sysobjects where id => object_id(N'[dbo].[Table1]') and OBJECTPROPERTY(id, N'IsUserTable') => 1)
> drop table [dbo].[Table1]
> GO
> CREATE TABLE [dbo].[Table1] (
> [Field1] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> ,
> [Field2] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> ,
> [Field3] [datetime] NOT NULL ,
> [Field4] [datetime] NULL ,
> [Field5] [datetime] NULL ,
> [Field6] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> ,
> [Field7] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
> I am using SQL 2000 sp3a:
> Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
> Dec 17 2002 14:22:05
> Copyright (c) 1988-2003 Microsoft Corporation
> Developer Edition on Windows NT 5.0 (Build 2195: Service Pack 3)
> BTW I found an 1.5 year old posting about this issue but no solution
> was ever posted:
> http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&threadm=%23XiCPwG3CHA.1624%40TK2MSFTNGP12.phx.gbl&rnum=1&prev=/groups%3Fhl%3Den%26lr%3D%26ie%3DUTF-8%26selm%3D%2523XiCPwG3CHA.1624%2540TK2MSFTNGP12.phx.gbl
> Thanks,
> David
>|||Thank you for the suggestion.
Yes, that is an option but I am importing a many different csv files
to a large number of DB tables (each with a different number of
columns and column types) and I'm trying to avoid writing custom file
parsing code for each of the table schemas. Importing csv files seems
like a pretty standard problem that has been solved many times already
so I'm also trying to avoid spending time writing my own generic csv
file parser/loader which seems like reinventing the wheel. In
addition I've heard that large numbers of INSERTs though ADO.NET is
not that efficient and news groups postings usually suggest DTS
instead?
BTW I forgot to mention that I am taking advantage of the fact that
since the source and destination have the same number of columns in
the same order I don't have to define and add any column objects to
the DTS.DataPumpTransformCopy transformation object. This is useful
since I can use the same generic package creation code for all of the
DB tables and not embed any knowledge of the column schema in my code.
See:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dtsprog/dtspapps_45k3.asp?frame=true
(see comment in the example)
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dtsprog/dtspapps_976r.asp?frame=true
(first bullet points)sql

DTS copy truncates bigint values

I just discovered that a bigint column in one of my tables is getting it's values truncated when I run a DTS job to copy the data to another database. The DTS job is designed to refresh our test environments from our production database. All the other tables copy fine. This include another table that also contains a bigint column. But this one table consistenly has a problem where 60 or so records are translated from positive number to negative values. The only explaination I came up with was truncation at some point.

Anyone know of a way to fix or work around this?Is it the same 60 records, or different ones? Have you tried perhaps converting this column to a large varchar field, to see what happens to those records then? Unfortunately I don't know what this is off the top of the head, but these might help figure out what it is.|||The number of records will change every so often. I think I first noticed 64 and then it was 62. I refined my query and discovered that in reality I have 192 records out of 6025 records where the bigint field was mangled. I did a join between the two databases to find the mismatches.

One thing I noticed is that it appears to work when going from our production server to our test server. Those servers are SQL Server 2K Standard SP2 while my machine is SQL Server 2K Developer SP3.|||Well maybe if you copied it to your machine to a temp table with a varchar as the datatype (converting the bigint to varchar in your DTS), then converting it back (just altering the table may do the trick since bigint to varchar is an implicit conversion). That may be a workaround for your machine anyways.

DTS copy all objects failed with invalid column name

I am trying to use the Import Data wizard to copy one database into
another on the same server and keep getting this error.
[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid column name
'ColumnName'
There are 6 columns in the list and all of them either don't exist
anymore or have been moved to another table in the database. I don't
understand why it keeps trying to copy columns that either aren't
there or have been moved. It looks like the database has become
corrupted. Does anyone know how I might fix this?
Regards,
Aaron
I have also tried dropping all the tables, recreating them and
re-inserting the data and I still get these errors when trying to copy
the database.
|||I have also tried dropping all the tables, recreating them and
re-inserting the data and I still get these errors when trying to copy
the database.
Regards,
Aaron

DTS copy all objects failed with invalid column name

I am trying to use the Import Data wizard to copy one database into
another on the same server and keep getting this error.
[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid column name
'ColumnName'
There are 6 columns in the list and all of them either don't exist
anymore or have been moved to another table in the database. I don't
understand why it keeps trying to copy columns that either aren't
there or have been moved. It looks like the database has become
corrupted. Does anyone know how I might fix this?
Regards,
AaronI have also tried dropping all the tables, recreating them and
re-inserting the data and I still get these errors when trying to copy
the database.|||I have also tried dropping all the tables, recreating them and
re-inserting the data and I still get these errors when trying to copy
the database.
Regards,
Aaron

dts conversion error

I have a dts package that imports data from a comma delimited .csv file.

I'm getting a conversion invalid for datatypes on column pair 1 (source 'col0012' (DBTYPE_STR), destination column 'latitude' (DBTYPE_R8)).

So, the source file is populated as a string for 'col002' and I have that field specified as a float within my table. Float is the correct type for the value.

How can I make the conversion correctly during the dts execution.

Thank you.do you have any empty strings in the source file going to the float column?|||Also - does your CSV include column headings and, if so, have you ticked the box in the DTS to let SQL Server know that?sql

DTS and Excel

Is it possible to import Excel column data to a SQL table, when the
Excel column contais both numbers and strings?
I would like to import a whole Excel column to a varchar(100) column in
a SQL table. All numeric data should be converted to varchar.
In my experience DTS discards all numeric data when the destination
column is varchar(100) and the first few rows in the Excel column
contain strings. Only string data is imported, all numbers are converted
to NULL?
I used SQL Server 7 DTS and "Microsoft Excel 8.0" datasource.
JF
Try this:
SELECT *
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="d:\Mydata.xls";
User ID=Admin;Password=;Extended properties=Excel 8.0')...Sheet1$
--File Stucture
Fname
kel123
kel456
"JF" <noone@.nowhere.com> wrote in message
news:uWAnDoUzEHA.1860@.TK2MSFTNGP15.phx.gbl...
> Is it possible to import Excel column data to a SQL table, when the
> Excel column contais both numbers and strings?
> I would like to import a whole Excel column to a varchar(100) column in
> a SQL table. All numeric data should be converted to varchar.
> In my experience DTS discards all numeric data when the destination
> column is varchar(100) and the first few rows in the Excel column
> contain strings. Only string data is imported, all numbers are converted
> to NULL?
> I used SQL Server 7 DTS and "Microsoft Excel 8.0" datasource.
>
|||Hello Uri,
my intention was to get an Excel column like
First row
Second row
Third row
4
5.0
Sixth row
imported to a SQL table by SQL Server 7 DTS.
In this case I get the following result to my SQL table:
First row
Second row
Third row
<NULL>
<NULL>
Sixth row
If Excel decides that the column is a text column all numeric values
will be converted to NULL?! Can this be avoided?
How can I get both the numeric and the text rows imported to one varchar
SQL column?
I would like that the numeric to varchar conversion would be 4>>"4" not
4>>NULL.
JF
Uri Dimant wrote:

> JF
> Try this:
> SELECT *
> FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
> 'Data Source="d:\Mydata.xls";
> User ID=Admin;Password=;Extended properties=Excel 8.0')...Sheet1$
> --File Stucture
> Fname
> --
> kel123
> kel456
>
>
>
> "JF" <noone@.nowhere.com> wrote in message
> news:uWAnDoUzEHA.1860@.TK2MSFTNGP15.phx.gbl...
>
>
|||JF
I tested it as following
CREATE TABLE ToExel
(
col VARCHAR(100)
)
INSERT INTO ToExel VALUES ('Hello')
INSERT INTO ToExel VALUES ('My')
INSERT INTO ToExel VALUES ('World')
INSERT INTO ToExel VALUES ('4')
INSERT INTO ToExel VALUES ('5.0')
INSERT INTO ToExel VALUES ('The End')
Now that I created a DTS wich transfered the data into Excel file and I have
not gotten NULL's within the file
If you post your DDL + sample data i would much easier to test it.
"JF" <noone@.nowhere.com> wrote in message
news:OOob9WVzEHA.2200@.TK2MSFTNGP09.phx.gbl...
> Hello Uri,
> my intention was to get an Excel column like
> First row
> Second row
> Third row
> 4
> 5.0
> Sixth row
> imported to a SQL table by SQL Server 7 DTS.
> In this case I get the following result to my SQL table:
> First row
> Second row
> Third row
> <NULL>
> <NULL>
> Sixth row
> If Excel decides that the column is a text column all numeric values
> will be converted to NULL?! Can this be avoided?
> How can I get both the numeric and the text rows imported to one varchar
> SQL column?
> I would like that the numeric to varchar conversion would be 4>>"4" not
> 4>>NULL.
> JF
>
> Uri Dimant wrote:
>
|||Uri Dimant wrote:
> JF
> I tested it as following
> CREATE TABLE ToExel
> (
> col VARCHAR(100)
> )
> INSERT INTO ToExel VALUES ('Hello')
> INSERT INTO ToExel VALUES ('My')
> INSERT INTO ToExel VALUES ('World')
> INSERT INTO ToExel VALUES ('4')
> INSERT INTO ToExel VALUES ('5.0')
> INSERT INTO ToExel VALUES ('The End')
> Now that I created a DTS wich transfered the data into Excel file and I have
> not gotten NULL's within the file
My problem was with transfer from Excel to SQL Server, not the other way
around!

>
> If you post your DDL + sample data i would much easier to test it.
>
>
> "JF" <noone@.nowhere.com> wrote in message
> news:OOob9WVzEHA.2200@.TK2MSFTNGP09.phx.gbl...
>
>
|||JF
I see what you mean.
To workaround i added a quotations to the numbers
Like
test
'5'
within Excel file and then everything works fine.
"JF" <noone@.nowhere.com> wrote in message
news:u4uCAqVzEHA.1192@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> Uri Dimant wrote:
have[vbcol=seagreen]
> My problem was with transfer from Excel to SQL Server, not the other way
> around!
in[vbcol=seagreen]
converted
>
|||My problem is that I have many Excel tables that should be imported to
Sql Server. Excel users are populating these tables which all have the
same predefined but very free format.
I have no control over the predefined Excel table format.
Excel table format is the following:
The table has only one datasheet.
The first row always contains usable string for SQL table field name.
The first column always (rows 2..n) contains integer for row number.
The sheet contains 50 columns.
Every other cell (2-n)..(2-50) can contain numeric or string data.
Every string is always shorter than 100 characters.
If only I could import the Excel datasheet to a SQL Server table which
has 50 varchar(100) columns.
I thought this is possible with SQL Server 7 DTS!
It seems to me that every numeric cell in Excel sheet will be converted
to NULL in SQL table!?
Is it possible to import very free format Excel datasheet directly to
SQL table which contains only varchar(100) columns so that also the
numeric cells in the Excel datasheet are converted to varchar in
sensible way (1>>"1" and 2.1>>"2.1" not 1>>NULL and 2.1>>NULL).
JF
|||See the newsgroup threads at
http://groups.google.com/groups?q=39393E666E76 for some information
about registry settings for the Jet driver that will affect how column
types are determined for an Excel import.
Steve Kass
Drew University
JF wrote:

> My problem is that I have many Excel tables that should be imported to
> Sql Server. Excel users are populating these tables which all have the
> same predefined but very free format.
> I have no control over the predefined Excel table format.
> Excel table format is the following:
> The table has only one datasheet.
> The first row always contains usable string for SQL table field name.
> The first column always (rows 2..n) contains integer for row number.
> The sheet contains 50 columns.
> Every other cell (2-n)..(2-50) can contain numeric or string data.
> Every string is always shorter than 100 characters.
> If only I could import the Excel datasheet to a SQL Server table which
> has 50 varchar(100) columns.
> I thought this is possible with SQL Server 7 DTS!
> It seems to me that every numeric cell in Excel sheet will be
> converted to NULL in SQL table!?
> Is it possible to import very free format Excel datasheet directly to
> SQL table which contains only varchar(100) columns so that also the
> numeric cells in the Excel datasheet are converted to varchar in
> sensible way (1>>"1" and 2.1>>"2.1" not 1>>NULL and 2.1>>NULL).
> JF
>
|||Hello Steve,
Steve Kass wrote:

> See the newsgroup threads at
> http://groups.google.com/groups?q=39393E666E76 for some information
thanks!
Is this information valid also for SQL Server 7?
JF
|||It's the same problem as reported in the following:
http://support.microsoft.com/?id=194124
Add IMEX=1 to the connection string...something along the
lines of:
'Microsoft.Jet.OLEDB.4.0','Excel
8.0;Database=\\PathToYourFile.xls;HDR=YES;IMEX=1', YourSheetName$
-Sue
On Thu, 18 Nov 2004 12:02:09 +0200, JF <noone@.nowhere.com>
wrote:
[vbcol=seagreen]
>Hello Uri,
>my intention was to get an Excel column like
> First row
> Second row
> Third row
> 4
> 5.0
> Sixth row
>imported to a SQL table by SQL Server 7 DTS.
>In this case I get the following result to my SQL table:
> First row
> Second row
> Third row
> <NULL>
> <NULL>
> Sixth row
>If Excel decides that the column is a text column all numeric values
>will be converted to NULL?! Can this be avoided?
>How can I get both the numeric and the text rows imported to one varchar
>SQL column?
>I would like that the numeric to varchar conversion would be 4>>"4" not
>4>>NULL.
>JF
>
>Uri Dimant wrote:

DTS and Excel

Is it possible to import Excel column data to a SQL table, when the
Excel column contais both numbers and strings?
I would like to import a whole Excel column to a varchar(100) column in
a SQL table. All numeric data should be converted to varchar.
In my experience DTS discards all numeric data when the destination
column is varchar(100) and the first few rows in the Excel column
contain strings. Only string data is imported, all numbers are converted
to NULL?
I used SQL Server 7 DTS and "Microsoft Excel 8.0" datasource.JF
Try this:
SELECT *
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="d:\Mydata.xls";
User ID=Admin;Password=;Extended properties=Excel 8.0')...Sheet1$
--File Stucture
Fname
--
kel123
kel456
"JF" <noone@.nowhere.com> wrote in message
news:uWAnDoUzEHA.1860@.TK2MSFTNGP15.phx.gbl...
> Is it possible to import Excel column data to a SQL table, when the
> Excel column contais both numbers and strings?
> I would like to import a whole Excel column to a varchar(100) column in
> a SQL table. All numeric data should be converted to varchar.
> In my experience DTS discards all numeric data when the destination
> column is varchar(100) and the first few rows in the Excel column
> contain strings. Only string data is imported, all numbers are converted
> to NULL?
> I used SQL Server 7 DTS and "Microsoft Excel 8.0" datasource.
>|||Hello Uri,
my intention was to get an Excel column like
First row
Second row
Third row
4
5.0
Sixth row
imported to a SQL table by SQL Server 7 DTS.
In this case I get the following result to my SQL table:
First row
Second row
Third row
<NULL>
<NULL>
Sixth row
If Excel decides that the column is a text column all numeric values
will be converted to NULL?! Can this be avoided?
How can I get both the numeric and the text rows imported to one varchar
SQL column?
I would like that the numeric to varchar conversion would be 4>>"4" not
4>>NULL.
JF
Uri Dimant wrote:

> JF
> Try this:
> SELECT *
> FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
> 'Data Source="d:\Mydata.xls";
> User ID=Admin;Password=;Extended properties=Excel 8.0')...Sheet1$
> --File Stucture
> Fname
> --
> kel123
> kel456
>
>
>
> "JF" <noone@.nowhere.com> wrote in message
> news:uWAnDoUzEHA.1860@.TK2MSFTNGP15.phx.gbl...
>
>
>|||JF
I tested it as following
CREATE TABLE ToExel
(
col VARCHAR(100)
)
INSERT INTO ToExel VALUES ('Hello')
INSERT INTO ToExel VALUES ('My')
INSERT INTO ToExel VALUES ('World')
INSERT INTO ToExel VALUES ('4')
INSERT INTO ToExel VALUES ('5.0')
INSERT INTO ToExel VALUES ('The End')
Now that I created a DTS wich transfered the data into Excel file and I have
not gotten NULL's within the file
If you post your DDL + sample data i would much easier to test it.
"JF" <noone@.nowhere.com> wrote in message
news:OOob9WVzEHA.2200@.TK2MSFTNGP09.phx.gbl...
> Hello Uri,
> my intention was to get an Excel column like
> First row
> Second row
> Third row
> 4
> 5.0
> Sixth row
> imported to a SQL table by SQL Server 7 DTS.
> In this case I get the following result to my SQL table:
> First row
> Second row
> Third row
> <NULL>
> <NULL>
> Sixth row
> If Excel decides that the column is a text column all numeric values
> will be converted to NULL?! Can this be avoided?
> How can I get both the numeric and the text rows imported to one varchar
> SQL column?
> I would like that the numeric to varchar conversion would be 4>>"4" not
> 4>>NULL.
> JF
>
> Uri Dimant wrote:
>
>|||Uri Dimant wrote:
> JF
> I tested it as following
> CREATE TABLE ToExel
> (
> col VARCHAR(100)
> )
> INSERT INTO ToExel VALUES ('Hello')
> INSERT INTO ToExel VALUES ('My')
> INSERT INTO ToExel VALUES ('World')
> INSERT INTO ToExel VALUES ('4')
> INSERT INTO ToExel VALUES ('5.0')
> INSERT INTO ToExel VALUES ('The End')
> Now that I created a DTS wich transfered the data into Excel file and I ha
ve
> not gotten NULL's within the file
My problem was with transfer from Excel to SQL Server, not the other way
around!

>
> If you post your DDL + sample data i would much easier to test it.
>
>
> "JF" <noone@.nowhere.com> wrote in message
> news:OOob9WVzEHA.2200@.TK2MSFTNGP09.phx.gbl...
>
>|||JF
I see what you mean.
To workaround i added a quotations to the numbers
Like
test
'5'
within Excel file and then everything works fine.
"JF" <noone@.nowhere.com> wrote in message
news:u4uCAqVzEHA.1192@.tk2msftngp13.phx.gbl...
> Uri Dimant wrote:
have[vbcol=seagreen]
> My problem was with transfer from Excel to SQL Server, not the other way
> around!
>
in[vbcol=seagreen]
converted[vbcol=seagreen]
>|||My problem is that I have many Excel tables that should be imported to
Sql Server. Excel users are populating these tables which all have the
same predefined but very free format.
I have no control over the predefined Excel table format.
Excel table format is the following:
The table has only one datasheet.
The first row always contains usable string for SQL table field name.
The first column always (rows 2..n) contains integer for row number.
The sheet contains 50 columns.
Every other cell (2-n)..(2-50) can contain numeric or string data.
Every string is always shorter than 100 characters.
If only I could import the Excel datasheet to a SQL Server table which
has 50 varchar(100) columns.
I thought this is possible with SQL Server 7 DTS!
It seems to me that every numeric cell in Excel sheet will be converted
to NULL in SQL table!?
Is it possible to import very free format Excel datasheet directly to
SQL table which contains only varchar(100) columns so that also the
numeric cells in the Excel datasheet are converted to varchar in
sensible way (1>>"1" and 2.1>>"2.1" not 1>>NULL and 2.1>>NULL).
JF|||See the newsgroup threads at
http://groups.google.com/groups?q=39393E666E76 for some information
about registry settings for the Jet driver that will affect how column
types are determined for an Excel import.
Steve Kass
Drew University
JF wrote:

> My problem is that I have many Excel tables that should be imported to
> Sql Server. Excel users are populating these tables which all have the
> same predefined but very free format.
> I have no control over the predefined Excel table format.
> Excel table format is the following:
> The table has only one datasheet.
> The first row always contains usable string for SQL table field name.
> The first column always (rows 2..n) contains integer for row number.
> The sheet contains 50 columns.
> Every other cell (2-n)..(2-50) can contain numeric or string data.
> Every string is always shorter than 100 characters.
> If only I could import the Excel datasheet to a SQL Server table which
> has 50 varchar(100) columns.
> I thought this is possible with SQL Server 7 DTS!
> It seems to me that every numeric cell in Excel sheet will be
> converted to NULL in SQL table!?
> Is it possible to import very free format Excel datasheet directly to
> SQL table which contains only varchar(100) columns so that also the
> numeric cells in the Excel datasheet are converted to varchar in
> sensible way (1>>"1" and 2.1>>"2.1" not 1>>NULL and 2.1>>NULL).
> JF
>|||Hello Steve,
Steve Kass wrote:

> See the newsgroup threads at
> http://groups.google.com/groups?q=39393E666E76 for some information
thanks!
Is this information valid also for SQL Server 7?
JF|||It's the same problem as reported in the following:
http://support.microsoft.com/?id=194124
Add IMEX=1 to the connection string...something along the
lines of:
'Microsoft.Jet.OLEDB.4.0','Excel
8.0;Database=\\PathToYourFile.xls;HDR=YES;IMEX=1',YourSheetName$
-Sue
On Thu, 18 Nov 2004 12:02:09 +0200, JF <noone@.nowhere.com>
wrote:
[vbcol=seagreen]
>Hello Uri,
>my intention was to get an Excel column like
> First row
> Second row
> Third row
> 4
> 5.0
> Sixth row
>imported to a SQL table by SQL Server 7 DTS.
>In this case I get the following result to my SQL table:
> First row
> Second row
> Third row
> <NULL>
> <NULL>
> Sixth row
>If Excel decides that the column is a text column all numeric values
>will be converted to NULL?! Can this be avoided?
>How can I get both the numeric and the text rows imported to one varchar
>SQL column?
>I would like that the numeric to varchar conversion would be 4>>"4" not
>4>>NULL.
>JF
>
>Uri Dimant wrote:
>

DTS and Excel

Is it possible to import Excel column data to a SQL table, when the
Excel column contais both numbers and strings?
I would like to import a whole Excel column to a varchar(100) column in
a SQL table. All numeric data should be converted to varchar.
In my experience DTS discards all numeric data when the destination
column is varchar(100) and the first few rows in the Excel column
contain strings. Only string data is imported, all numbers are converted
to NULL?
I used SQL Server 7 DTS and "Microsoft Excel 8.0" datasource.JF
Try this:
SELECT *
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="d:\Mydata.xls";
User ID=Admin;Password=;Extended properties=Excel 8.0')...Sheet1$
--File Stucture
Fname
--
kel123
kel456
"JF" <noone@.nowhere.com> wrote in message
news:uWAnDoUzEHA.1860@.TK2MSFTNGP15.phx.gbl...
> Is it possible to import Excel column data to a SQL table, when the
> Excel column contais both numbers and strings?
> I would like to import a whole Excel column to a varchar(100) column in
> a SQL table. All numeric data should be converted to varchar.
> In my experience DTS discards all numeric data when the destination
> column is varchar(100) and the first few rows in the Excel column
> contain strings. Only string data is imported, all numbers are converted
> to NULL?
> I used SQL Server 7 DTS and "Microsoft Excel 8.0" datasource.
>|||Hello Uri,
my intention was to get an Excel column like
First row
Second row
Third row
4
5.0
Sixth row
imported to a SQL table by SQL Server 7 DTS.
In this case I get the following result to my SQL table:
First row
Second row
Third row
<NULL>
<NULL>
Sixth row
If Excel decides that the column is a text column all numeric values
will be converted to NULL?! Can this be avoided?
How can I get both the numeric and the text rows imported to one varchar
SQL column?
I would like that the numeric to varchar conversion would be 4>>"4" not
4>>NULL.
JF
Uri Dimant wrote:
> JF
> Try this:
> SELECT *
> FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
> 'Data Source="d:\Mydata.xls";
> User ID=Admin;Password=;Extended properties=Excel 8.0')...Sheet1$
> --File Stucture
> Fname
> --
> kel123
> kel456
>
>
>
> "JF" <noone@.nowhere.com> wrote in message
> news:uWAnDoUzEHA.1860@.TK2MSFTNGP15.phx.gbl...
>>Is it possible to import Excel column data to a SQL table, when the
>>Excel column contais both numbers and strings?
>>I would like to import a whole Excel column to a varchar(100) column in
>>a SQL table. All numeric data should be converted to varchar.
>>In my experience DTS discards all numeric data when the destination
>>column is varchar(100) and the first few rows in the Excel column
>>contain strings. Only string data is imported, all numbers are converted
>>to NULL?
>>I used SQL Server 7 DTS and "Microsoft Excel 8.0" datasource.
>
>|||JF
I tested it as following
CREATE TABLE ToExel
(
col VARCHAR(100)
)
INSERT INTO ToExel VALUES ('Hello')
INSERT INTO ToExel VALUES ('My')
INSERT INTO ToExel VALUES ('World')
INSERT INTO ToExel VALUES ('4')
INSERT INTO ToExel VALUES ('5.0')
INSERT INTO ToExel VALUES ('The End')
Now that I created a DTS wich transfered the data into Excel file and I have
not gotten NULL's within the file
If you post your DDL + sample data i would much easier to test it.
"JF" <noone@.nowhere.com> wrote in message
news:OOob9WVzEHA.2200@.TK2MSFTNGP09.phx.gbl...
> Hello Uri,
> my intention was to get an Excel column like
> First row
> Second row
> Third row
> 4
> 5.0
> Sixth row
> imported to a SQL table by SQL Server 7 DTS.
> In this case I get the following result to my SQL table:
> First row
> Second row
> Third row
> <NULL>
> <NULL>
> Sixth row
> If Excel decides that the column is a text column all numeric values
> will be converted to NULL?! Can this be avoided?
> How can I get both the numeric and the text rows imported to one varchar
> SQL column?
> I would like that the numeric to varchar conversion would be 4>>"4" not
> 4>>NULL.
> JF
>
> Uri Dimant wrote:
> > JF
> > Try this:
> >
> > SELECT *
> > FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
> > 'Data Source="d:\Mydata.xls";
> > User ID=Admin;Password=;Extended properties=Excel 8.0')...Sheet1$
> >
> > --File Stucture
> > Fname
> > --
> > kel123
> > kel456
> >
> >
> >
> >
> >
> >
> > "JF" <noone@.nowhere.com> wrote in message
> > news:uWAnDoUzEHA.1860@.TK2MSFTNGP15.phx.gbl...
> >
> >>Is it possible to import Excel column data to a SQL table, when the
> >>Excel column contais both numbers and strings?
> >>I would like to import a whole Excel column to a varchar(100) column in
> >>a SQL table. All numeric data should be converted to varchar.
> >>
> >>In my experience DTS discards all numeric data when the destination
> >>column is varchar(100) and the first few rows in the Excel column
> >>contain strings. Only string data is imported, all numbers are converted
> >>to NULL?
> >>
> >>I used SQL Server 7 DTS and "Microsoft Excel 8.0" datasource.
> >>
> >
> >
> >
>|||Uri Dimant wrote:
> JF
> I tested it as following
> CREATE TABLE ToExel
> (
> col VARCHAR(100)
> )
> INSERT INTO ToExel VALUES ('Hello')
> INSERT INTO ToExel VALUES ('My')
> INSERT INTO ToExel VALUES ('World')
> INSERT INTO ToExel VALUES ('4')
> INSERT INTO ToExel VALUES ('5.0')
> INSERT INTO ToExel VALUES ('The End')
> Now that I created a DTS wich transfered the data into Excel file and I have
> not gotten NULL's within the file
My problem was with transfer from Excel to SQL Server, not the other way
around!
>
> If you post your DDL + sample data i would much easier to test it.
>
>
> "JF" <noone@.nowhere.com> wrote in message
> news:OOob9WVzEHA.2200@.TK2MSFTNGP09.phx.gbl...
>>Hello Uri,
>>my intention was to get an Excel column like
>> First row
>> Second row
>> Third row
>> 4
>> 5.0
>> Sixth row
>>imported to a SQL table by SQL Server 7 DTS.
>>In this case I get the following result to my SQL table:
>> First row
>> Second row
>> Third row
>> <NULL>
>> <NULL>
>> Sixth row
>>If Excel decides that the column is a text column all numeric values
>>will be converted to NULL?! Can this be avoided?
>>How can I get both the numeric and the text rows imported to one varchar
>>SQL column?
>>I would like that the numeric to varchar conversion would be 4>>"4" not
>>4>>NULL.
>>JF
>>
>>Uri Dimant wrote:
>>
>>JF
>>Try this:
>>SELECT *
>>FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
>> 'Data Source="d:\Mydata.xls";
>> User ID=Admin;Password=;Extended properties=Excel 8.0')...Sheet1$
>>--File Stucture
>>Fname
>>--
>>kel123
>>kel456
>>
>>
>>
>>"JF" <noone@.nowhere.com> wrote in message
>>news:uWAnDoUzEHA.1860@.TK2MSFTNGP15.phx.gbl...
>>
>>Is it possible to import Excel column data to a SQL table, when the
>>Excel column contais both numbers and strings?
>>I would like to import a whole Excel column to a varchar(100) column in
>>a SQL table. All numeric data should be converted to varchar.
>>In my experience DTS discards all numeric data when the destination
>>column is varchar(100) and the first few rows in the Excel column
>>contain strings. Only string data is imported, all numbers are converted
>>to NULL?
>>I used SQL Server 7 DTS and "Microsoft Excel 8.0" datasource.
>>
>>
>|||JF
I see what you mean.
To workaround i added a quotations to the numbers
Like
test
'5'
within Excel file and then everything works fine.
"JF" <noone@.nowhere.com> wrote in message
news:u4uCAqVzEHA.1192@.tk2msftngp13.phx.gbl...
> Uri Dimant wrote:
> > JF
> > I tested it as following
> > CREATE TABLE ToExel
> > (
> > col VARCHAR(100)
> > )
> > INSERT INTO ToExel VALUES ('Hello')
> > INSERT INTO ToExel VALUES ('My')
> > INSERT INTO ToExel VALUES ('World')
> > INSERT INTO ToExel VALUES ('4')
> > INSERT INTO ToExel VALUES ('5.0')
> > INSERT INTO ToExel VALUES ('The End')
> >
> > Now that I created a DTS wich transfered the data into Excel file and I
have
> > not gotten NULL's within the file
> My problem was with transfer from Excel to SQL Server, not the other way
> around!
> >
> >
> > If you post your DDL + sample data i would much easier to test it.
> >
> >
> >
> >
> > "JF" <noone@.nowhere.com> wrote in message
> > news:OOob9WVzEHA.2200@.TK2MSFTNGP09.phx.gbl...
> >
> >>Hello Uri,
> >>
> >>my intention was to get an Excel column like
> >> First row
> >> Second row
> >> Third row
> >> 4
> >> 5.0
> >> Sixth row
> >>imported to a SQL table by SQL Server 7 DTS.
> >>
> >>In this case I get the following result to my SQL table:
> >> First row
> >> Second row
> >> Third row
> >> <NULL>
> >> <NULL>
> >> Sixth row
> >>
> >>If Excel decides that the column is a text column all numeric values
> >>will be converted to NULL?! Can this be avoided?
> >>How can I get both the numeric and the text rows imported to one varchar
> >>SQL column?
> >>I would like that the numeric to varchar conversion would be 4>>"4" not
> >>4>>NULL.
> >>
> >>JF
> >>
> >>
> >>Uri Dimant wrote:
> >>
> >>
> >>JF
> >>Try this:
> >>
> >>SELECT *
> >>FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
> >> 'Data Source="d:\Mydata.xls";
> >> User ID=Admin;Password=;Extended properties=Excel 8.0')...Sheet1$
> >>
> >>--File Stucture
> >>Fname
> >>--
> >>kel123
> >>kel456
> >>
> >>
> >>
> >>
> >>
> >>
> >>"JF" <noone@.nowhere.com> wrote in message
> >>news:uWAnDoUzEHA.1860@.TK2MSFTNGP15.phx.gbl...
> >>
> >>
> >>Is it possible to import Excel column data to a SQL table, when the
> >>Excel column contais both numbers and strings?
> >>I would like to import a whole Excel column to a varchar(100) column
in
> >>a SQL table. All numeric data should be converted to varchar.
> >>
> >>In my experience DTS discards all numeric data when the destination
> >>column is varchar(100) and the first few rows in the Excel column
> >>contain strings. Only string data is imported, all numbers are
converted
> >>to NULL?
> >>
> >>I used SQL Server 7 DTS and "Microsoft Excel 8.0" datasource.
> >>
> >>
> >>
> >>
> >
> >
>|||My problem is that I have many Excel tables that should be imported to
Sql Server. Excel users are populating these tables which all have the
same predefined but very free format.
I have no control over the predefined Excel table format.
Excel table format is the following:
The table has only one datasheet.
The first row always contains usable string for SQL table field name.
The first column always (rows 2..n) contains integer for row number.
The sheet contains 50 columns.
Every other cell (2-n)..(2-50) can contain numeric or string data.
Every string is always shorter than 100 characters.
If only I could import the Excel datasheet to a SQL Server table which
has 50 varchar(100) columns.
I thought this is possible with SQL Server 7 DTS!
It seems to me that every numeric cell in Excel sheet will be converted
to NULL in SQL table!?
Is it possible to import very free format Excel datasheet directly to
SQL table which contains only varchar(100) columns so that also the
numeric cells in the Excel datasheet are converted to varchar in
sensible way (1>>"1" and 2.1>>"2.1" not 1>>NULL and 2.1>>NULL).
JF|||See the newsgroup threads at
http://groups.google.com/groups?q=39393E666E76 for some information
about registry settings for the Jet driver that will affect how column
types are determined for an Excel import.
Steve Kass
Drew University
JF wrote:
> My problem is that I have many Excel tables that should be imported to
> Sql Server. Excel users are populating these tables which all have the
> same predefined but very free format.
> I have no control over the predefined Excel table format.
> Excel table format is the following:
> The table has only one datasheet.
> The first row always contains usable string for SQL table field name.
> The first column always (rows 2..n) contains integer for row number.
> The sheet contains 50 columns.
> Every other cell (2-n)..(2-50) can contain numeric or string data.
> Every string is always shorter than 100 characters.
> If only I could import the Excel datasheet to a SQL Server table which
> has 50 varchar(100) columns.
> I thought this is possible with SQL Server 7 DTS!
> It seems to me that every numeric cell in Excel sheet will be
> converted to NULL in SQL table!?
> Is it possible to import very free format Excel datasheet directly to
> SQL table which contains only varchar(100) columns so that also the
> numeric cells in the Excel datasheet are converted to varchar in
> sensible way (1>>"1" and 2.1>>"2.1" not 1>>NULL and 2.1>>NULL).
> JF
>|||Hello Steve,
Steve Kass wrote:
> See the newsgroup threads at
> http://groups.google.com/groups?q=39393E666E76 for some information
thanks!
Is this information valid also for SQL Server 7?
JF|||It's the same problem as reported in the following:
http://support.microsoft.com/?id=194124
Add IMEX=1 to the connection string...something along the
lines of:
'Microsoft.Jet.OLEDB.4.0','Excel
8.0;Database=\\PathToYourFile.xls;HDR=YES;IMEX=1',YourSheetName$
-Sue
On Thu, 18 Nov 2004 12:02:09 +0200, JF <noone@.nowhere.com>
wrote:
>Hello Uri,
>my intention was to get an Excel column like
> First row
> Second row
> Third row
> 4
> 5.0
> Sixth row
>imported to a SQL table by SQL Server 7 DTS.
>In this case I get the following result to my SQL table:
> First row
> Second row
> Third row
> <NULL>
> <NULL>
> Sixth row
>If Excel decides that the column is a text column all numeric values
>will be converted to NULL?! Can this be avoided?
>How can I get both the numeric and the text rows imported to one varchar
>SQL column?
>I would like that the numeric to varchar conversion would be 4>>"4" not
>4>>NULL.
>JF
>
>Uri Dimant wrote:
>> JF
>> Try this:
>> SELECT *
>> FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
>> 'Data Source="d:\Mydata.xls";
>> User ID=Admin;Password=;Extended properties=Excel 8.0')...Sheet1$
>> --File Stucture
>> Fname
>> --
>> kel123
>> kel456
>>
>>
>>
>> "JF" <noone@.nowhere.com> wrote in message
>> news:uWAnDoUzEHA.1860@.TK2MSFTNGP15.phx.gbl...
>>Is it possible to import Excel column data to a SQL table, when the
>>Excel column contais both numbers and strings?
>>I would like to import a whole Excel column to a varchar(100) column in
>>a SQL table. All numeric data should be converted to varchar.
>>In my experience DTS discards all numeric data when the destination
>>column is varchar(100) and the first few rows in the Excel column
>>contain strings. Only string data is imported, all numbers are converted
>>to NULL?
>>I used SQL Server 7 DTS and "Microsoft Excel 8.0" datasource.
>>
>>|||I suspect it will work for SQL Server 7, but it's just a guess. I
haven't had a 7.0 server to test things on for some time now.
SK
JF wrote:
> Hello Steve,
> Steve Kass wrote:
>> See the newsgroup threads at
>> http://groups.google.com/groups?q=39393E666E76 for some information
> thanks!
> Is this information valid also for SQL Server 7?
> JF
>|||Thanks Steve and Sue,
the mystical "IMEX=1" solved my problem.
I didn't even have to make any registry changes.
JF

DTS - Import Text file - varying column count

I receive a pipe delimited file without headers, and a header and trailer
row added as a check to each file. When I say header I don't mean column
headings, I mean a row of information that verifies the contents of the file
(example below)
The header info contains 5 columns, trailer info contains 7 columns, and the
normal data contains 28 columns.
The import succeeds when there is data (the header/trailer rows inserts NULL
into the remaining columns which is fine), however when there are no rows,
only the header and trailer rows are in the file. This fails as the columns
from 8 onwards are not there.
Firstly, can I make the DTS not "fail" if the full number of columns are
present? Currently I'm using "on completion" at the upload which continues
the DTS processing, but it fails at the end.
Second option - can I do a quick check to see if there are only 2 rows in
the file, and if so, maybe throw in a line of 27 pipes in to make the DTS
work, then delete the NULL row?
example file:
IMPORT_HEADER|HR|2006|04|18
datarows|etc|etc|28 columns in total
datarows|etc|etc|28 columns in total
datarows|etc|etc|28 columns in total
IMPORT_TRAILER|TR|2006|04|18|3|123.99
Thanks,Hi
You may find better advice in the DTS newsgroup
microsoft.public.sqlserver.dts , but you may be able to use an activeX
transformation to determine if you are reading a header row, data row or
footer assuming that the first column defines the row type.
Check out http://www.sqldts.com/default.aspx?279 and possibly
http://www.sqldts.com/default.aspx?266 and
http://www.sqldts.com/default.aspx?282. You should also be able to skip the
insertion of the header and trailer by returning DTSTransformStat_SkipInsert
or DTSTransformStat_NoMoreRows (if the row is the footer or the row is the
header and it says there are no rows within the file!) to make it cleaner
see the topic "DTSTransformStatus" in Books online for more.
John
"Ben Rum" <bundyrum75@.yahoo.com> wrote in message
news:OM91g.40851$Ph2.9628@.newsfe4-gui.ntli.net...
>I receive a pipe delimited file without headers, and a header and trailer
>row added as a check to each file. When I say header I don't mean column
>headings, I mean a row of information that verifies the contents of the
>file (example below)
> The header info contains 5 columns, trailer info contains 7 columns, and
> the normal data contains 28 columns.
> The import succeeds when there is data (the header/trailer rows inserts
> NULL into the remaining columns which is fine), however when there are no
> rows, only the header and trailer rows are in the file. This fails as the
> columns from 8 onwards are not there.
> Firstly, can I make the DTS not "fail" if the full number of columns are
> present? Currently I'm using "on completion" at the upload which continues
> the DTS processing, but it fails at the end.
> Second option - can I do a quick check to see if there are only 2 rows in
> the file, and if so, maybe throw in a line of 27 pipes in to make the DTS
> work, then delete the NULL row?
> example file:
> IMPORT_HEADER|HR|2006|04|18
> datarows|etc|etc|28 columns in total
> datarows|etc|etc|28 columns in total
> datarows|etc|etc|28 columns in total
> IMPORT_TRAILER|TR|2006|04|18|3|123.99
> Thanks,
>

DTS - Access Commection Properties

Does anyone know what the different values are (and what they mean) on the "Database Locking Mode" column off of the advanced button on a microsoft access connection within a DTS package on SQL server 7.0 ?

It's a 4 byte signed integer type with a default value of zero

Cheers,

DuncanMaybe this (http://www.able-consulting.com/tech.htm) will help?

dtproperties system table changed to user table in sysobjects?

Hi Gurus,
I occasionally use some T-SQL to add a column or trigger to all usertables t
ables within a database.
It has always worked before but on one database I am having a problem becaus
e the "dtproperties" system table appears to have been changed to a user tab
le in the "sysobjects" table and is being returned in along with all the use
rtables by:
select [name] from dbo.sysobjects where OBJECTPROPERTY(id, N'IsUserTable') = 1
I can skip that table easily enough, but can anyone tell me why this may ha
ve changed and if I should change it back to a system table, if so how would
I go about that?
Regards,
Pauldtproperties is created when you create a database diagram. It is marked as
a user table by default so I suggest you exclude it by name in your script.
David Portas
SQL Server MVP
--
"Paul B" <paul.bunting@.archsoftnet.com> wrote in message
news:%234h2UcmxFHA.2728@.TK2MSFTNGP14.phx.gbl...
Hi Gurus,
I occasionally use some T-SQL to add a column or trigger to all usertables
tables within a database.
It has always worked before but on one database I am having a problem
because the "dtproperties" system table appears to have been changed to a
user table in the "sysobjects" table and is being returned in along with all
the usertables by:
select [name] from dbo.sysobjects where OBJECTPROPERTY(id,
N'IsUserTable') = 1
I can skip that table easily enough, but can anyone tell me why this may
have changed and if I should change it back to a system table, if so how
would I go about that?
Regards,
Paul|||... or also use IsMsShipped:
select [name]
from dbo.sysobjects
where OBJECTPROPERTY(id, N'IsUserTable') = 1
AND OBJECTPROPERTY(id, N'IsMSShipped') = 0
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:2f2dnarVIKxH5aPenZ2dnUVZ8t2dnZ2d@.gi
ganews.com...
> dtproperties is created when you create a database diagram. It is marked a
s a user table by
> default so I suggest you exclude it by name in your script.
> --
> David Portas
> SQL Server MVP
> --
> "Paul B" <paul.bunting@.archsoftnet.com> wrote in message
> news:%234h2UcmxFHA.2728@.TK2MSFTNGP14.phx.gbl...
> Hi Gurus,
> I occasionally use some T-SQL to add a column or trigger to all usertables
tables within a
> database.
> It has always worked before but on one database I am having a problem beca
use the "dtproperties"
> system table appears to have been changed to a user table in the "sysobjec
ts" table and is being
> returned in along with all the usertables by:
> select [name] from dbo.sysobjects where OBJECTPROPERTY(id, N'IsUserTable'
) = 1
> I can skip that table easily enough, but can anyone tell me why this may
have changed and if I
> should change it back to a system table, if so how would I go about that?
> Regards,
> Paul
>|||Thanks,
Was a bit puzzled as to the fact that it was shown as a system table by
Enterprise Manager and returned as a user table by "where
OBJECTPROPERTY(id, N'IsUserTable') = 1".
David mentioned it is added when you create a database diagram, is this
always the case?... the database I had the error did not have any diagrams
and never has (it was only created a couple of days ago), and a duplicate
database created at the same time returned "dtproperties" as a system table!
I had included an if statment to exclude that table, will probably switch it
to "OBJECTPROPERTY(id, N'IsMSShipped')" as suggested by Tibor... is there
any other tables that could be both system and user at the same time that
this problem may occur with?
Regards,
Paul
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eW7LtgpxFHA.2540@.TK2MSFTNGP09.phx.gbl...
> ... or also use IsMsShipped:
> select [name]
> from dbo.sysobjects
> where OBJECTPROPERTY(id, N'IsUserTable') = 1
> AND OBJECTPROPERTY(id, N'IsMSShipped') = 0
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> news:2f2dnarVIKxH5aPenZ2dnUVZ8t2dnZ2d@.gi
ganews.com...
>|||EM is hard-wired to show dtproperties as a system table... The table might b
e created by other EM
GUI elements, like the design table dialog etc.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Paul B" <paul.no-email-please.bunting@.archsoftnet.com> wrote in message
news:Oduh6AsxFHA.2848@.TK2MSFTNGP15.phx.gbl...
> Thanks,
> Was a bit puzzled as to the fact that it was shown as a system table by En
terprise Manager and
> returned as a user table by "where OBJECTPROPERTY(id, N'IsUserTable') = 1
".
> David mentioned it is added when you create a database diagram, is this al
ways the case?... the
> database I had the error did not have any diagrams and never has (it was o
nly created a couple of
> days ago), and a duplicate database created at the same time returned "dtp
roperties" as a system
> table!
> I had included an if statment to exclude that table, will probably switch
it to
> "OBJECTPROPERTY(id, N'IsMSShipped')" as suggested by Tibor... is there any
other tables that could
> be both system and user at the same time that this problem may occur with?
> Regards,
> Paul
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n message
> news:eW7LtgpxFHA.2540@.TK2MSFTNGP09.phx.gbl...
>

DT_GUID to DT_WSTR yields braces around uniqueidentifier

I'm using a Lookup component to add a DT_GUID column named cat_id to a data flow, which is used in a downstream Derived Column component to add a DT_WSTR column named value. The DC expression simply casts the uniqueidentifier to the desired type: (DT_WSTR, 434)cat_id

But when the values are persisted in the destination table's nvarchar(434) column, they have braces around them, e.g. {F475DB7F-5CB0-4EE1-9BF2-758C77D7A6D7}

What is introducing those braces, and what do I need to do to prevent it?

You may remove the braces by using SUBSTRING function in the DC expression:
http://msdn2.microsoft.com/en-us/library/ms137541.aspx
SUBSTRING((DT_WSTR, 434)cat_id, 1, 40)

(if I remember GUID length, maybe the last param should be 39 or something like this).

|||

Michael Entin - MSFT wrote:

You may remove the braces by using SUBSTRING function in the DC expression:
http://msdn2.microsoft.com/en-us/library/ms137541.aspx

SUBSTRING((DT_WSTR, 434)cat_id, 1, 40)

(if I remember GUID length, maybe the last param should be 39 or something like this).

Thanks, but why is that even necesssary? In Transact SQL, when a uniqueidentifier is cast to a varchar/nvarchar the result doesn't have braces around the value.

|||It is very common to add braces around GUID representation, although it is of course unfortunate that SSIS implemented it differently than T-SQL.|||

Michael Entin - MSFT wrote:

It is very common to add braces around GUID representation, although it is of course unfortunate that SSIS implemented it differently than T-SQL.

Thanks for following up.

BTW, GUIDs are 36 characters and strings are 1-indexed, so the expression I'm using is:

SUBSTRING((DT_WSTR,434)cat_id,2,36)