Error converting data type varchar to numeric
-
Hi i have issue in stored procedure converting data type Destination table dbo.Z_tbl_CirculationCopy_TEMP has numeric data type But source table Z_tbl_Import_temp have VarChar Type If I export data from source, I have error message Msg 8114, Level 16, State 5, Procedure Z_usp_IMPORT_EXCEL_UNPIVOT_1, Line 10 Error converting data type varchar to numeric. Pls help to resolve this issue Thanking you in advance Maideen Below is my store procedure
INSERT INTO dbo.Z_tbl_CirculationCopy_TEMP([MEMBER],[LANGUAGE],[TYPE],[PLATFORM],[CATEGORY],[MAINAREA],[STATE],[COPIES],
[MONTHNAME],[YEARNO],[REMARKS],[AVGISSUE_PAID],[AVGISSUE_FREE],[BCP_NTT],[BCP_50],[BCP_20],[BCP_20_Below],
[WP_TOTAL],[SABAH_TOTAL],[SARAWAK_TOTAL],[EM_Total])SELECT [MEMBER],[LANGUAGE],[TYPE],[PLATFORM],[CATEGORY],[MAINAREA],[STATE],[COPIES],
[MONTHNAME],[YEARNO],[REMARKS],[AVGISSUE_PAID],[AVGISSUE_FREE],[BCP_NTT],[BCP_50],[BCP_20],[BCP_20_Below],
[WP_TOTAL],[SABAH_TOTAL],[SARAWAK_TOTAL],[EM_Total]FROM (SELECT [MEMBER],[LANGUAGE],[TYPE],[PLATFORM],[CATEGORY],[MAINAREA],[MONTHNAME],[YEARNO],[REMARKS],
[AVGISSUE_PAID],[AVGISSUE_FREE],[BCP_NTT],[BCP_50],[BCP_20], [BCP_20_Below],
[WP_TOTAL],[SABAH_Total],[SARAWAK_Total],[EM_Total],
[Perlis],[Kedah],[Penang],[Perak],[Kelantan],[Terengganu],[Pahang],[Selangor],[WP],[NegriSembilan],[Melaka],[Johor],
[KotaKinaBalu],[sandakan],[Tawau],[Kudat],[Pendalaman],[Labuan],[SABAH_Other],
[Kuching],[Miri],[Sibu],[Bintulu],[SriAman],[KotaSemerahan],[Sarikei],[SARAWAK_Others],[Others]
FROM dbo.Z_tbl_Import_temp ) PUNPIVOT (Copies FOR State IN ([Perlis],[Kedah],[Penang],[Perak],[Kelantan],[Terengganu],[Pahang],[Selangor],
[WP],[NegriSembilan],[Melaka],[Johor],
[KotaKinaBalu],[Sandakan],[Tawau],[Kudat],[Pendalaman],[Labuan],[SABAH_Other],
[Kuching],[Miri],[Sibu],[Bintulu],[SriAman],[KotaSemerahan],[Sarikei],[SARAWAK_Others],[Others]
))AS unpvt -
Hi i have issue in stored procedure converting data type Destination table dbo.Z_tbl_CirculationCopy_TEMP has numeric data type But source table Z_tbl_Import_temp have VarChar Type If I export data from source, I have error message Msg 8114, Level 16, State 5, Procedure Z_usp_IMPORT_EXCEL_UNPIVOT_1, Line 10 Error converting data type varchar to numeric. Pls help to resolve this issue Thanking you in advance Maideen Below is my store procedure
INSERT INTO dbo.Z_tbl_CirculationCopy_TEMP([MEMBER],[LANGUAGE],[TYPE],[PLATFORM],[CATEGORY],[MAINAREA],[STATE],[COPIES],
[MONTHNAME],[YEARNO],[REMARKS],[AVGISSUE_PAID],[AVGISSUE_FREE],[BCP_NTT],[BCP_50],[BCP_20],[BCP_20_Below],
[WP_TOTAL],[SABAH_TOTAL],[SARAWAK_TOTAL],[EM_Total])SELECT [MEMBER],[LANGUAGE],[TYPE],[PLATFORM],[CATEGORY],[MAINAREA],[STATE],[COPIES],
[MONTHNAME],[YEARNO],[REMARKS],[AVGISSUE_PAID],[AVGISSUE_FREE],[BCP_NTT],[BCP_50],[BCP_20],[BCP_20_Below],
[WP_TOTAL],[SABAH_TOTAL],[SARAWAK_TOTAL],[EM_Total]FROM (SELECT [MEMBER],[LANGUAGE],[TYPE],[PLATFORM],[CATEGORY],[MAINAREA],[MONTHNAME],[YEARNO],[REMARKS],
[AVGISSUE_PAID],[AVGISSUE_FREE],[BCP_NTT],[BCP_50],[BCP_20], [BCP_20_Below],
[WP_TOTAL],[SABAH_Total],[SARAWAK_Total],[EM_Total],
[Perlis],[Kedah],[Penang],[Perak],[Kelantan],[Terengganu],[Pahang],[Selangor],[WP],[NegriSembilan],[Melaka],[Johor],
[KotaKinaBalu],[sandakan],[Tawau],[Kudat],[Pendalaman],[Labuan],[SABAH_Other],
[Kuching],[Miri],[Sibu],[Bintulu],[SriAman],[KotaSemerahan],[Sarikei],[SARAWAK_Others],[Others]
FROM dbo.Z_tbl_Import_temp ) PUNPIVOT (Copies FOR State IN ([Perlis],[Kedah],[Penang],[Perak],[Kelantan],[Terengganu],[Pahang],[Selangor],
[WP],[NegriSembilan],[Melaka],[Johor],
[KotaKinaBalu],[Sandakan],[Tawau],[Kudat],[Pendalaman],[Labuan],[SABAH_Other],
[Kuching],[Miri],[Sibu],[Bintulu],[SriAman],[KotaSemerahan],[Sarikei],[SARAWAK_Others],[Others]
))AS unpvtYour excel sheet is probably formatted with comma. You should split out your unpivot and inspect the results of the query to try and determine the invalid data, look for blanks, character and formatting (commans)
Never underestimate the power of human stupidity RAH