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