May 19, 2008 at 12:28 pm
I've imported a table from text file.
I am then taking the contents of this table and putting it into a new table that I have created. I'm having real problems getting the numbers that are in varchar format into a numeric form. I keep getting error messages. I've tried casting and converting. I must be doing something wrong. The varchar numbers are like:
8.465549541
I would quite like these in a precise numberic form.
Any suggestions?
May 19, 2008 at 12:44 pm
I am assuming that the data value provided is a sample of the data you are importing. Also, what error messages are you getting during the convert process? What does your current code look like?
😎
May 19, 2008 at 12:50 pm
The error that I get when I try to manually update the table is:
'Table_name' table
- Unable to modify table.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]Error converting data type varchar to numeric.
May 19, 2008 at 12:53 pm
Let me get this straight. You imported the data into a table with all varchar columns, and you are now trying to modify the data type of the columns in the table?
😎
May 19, 2008 at 12:55 pm
ALTER PROCEDURE Populations_1991_onwards
AS
CREATE TABLE [Table_name] (
[Key_m] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,
[GeographicCode] [varchar] (10) COLLATE Latin1_General_CI_AS NULL ,
[Year_census_estimate] [int] NULL ,
[Sex] [int] NULL ,
[Age_start] [int] NULL ,
[Age_end] [int] NULL ,
[Age_range] [varchar] (10) COLLATE Latin1_General_CI_AS NULL ,
[Person_count] [decimal](18, 9) NULL
) ON [PRIMARY]
INSERT INTO [Table_name](
[GeographicCode] ,
[Year_census_estimate] ,
[Sex],
[Age_start] ,
[Age_end] ,
[Age_range] ,
[Person_count] )
SELECT [Col002] ,
[Col003] ,
[Col004],
[Col005] ,
[Col006] ,
[Col007] ,
[Col008]
--Cast([Col008] as decimal)
FROM Just_imported_table
May 19, 2008 at 1:02 pm
Something does not look right in your description of the problem. The first post suggested that you are reloading data from one table (with varchar fields) to another table ( with numeric fields). The last post suggests you are modifying the original table.
In most cases the error you are getting is related to having a non numeric character in one of the records you are trying to move. Run a query to find out which record is the offending one by using the ISNUMERIC function. Something like this:
SELECT * FROM my_table
WHERE ISNUMERIC(my_field) = 0
---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font]
May 19, 2008 at 1:08 pm
With VERY LIMITED test data, this is what I did and it works:
CREATE TABLE [dbo].[Table_name] (
[Key_m] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,
[GeographicCode] [varchar] (10) COLLATE Latin1_General_CI_AS NULL ,
[Year_census_estimate] [int] NULL ,
[Sex] [int] NULL ,
[Age_start] [int] NULL ,
[Age_end] [int] NULL ,
[Age_range] [varchar] (10) COLLATE Latin1_General_CI_AS NULL ,
[Person_count] [decimal](18, 9) NULL
) ON [PRIMARY]
go
CREATE TABLE [dbo].[Just_imported_table] (
[Col002] varchar(10),
[Col003] varchar(25),
[Col004] varchar(25),
[Col005] varchar(25),
[Col006] varchar(25),
[Col007] varchar(10),
[Col008] varchar(25)
)
go
insert into [dbo].[Just_imported_table]
select 'ak', '1992', '1', '18', '25', 'Y Adult', '8.465549541'
go
select * from [dbo].[Just_imported_table];
go
INSERT INTO [dbo].[Table_name](
[GeographicCode] ,
[Year_census_estimate] ,
[Sex],
[Age_start] ,
[Age_end] ,
[Age_range] ,
[Person_count] )
SELECT
[Col002] ,
[Col003] ,
[Col004] ,
[Col005] ,
[Col006] ,
[Col007] ,
cast([Col008] as decimal(18,9))
--Cast([Col008] as decimal)
FROM
[dbo].[Just_imported_table]
go
select
*
from
[dbo].[Table_name]
go
drop table [dbo].[Table_name]
drop table [dbo].[Just_imported_table]
go
Start with this and go from there. Any problems, just post back.
😎
May 19, 2008 at 1:12 pm
Try this and see what comes out
SELECT * FROM [dbo].[Just_imported_table]
WHERE ISNUMERIC([Col003]) = 0
OR ISNUMERIC([Col004]) = 0
OR ISNUMERIC([Col005]) = 0
OR ISNUMERIC([Col006]) = 0
OR ISNUMERIC([Col008]) = 0
---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font]
May 20, 2008 at 3:10 am
That has helped me find the problem. Thanks.
May 20, 2008 at 7:07 pm
So, what was the problem?
--Jeff Moden
Change is inevitable... Change for the better is not.
May 20, 2008 at 8:15 pm
Jeff Moden (5/20/2008)
So, what was the problem?
With 99% probablility it was empty strings.
_____________
Code for TallyGenerator
May 21, 2008 at 2:39 am
There was some duff data in the table. Sorry about that.
May 21, 2008 at 7:19 pm
Not a problem... thanks for the feedback. 🙂
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply