 |
|
|
|
Next: ANSI_NULL.
|
| Author |
Message |
External
Since: Apr 06, 2006
Posts: 10
|
(Msg. 1) Posted: Tue Feb 10, 2009 6:37 am
Post subject: Field becomes null in a select statement after import
Archived from groups: microsoft>public>sqlserver>programming (more info?)
|
|
|
Hi,
I am trying to import the data from Excel to SQL. The first column is
the employee ID, in which it can be numeric e.g 12345 or in alpha e.g
CATETY
When I run the following SQL, those Alpha Employee ID become null. Is
there anything I can do to avoid it? I have already format the column
to be text, but it doesn't help. And even I create a table where
employee id is a varchar field, it is still the same.
select cast(employee_id as varchar(15)), first, last, email
FROM OPENDATASOURCE ('Microsoft.Jet.OLEDB.4.0',
'Data Source=filename.xls;Extended
Properties=Excel 8.0' )...[Sheet1$]
Please advice. Your help would be greatly appreciated. |
|
| Back to top |
|
 | |
External
Since: Apr 06, 2006
Posts: 10
|
(Msg. 2) Posted: Tue Feb 10, 2009 7:21 am
Post subject: Re: Field becomes null in a select statement after import [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
Thank you so much, you are the best.
On Feb 10, 10:04 am, Plamen Ratchev wrote:
> You can use the IMEX property (Import/Export Mode). The IMEX value
> instructs the provider how to convert/format the data to fit the column
> type. Setting IMEX to 1 tells the provider to read the mixed data
> columns as text.
>
> SELECT CAST(employee_id AS VARCHAR(15)),
> first, last, email
> FROM OPENDATASOURCE ('Microsoft.Jet.OLEDB.4.0',
> 'Data Source=filename.xls;Extended
> Properties="Excel 8.0;IMEX=1"' )...[Sheet1$]
>
> --
> Plamen Ratchevhttp://www.SQLStudio.com |
|
| Back to top |
|
| |
External
Since: Apr 06, 2006
Posts: 10
|
(Msg. 3) Posted: Tue Feb 10, 2009 7:27 am
Post subject: Re: Field becomes null in a select statement after import [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
Oh actually, I spoke too fast. Ff the first row of the employee_id is
a numeric, I will get null for the latter case if it comes across a
alpha employee id. I can only get the right format, only when I move
the alpha numeric to the 1st row. What do you think?
On Feb 10, 10:04 am, Plamen Ratchev wrote:
> You can use the IMEX property (Import/Export Mode). The IMEX value
> instructs the provider how to convert/format the data to fit the column
> type. Setting IMEX to 1 tells the provider to read the mixed data
> columns as text.
>
> SELECT CAST(employee_id AS VARCHAR(15)),
> first, last, email
> FROM OPENDATASOURCE ('Microsoft.Jet.OLEDB.4.0',
> 'Data Source=filename.xls;Extended
> Properties="Excel 8.0;IMEX=1"' )...[Sheet1$]
>
> --
> Plamen Ratchevhttp://www.SQLStudio.com >> Stay informed about: Field becomes null in a select statement after import |
|
| Back to top |
|
| |
External
Since: Apr 06, 2006
Posts: 10
|
(Msg. 4) Posted: Tue Feb 10, 2009 8:44 am
Post subject: Re: Field becomes null in a select statement after import [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
Here is what happens:
If my alpha employee ID is in Row 2, the import is still working fine.
But since my first alpha employee ID appears in Row 18, it doesn't
recognize any more.
On Feb 10, 10:52 am, Plamen Ratchev wrote:
> This is strange, I always use IMEX=1 and it works because in essence it
> sets the imported value format to text. You can try IMEX=0 and IMEX=2,
> but those I believe use the first few rows from the source to determine
> the data type. Also, you can use HDR=Yes or HDR=No to set if the first
> row contains headers or not.
>
> --
> Plamen Ratchevhttp://www.SQLStudio.com >> Stay informed about: Field becomes null in a select statement after import |
|
| Back to top |
|
| |
External
Since: Apr 06, 2006
Posts: 10
|
(Msg. 5) Posted: Tue Feb 10, 2009 9:17 am
Post subject: Re: Field becomes null in a select statement after import [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
Thanks. But I am pretty sure I have it right, as if IMEX=1 isn't
there, I don't even see the alpha employee id appears. Currently, if
the first alpha numeric employee id is in 2nd row, it works, but not
down to row 18.
On Feb 10, 12:03 pm, Plamen Ratchev wrote:
> Please check that you added the option with proper syntax. Note the
> double quotes since those are two options listed for extended properties:
>
> Extended Properties="Excel 8.0;IMEX=1"
>
> The way you describe it seems like IMEX=1 is ignored.
>
> --
> Plamen Ratchevhttp://www.SQLStudio.com >> Stay informed about: Field becomes null in a select statement after import |
|
| Back to top |
|
| |
External
Since: Aug 20, 2008
Posts: 672
|
(Msg. 6) Posted: Tue Feb 10, 2009 10:04 am
Post subject: Re: Field becomes null in a select statement after import [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
You can use the IMEX property (Import/Export Mode). The IMEX value
instructs the provider how to convert/format the data to fit the column
type. Setting IMEX to 1 tells the provider to read the mixed data
columns as text.
SELECT CAST(employee_id AS VARCHAR(15)),
first, last, email
FROM OPENDATASOURCE ('Microsoft.Jet.OLEDB.4.0',
'Data Source=filename.xls;Extended
Properties="Excel 8.0;IMEX=1"' )...[Sheet1$]
--
Plamen Ratchev
http://www.SQLStudio.com >> Stay informed about: Field becomes null in a select statement after import |
|
| Back to top |
|
| |
External
Since: Aug 20, 2008
Posts: 672
|
(Msg. 7) Posted: Tue Feb 10, 2009 10:52 am
Post subject: Re: Field becomes null in a select statement after import [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
|
|
| Back to top |
|
| |
External
Since: Aug 20, 2008
Posts: 672
|
(Msg. 8) Posted: Tue Feb 10, 2009 12:03 pm
Post subject: Re: Field becomes null in a select statement after import [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
|
|
| Back to top |
|
| |
| Related Topics: | How convert NULL to empty string in select statement? - Hello: I was wondering, given the following select statement is there an easy was to return an empty string for the FirstName field if the value is NULL? I'm not quite sure of the necessary syntax: // If FirstName IS NULL, FirstName should = ''..
Select statement comparing a uniqueidentifier field - Hi, I'm attempting to compare information in a uniqueidentifier field using a select statement and am having fits trying to get it to work. Here's a sample of my SQL statement. SELECT Organization.TreatAsTypeOf_ENUM, Organization.OrganizationID, ..
testing for null and not null in case statement - I want to know whether a field has a date or not, and then group by that result in a query. I'm trying: case activate_date when null then 'active next bill date' when not null then 'active' end and it didn't like that. Didn't like that "not"...
How to select specific line in select statement from a giv.. - TableName : TableX Column 1 : name (single line) Column 2 : Comments (multiple line) In "Column2" i have comments which are all about roughtly 20 lines but can vary. What i want to do is for example i want to write a select query which will di...
select all columns that are not null - Is there a way to add a where clause to this statement SELECT * FROM tableName so that you only return columns that don't have a null in each row? I don't want blank columns returned......but this will change each month so I don't want to hard code th... |
|
You can post new topics in this forum
You can reply to topics in this forum
You can edit your posts in this forum
You can delete your posts in this forum
You can vote in polls in this forum
|
|
|
|
|
|
|
FAQ
Search
Profile
Private Messages
Log in
