|
Home > Archive > MS SQL Server > October 2006 > SSIS - Importing Flat File - Data Types
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
SSIS - Importing Flat File - Data Types
|
|
| MilkBottle 2006-10-31, 12:15 am |
|
Trying to import a flat file into SQL database using SSIS import wizard.
How do i convert a column into an integer.
I was told to edit the SQL script and I used
Create Table.....
(Acc varchar(12) not null,
etc...
Convert(int, [SalesQty]) As NewQty,
CustomerName(nvarcha
r(12), not null)
but it doesn't work....
HELP......................
--
Milk Bottle
| |
| John Bell 2006-10-31, 7:12 pm |
| Hi
Do you get an error message? Is all your data integer?
John
"MilkBottle" wrote:
>
> Trying to import a flat file into SQL database using SSIS import wizard.
> How do i convert a column into an integer.
>
> I was told to edit the SQL script and I used
> Create Table.....
> (Acc varchar(12) not null,
> etc...
> Convert(int, [SalesQty]) As NewQty,
> CustomerName(nvarcha
r(12), not null)
>
> but it doesn't work....
>
> HELP......................
>
> --
> Milk Bottle
| |
| MilkBottle 2006-10-31, 7:12 pm |
|
John
Cheers for the reply...
Data is a sales history table with a mixture of customer addresses and order
details. That particular column is integer but has some blanks.
Error Message Below along with convert statement;
convert(int,[Rep Qty]) as Red,
"Incorrect syntax near the keyword 'convert'.". Possible failure reasons:
Problems with the query, "ResultSet" property not set correctly, parameters
not set correctly, or connection not established correctly.
Help much appreciated
--
Milk Bottle
"John Bell" wrote:
[color=darkred]
> Hi
>
> Do you get an error message? Is all your data integer?
>
> John
>
> "MilkBottle" wrote:
>
| |
| John Bell 2006-10-31, 7:12 pm |
| Hi
I think this is probably because there are values that can not be converted
to an integer, but if you a value of spaces this should be converted to 0.
You may want to test column values by selecting all the values where
isnumeric (col) =0 and see what is returned.
John
"MilkBottle" wrote:
[color=darkred]
>
> John
>
> Cheers for the reply...
>
> Data is a sales history table with a mixture of customer addresses and order
> details. That particular column is integer but has some blanks.
> Error Message Below along with convert statement;
>
> convert(int,[Rep Qty]) as Red,
>
> "Incorrect syntax near the keyword 'convert'.". Possible failure reasons:
> Problems with the query, "ResultSet" property not set correctly, parameters
> not set correctly, or connection not established correctly.
>
> Help much appreciated
> --
> Milk Bottle
>
>
> "John Bell" wrote:
>
| |
| MilkBottle 2006-10-31, 7:12 pm |
| john
jackpot. The column contains spaces...hence the reason it won't convert...
understand how to use the isnumeric statement and case statements.
Where would i enter these in the data flow process...to convert the spaces
to 0 and if i have a space in a date column would i convert to 0..
Cheers for your help.
--
Milk Bottle
"John Bell" wrote:
[color=darkred]
> Hi
>
> I think this is probably because there are values that can not be converted
> to an integer, but if you a value of spaces this should be converted to 0.
> You may want to test column values by selecting all the values where
> isnumeric (col) =0 and see what is returned.
>
> John
>
> "MilkBottle" wrote:
>
| |
| John Bell 2006-10-31, 7:13 pm |
| Hi
Spaces should convert, although (I think) this may possibly be affected by
your ansi setting. Also use the ASCII function to get the ascii value of the
first character. If you ran
SELECT CONVERT(int,intval),
ISNUMERIC(intval), ASCII(intval)
FROM ( SELECT '1' as intval
UNION ALL SELECT SPACE(1)
UNION ALL SELECT SPACE(3)
UNION ALL SELECT SPACE(0) ) A
I get
----------- ----------- -----------
1 1 49
0 0 32
0 0 32
0 0 NULL
To convert it maybe easiest to use a view
CREATE VIEW myview
AS
SELECT CASE WHEN ISNUMBERIC(intval)=1
THEN intval ELSE 0 END AS intval
FROM Mytable
John
"MilkBottle" wrote:
[color=darkred]
> john
>
> jackpot. The column contains spaces...hence the reason it won't convert...
> understand how to use the isnumeric statement and case statements.
> Where would i enter these in the data flow process...to convert the spaces
> to 0 and if i have a space in a date column would i convert to 0..
>
> Cheers for your help.
>
> --
> Milk Bottle
>
>
> "John Bell" wrote:
>
|
|
|
|
|