June 11, 2006 at 6:45 am
Hallo all ,
in first sorry for my English.
I have a problem. To insert a text into my sql server table.
The table had this look :
CREATE TABLE Minuetlich(
Datum text NOT NULL ,
Uhrzeit text NOT NULL ,
EWT_BO int default 0,
ASA_BO int default 0,
AS_BO int default 0,
AHT_BO int default 0,
APS_BO int default 0,
OAO_BO int default 0,
CO_BO int default 0,
CH_BO int default 0,
CA_BO int default 0,
CA_PR_BO int default 0,
CPH_BO int default 0,
CO_POA_BO int default 0,
LAST_PR_BO int default 0,
OLD_CALL_BO int default 0,
TTL_BSR_CO_BO int default 0,
TTL_BSR_CH_BO int default 0,
TTL_BSR_CA_BO int default 0,
TTL_BSR_CA_PR_BO int default 0,
TTL_BSR_NWP_BO int default 0,
TTL_BSR_WA_BO int default 0,
TTL_BSR_NWP_PR_BO int default 0,
TTL_BSR1_CO_BO int default 0,
TTL_BSR1_CH_BO int default 0,
TTL_BSR1_CA_BO int default 0,
TTL_BSR1_CA_PR_BO int default 0,
TTL_BSR1_NWP_BO int default 0,
TTL_BSR1_WA_BO int default 0,
TTL_BSR1_NWP_PR_BO int default 0,
TTL_BSR2_CO_BO int default 0,
TTL_BSR2_CH_BO int default 0,
TTL_BSR2_CA_BO int default 0,
TTL_BSR2_CA_PR_BO int default 0,
TTL_BSR2_NWP_BO int default 0,
TTL_BSR2_WA_BO int default 0,
TTL_BSR2_NWP_PR_BO int default 0,
EWT_KS int default 0,
ASA_KS int default 0,
AS_KS int default 0,
AHT_KS int default 0,
APS_KS int default 0,
OAO_KS int default 0,
CO_KS int default 0,
CH_KS int default 0,
CA_KS int default 0,
CA_PR_KS int default 0,
CPH_KS int default 0,
CO_POA_KS int default 0,
LAST_PR_KS int default 0,
OLD_CALL_KS int default 0,
TTL_BSR_CO_KS int default 0,
TTL_BSR_CH_KS int default 0,
TTL_BSR_CA_KS int default 0,
TTL_BSR_CA_PR_KS int default 0,
TTL_BSR_NWP_KS int default 0,
TTL_BSR_WA_KS int default 0,
TTL_BSR_NWP_PR_KS int default 0,
TTL_BSR1_CO_KS int default 0,
TTL_BSR1_CH_KS int default 0,
TTL_BSR1_CA_KS int default 0,
TTL_BSR1_CA_PR_KS int default 0,
TTL_BSR1_NWP_KS int default 0,
TTL_BSR1_WA_KS int default 0,
TTL_BSR1_NWP_PR_KS int default 0,
TTL_BSR2_CO_KS int default 0,
TTL_BSR2_CH_KS int default 0,
TTL_BSR2_CA_KS int default 0,
TTL_BSR2_CA_PR_KS int default 0,
TTL_BSR2_NWP_KS int default 0,
TTL_BSR2_WA_KS int default 0,
TTL_BSR2_NWP_PR_KS int default 0,
V51_CO int default 0,
V51_CH int default 0,
V51_CA_PR int default 0,
V52_CO int default 0,
V52_CH int default 0,
V52_CA_PR int default 0
)
Every Minute i became this text File on the Servers Folder:
6/11/2006
14:33:22
0;0;57;92;11030;10309.9;28;28;0;.0;111.5;4.9;25.1;0
28;28;0;.0;30;0;107.1
16;16;0;.0;30;0;187.5
12;12;0;.0;0;0;.0
0;0.148148149252;58;116;11503;9569.0;27;27;0;.0;82.3;5.1;32.8;0
27;27;0;.0;27;0
13;13;0;.0;27;0
14;14;0;.0;0;0
0;0;0
0;0;0
0;0;0
So one Problem is to insert it into the Table.
Another Problem is this ;.0 any numeric field with ;.0 must
look like ;0.0
I hope all understand my Problem and hope for Help.
Thanks and Regards
Thomas
June 12, 2006 at 2:32 am
Hello Thomas,
one question first: why are the date and time columns in your table TEXT? Date and time should be stored as DATETIME; I can understand that in an intermediate table it can be VARCHAR, but why text?
As to the import, you can do lots of things using DTS (which I don't know well enough to give advice) or you can simply import the file as it is, into a staging table with 2 columns - rownumber column and VARCHAR column which will hold the entire line of text from the file, no matter how long it is... and then use a parsing procedure, which will parse row by row and handle any possible problems (like .0), and finally insert data into your table.
June 12, 2006 at 2:53 am
Hallo thanks for answere,
you are right . This was one of my first test and i think its ok for test. The problem is that the array isnt in one line to work for the dts manager to input.
Greetings thomas
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply