Sum or aggregate operation......
-
Hi All
I have the following:
I have a table that gets populated with a .csv file that get imported every day... the table columns are defined as varchar, because when I do the import it doesnt like any other datatype, eg. date column(datetime)
When I run my query I get the following error:
Error converting data type varchar to float....
When I changed it to varbinary, I got the following error:
Sum or aggregate function operation cannot take a varbinary data type as an argument...
The query is as follows:
Select sum(cast(F9 as varbinary(8)))
from Table
What Am I doing wrong??
Thanks
Anchelin
-
How are you importing the CSV file?
Almost always let SQL Server to decide how to convert data. You don't have to do it in most cases...
N 56°04'39.16"
E 12°55'05.25" -
I created the table, right click on table, all tasks, import, and followed the steps....
-
Please post some rows of source data, the CSV file.
Also provide DDL for target table.
N 56°04'39.16"
E 12°55'05.25" -
Hi
the rows:
CTSAPP01.engenoil.net Buffer %rcache 2-Feb-07 Buffer->%rcache 100 100 100 100 100 100 100 100 100 100 100 100 100 100 100 100 100 100 100 100 100 100 100 100 100 100 100 100 100 100 100 100 100 100 100 100 100 100 100 100 100 100 100 100 100 100 100 100 100 100 100 100 100 100 100 100 100 100 100 100 100 100 100 100 100 100 100 100 100 100 100 100 100 100 100 100 100 100 100 100 100 100 100 100 100 100 100 100 100 100 CTSAPP01.engenoil.net Buffer %wcache 2-Feb-07 Buffer->%wcache 38 38 56 45 38 39 40 38 56 48 38 37 37 37 54 51 37 37 37 37 52 54 37 38 39 42 52 54 36 39 42 41 50 55 38 43 37 43 49 58 42 39 43 44 47 57 40 43 40 42 48 59 45 42 36 38 45 62 37 38 37 38 45 63 42 37 37 38 44 65 39 38 37 38 43 66 37 38 36 38 42 67 37 38 37 39 40 68 38 38 CTSAPP01.engenoil.net Buffer bread/s 2-Feb-07 Buffer->bread/s 10 0 1 0 0 0 0 1 0 0 0 0 1 0 0 0 1 0 0 0 2 0 0 0 0 0 1 0 1 0 0 1 1 1 0 1 0 0 1 0 0 0 1 3 1 2 1 3 0 0 0 0 1 0 0 2 0 1 0 0 0 0 0 0 1 0 1 2 0 0 2 1 1 0 0 0 0 0 1 2 0 0 0 0 0 0 0 0 0 0 CTSAPP01.engenoil.net Buffer bwrit/s 2-Feb-07 Buffer->bwrit/s 74 71 75 73 73 85 76 62 74 70 70 67 67 71 73 74 75 69 65 72 77 76 68 66 78 111 73 120 75 80 105 89 83 117 81 109 70 118 80 102 98 81 114 122 79 128 91 121 88 117 108 111 143 121 75 76 77 93 76 79 77 76 78 85 105 70 76 76 78 85 80 74 72 73 75 83 71 74 76 77 75 85 76 65 68 71 71 84 72 68 CTSAPP01.engenoil.net Buffer lread/s 2-Feb-07 Buffer->lread/s 9042 9621 25790 13081 9524 9572 9866 10363 25318 15308 9564 9819 9709 9668 23692 17435 9550 9584 9981 9205 22439 19859 9596 9851 9742 9282 22117 21625 9545 9263 9491 9540 20644 23891 9278 9563 9451 9347 18929 26094 9059 9119 8998 8945 18064 28161 9126 8916 9101 8940 17088 30530 8901 8997 9091 9436 16569 32387 9266 9148 9097 8996 16231 34070 9325 9451 9559 9179 15026 36412 9282 9082 9671 9481 14082 38818 9740 9221 9365 9212 12832 41266 9342 9928 9913 9470 11808 43952 9609 9777 CTSAPP01.engenoil.net Buffer lwrit/s 2-Feb-07 Buffer->lwrit/s 119 114 171 133 117 140 127 100 166 134 112 107 107 114 160 151 119 111 104 115 163 163 108 106 128 193 153 260 117 131 182 151 165 262 132 190 111 208 157 242 167 133 199 219 149 300 151 212 146 202 208 272 258 210 119 122 141 243 121 127 123 122 143 231 180 112 121 123 140 240 130 119 114 118 130 247 113 119 120 124 128 257 121 106 108 116 118 264 117 110 CTSAPP01.engenoil.net Buffer pread/s 2-Feb-07 Buffer->pread/s 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 CTSAPP01.engenoil.net Buffer pwrit/s 2-Feb-07 Buffer->pwrit/s 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 DDL:
create table TAB(
col1 (varchar) 8000 collate Latin1_General_CI_AS Null,
col2(varchar) 8000 collate Latin1_General_CI_AS Null,
col3 (varchar) 8000 collate Latin1_General_CI_AS Null,
up to 256 cols
Thanks....
-
You are trying to import all those records into three columns?
N 56°04'39.16"
E 12°55'05.25" -
Hi
No... I created a table with 256 columns, the same as CSV file...
-
You will get errors if you create 256 column of width 8000.
For the last 200 columns or so, don't use VARCHAR. Use INT since all values are integer anyway.
And the first 4 column you create as VARCHAR(100).
Use a value that is the largest length possible for each one one of the first 4 columns individually.
N 56°04'39.16"
E 12°55'05.25" -
So... make my first five columns varchar and my other columns???
Also what length should I give my columns?
Thanks for your help so far!
Anchelin
-
I did exectly as you said and when I did the import gave me an error:
TransformCopy 'DirectCopyXform' conversion error: Conversion invalid for datatypes on column pair6 (source column 'Col006'(DBTYPE_STR) destination column 'F6' (DBTYPE_I4)
the first 4 columns varchar(255), rest of the columns int....
Thanks
Anchelin
-
Are there empty strings somewhere in those numeric columns?
Empty string cannot be converted to int. You must use NULLIF(COL_STR, '') to make it NULL.
Check data in the file. There must be non-numeric values somewhere.
_____________
Code for TallyGenerator -
What is the delimiter for the csv file (hard to tell from the post)
The data you posted is formatted as 6 char columns (although one maybe date) before the numbers, this is consistent with the error
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 12 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply