Here's the deal, I have got a text file that contains information that needs to be inserted into a table embedded in a database with 500,000 records...
The text file is set up as follows:
"PK", "Date", "ID-Name", "Column_name1", "Data1", "Column_name2", "Data2"...
There are 150 column names and then the corresponding data.
To accomplish the task, I built a cursor...Appended the text file to that cursor, then alter the cursor to remove the column names....
New View:
PK Data1 Data2 Data3... in table format
Now I have to update the table in my database with the information in the cursor....
My original thought was to scan through the cursor record by record and update the table based on the primary key....
For some reason, I can't get it to do that.....
This is the program I wrote in ugly format.....
CLOSE ALL
CLEAR ALL
CREATE CURSOR cursor_org (f1 c(14),f2 c(14),f3 c(14), f4 C(25), f5 C(8), f6 C(25), f7 C(8), f8 C(25), f9 C(8), f10 C(25), f11 C(8),;
f12 C(25), f13 C(8), f14 C(25), f15 C(8), f16 C(25), f17 C(8), f18 C(25), f19 C(8), f20 C(25), f21 C(8), f22 C(25), f23 C(8), f24 C(25), f25 C(8),;
f26 C(25), f27 C(8), f28 C(25), f29 C(8), f30 C(25), f31 C(8), f32 C(25), f33 C(8), f34 C(25), f35 C(8), f36 C(25), f37 C(8), f38 C(25), f39 C(8),;
f40 C(25), f41 C(8), f42 C(25), f43 C(8), f44 C(25), f45 C(8), f46 C(25), f47 C(8), f48 C(25), f49 C(8), f50 C(25), f51 C(8), f52 C(25), f53 C(8),;
f54 C(25), f55 C(8), f56 C(25), f57 C(8), f58 C(25), f59 C(8), f60 C(25), f61 C(8), f62 C(25), f63 C(8), f64 C(25), f65 C(8), f66 C(25), f67 C(8),;
f68 C(25), f69 C(8), f70 C(25), f71 C(8), f72 C(25), f73 C(8), f74 C(25), f75 C(8), f76 C(25), f77 C(8), f78 C(25), f79 C(8), f80 C(25), f81 C(8),;
f82 C(25), f83 C(8), f84 C(25), f85 C(8), f86 C(25), f87 C(8), f88 C(25), f89 C(8), f90 C(25), f91 C(8), f92 C(25), f93 C(8), f94 C(25), f95 C(8),;
f96 C(25), f97 C(8), f98 C(25), f99 C(8), f100 C(25), f101 C(8), f102 C(25), f103 C(8), f104 C(25), f105 C(8), f106 C(25), f107 C(8), f108 C(25), f109 C(8),;
f110 C(25), f111 C(8), f112 C(25), f113 C(8), f114 C(25), f115 C(8), f116 C(25), f117 C(8), f118 C(25), f119 C(8), f120 C(25), f121 C(8), f122 C(25), f123 C(25),;
f124 C(8), f125 C(25))
APPEND FROM "D:\Projects\Health Lab\org.txt" DELIMITED
ALTER TABLE cursor_org DROP f2
ALTER TABLE cursor_org DROP F3
ALTER TABLE cursor_org DROP F4
ALTER TABLE cursor_org DROP F6
ALTER TABLE cursor_org DROP F8
ALTER TABLE cursor_org DROP F10
ALTER TABLE cursor_org DROP F12
ALTER TABLE cursor_org DROP F14
ALTER TABLE cursor_org DROP F16
ALTER TABLE cursor_org DROP F18
ALTER TABLE cursor_org DROP F20
ALTER TABLE cursor_org DROP F22
ALTER TABLE cursor_org DROP F24
ALTER TABLE cursor_org DROP F26
ALTER TABLE cursor_org DROP F28
ALTER TABLE cursor_org DROP F30
ALTER TABLE cursor_org DROP F32
ALTER TABLE cursor_org DROP F34
ALTER TABLE cursor_org DROP F36
ALTER TABLE cursor_org DROP F38
ALTER TABLE cursor_org DROP F40
ALTER TABLE cursor_org DROP F42
ALTER TABLE cursor_org DROP F44
ALTER TABLE cursor_org DROP F46
ALTER TABLE cursor_org DROP F48
ALTER TABLE cursor_org DROP F50
ALTER TABLE cursor_org DROP F52
ALTER TABLE cursor_org DROP F54
ALTER TABLE cursor_org DROP F56
ALTER TABLE cursor_org DROP F58
ALTER TABLE cursor_org DROP F60
ALTER TABLE cursor_org DROP F62
ALTER TABLE cursor_org DROP F64
ALTER TABLE cursor_org DROP F66
ALTER TABLE cursor_org DROP F68
ALTER TABLE cursor_org DROP F70
ALTER TABLE cursor_org DROP F72
ALTER TABLE cursor_org DROP F74
ALTER TABLE cursor_org DROP F76
ALTER TABLE cursor_org DROP F78
ALTER TABLE cursor_org DROP F80
ALTER TABLE cursor_org DROP F82
ALTER TABLE cursor_org DROP F84
ALTER TABLE cursor_org DROP F86
ALTER TABLE cursor_org DROP F88
ALTER TABLE cursor_org DROP F90
ALTER TABLE cursor_org DROP F92
ALTER TABLE cursor_org DROP F94
ALTER TABLE cursor_org DROP F96
ALTER TABLE cursor_org DROP F98
ALTER TABLE cursor_org DROP F100
ALTER TABLE cursor_org DROP F102
ALTER TABLE cursor_org DROP F104
ALTER TABLE cursor_org DROP F106
ALTER TABLE cursor_org DROP F108
ALTER TABLE cursor_org DROP F110
ALTER TABLE cursor_org DROP F112
ALTER TABLE cursor_org DROP F114
ALTER TABLE cursor_org DROP F116
ALTER TABLE cursor_org DROP F118
ALTER TABLE cursor_org DROP F120
ALTER TABLE cursor_org DROP F122
ALTER TABLE cursor_org DROP F124
OPEN "Database Path"...
SCAN
UPDATE wqorg
SET
dichloroethylene_1_1 = F5, trichlorothane_1_1_1 = F7, trichlorothane_1_1_2 = F9, dichloroethane_1_2 = F11, diochloropropane_1_2 = F13,;
trichlorobenzene_1_2_4 = F15, benzene = F17, carbon_tetrachloride = F19, dichloroethylene_cis_1_2 = F21, methylene_chloride,;
ethylbenzene = F23, chlorobenzene = F25, o_dichlorobenzene = F29, para_dichlorobenzene = F31, styrene = F33, tetrachloroethylene = F35,;
toluene = F37, dichloroethylene_trans_1_2 = F39, trichloroethylene = F41, vinyl_chloride = F43, total_xylenes = F45, bromobenzene = F47,;
bromochloromethane = F49, bromodichloromethane = F51, bromoform = F53, bromomethane = F55, chloroethane = F57, chloroform = F59, chloromethane = F61,;
chlorotoluene_2 = F63, chlorotoluene_4 = F65, dibromo_3_chloropropane_2 = F67, dibromochloromethane = F69, dibromoethane_2_1 = F71, dibromomethane = F73,;
dichlorobenzene_1_3 = F75, dichlorodifluoromethane = F77, dichloropropene_1_1 = F79, dichloropropene_trans_1_3 = F81, hexachlorobutadiene = F83, isopropyl_toluene = F85,;
isopropylbenzene = F87, n_butylbenzene = F89, n_propylbenzene = F91, naphthalene = F93, sec_butylbenzene = F95, tert_butylbenzene = F97, tetrachloroethane_1_1_1_2 = F99,;
tetrachloroethane_1_1_2_2 = F101, trichlorobenzene_1_2_3 = F103, trichlorofluoromethane = F105, trichloropropane_1_2_3 = F107, trimethylbenzene_4_2_1 = F109, trimethylbenzene_5_3_1 = F111,;
total_trihalomethanes = F113, mp_xylene = F115, o_xylene = F117, dichloroethane_1_1 = F119, dichloropropane_3_1 = F121, dichloropropene_cis_1_3 = F123, dichloropropane_2_2 = F125
WHERE sample_number = F1
END SCAN
If you have any suggestions, I'm at a loss....
Thanks....
The text file is set up as follows:
"PK", "Date", "ID-Name", "Column_name1", "Data1", "Column_name2", "Data2"...
There are 150 column names and then the corresponding data.
To accomplish the task, I built a cursor...Appended the text file to that cursor, then alter the cursor to remove the column names....
New View:
PK Data1 Data2 Data3... in table format
Now I have to update the table in my database with the information in the cursor....
My original thought was to scan through the cursor record by record and update the table based on the primary key....
For some reason, I can't get it to do that.....
This is the program I wrote in ugly format.....
CLOSE ALL
CLEAR ALL
CREATE CURSOR cursor_org (f1 c(14),f2 c(14),f3 c(14), f4 C(25), f5 C(8), f6 C(25), f7 C(8), f8 C(25), f9 C(8), f10 C(25), f11 C(8),;
f12 C(25), f13 C(8), f14 C(25), f15 C(8), f16 C(25), f17 C(8), f18 C(25), f19 C(8), f20 C(25), f21 C(8), f22 C(25), f23 C(8), f24 C(25), f25 C(8),;
f26 C(25), f27 C(8), f28 C(25), f29 C(8), f30 C(25), f31 C(8), f32 C(25), f33 C(8), f34 C(25), f35 C(8), f36 C(25), f37 C(8), f38 C(25), f39 C(8),;
f40 C(25), f41 C(8), f42 C(25), f43 C(8), f44 C(25), f45 C(8), f46 C(25), f47 C(8), f48 C(25), f49 C(8), f50 C(25), f51 C(8), f52 C(25), f53 C(8),;
f54 C(25), f55 C(8), f56 C(25), f57 C(8), f58 C(25), f59 C(8), f60 C(25), f61 C(8), f62 C(25), f63 C(8), f64 C(25), f65 C(8), f66 C(25), f67 C(8),;
f68 C(25), f69 C(8), f70 C(25), f71 C(8), f72 C(25), f73 C(8), f74 C(25), f75 C(8), f76 C(25), f77 C(8), f78 C(25), f79 C(8), f80 C(25), f81 C(8),;
f82 C(25), f83 C(8), f84 C(25), f85 C(8), f86 C(25), f87 C(8), f88 C(25), f89 C(8), f90 C(25), f91 C(8), f92 C(25), f93 C(8), f94 C(25), f95 C(8),;
f96 C(25), f97 C(8), f98 C(25), f99 C(8), f100 C(25), f101 C(8), f102 C(25), f103 C(8), f104 C(25), f105 C(8), f106 C(25), f107 C(8), f108 C(25), f109 C(8),;
f110 C(25), f111 C(8), f112 C(25), f113 C(8), f114 C(25), f115 C(8), f116 C(25), f117 C(8), f118 C(25), f119 C(8), f120 C(25), f121 C(8), f122 C(25), f123 C(25),;
f124 C(8), f125 C(25))
APPEND FROM "D:\Projects\Health Lab\org.txt" DELIMITED
ALTER TABLE cursor_org DROP f2
ALTER TABLE cursor_org DROP F3
ALTER TABLE cursor_org DROP F4
ALTER TABLE cursor_org DROP F6
ALTER TABLE cursor_org DROP F8
ALTER TABLE cursor_org DROP F10
ALTER TABLE cursor_org DROP F12
ALTER TABLE cursor_org DROP F14
ALTER TABLE cursor_org DROP F16
ALTER TABLE cursor_org DROP F18
ALTER TABLE cursor_org DROP F20
ALTER TABLE cursor_org DROP F22
ALTER TABLE cursor_org DROP F24
ALTER TABLE cursor_org DROP F26
ALTER TABLE cursor_org DROP F28
ALTER TABLE cursor_org DROP F30
ALTER TABLE cursor_org DROP F32
ALTER TABLE cursor_org DROP F34
ALTER TABLE cursor_org DROP F36
ALTER TABLE cursor_org DROP F38
ALTER TABLE cursor_org DROP F40
ALTER TABLE cursor_org DROP F42
ALTER TABLE cursor_org DROP F44
ALTER TABLE cursor_org DROP F46
ALTER TABLE cursor_org DROP F48
ALTER TABLE cursor_org DROP F50
ALTER TABLE cursor_org DROP F52
ALTER TABLE cursor_org DROP F54
ALTER TABLE cursor_org DROP F56
ALTER TABLE cursor_org DROP F58
ALTER TABLE cursor_org DROP F60
ALTER TABLE cursor_org DROP F62
ALTER TABLE cursor_org DROP F64
ALTER TABLE cursor_org DROP F66
ALTER TABLE cursor_org DROP F68
ALTER TABLE cursor_org DROP F70
ALTER TABLE cursor_org DROP F72
ALTER TABLE cursor_org DROP F74
ALTER TABLE cursor_org DROP F76
ALTER TABLE cursor_org DROP F78
ALTER TABLE cursor_org DROP F80
ALTER TABLE cursor_org DROP F82
ALTER TABLE cursor_org DROP F84
ALTER TABLE cursor_org DROP F86
ALTER TABLE cursor_org DROP F88
ALTER TABLE cursor_org DROP F90
ALTER TABLE cursor_org DROP F92
ALTER TABLE cursor_org DROP F94
ALTER TABLE cursor_org DROP F96
ALTER TABLE cursor_org DROP F98
ALTER TABLE cursor_org DROP F100
ALTER TABLE cursor_org DROP F102
ALTER TABLE cursor_org DROP F104
ALTER TABLE cursor_org DROP F106
ALTER TABLE cursor_org DROP F108
ALTER TABLE cursor_org DROP F110
ALTER TABLE cursor_org DROP F112
ALTER TABLE cursor_org DROP F114
ALTER TABLE cursor_org DROP F116
ALTER TABLE cursor_org DROP F118
ALTER TABLE cursor_org DROP F120
ALTER TABLE cursor_org DROP F122
ALTER TABLE cursor_org DROP F124
OPEN "Database Path"...
SCAN
UPDATE wqorg
SET
dichloroethylene_1_1 = F5, trichlorothane_1_1_1 = F7, trichlorothane_1_1_2 = F9, dichloroethane_1_2 = F11, diochloropropane_1_2 = F13,;
trichlorobenzene_1_2_4 = F15, benzene = F17, carbon_tetrachloride = F19, dichloroethylene_cis_1_2 = F21, methylene_chloride,;
ethylbenzene = F23, chlorobenzene = F25, o_dichlorobenzene = F29, para_dichlorobenzene = F31, styrene = F33, tetrachloroethylene = F35,;
toluene = F37, dichloroethylene_trans_1_2 = F39, trichloroethylene = F41, vinyl_chloride = F43, total_xylenes = F45, bromobenzene = F47,;
bromochloromethane = F49, bromodichloromethane = F51, bromoform = F53, bromomethane = F55, chloroethane = F57, chloroform = F59, chloromethane = F61,;
chlorotoluene_2 = F63, chlorotoluene_4 = F65, dibromo_3_chloropropane_2 = F67, dibromochloromethane = F69, dibromoethane_2_1 = F71, dibromomethane = F73,;
dichlorobenzene_1_3 = F75, dichlorodifluoromethane = F77, dichloropropene_1_1 = F79, dichloropropene_trans_1_3 = F81, hexachlorobutadiene = F83, isopropyl_toluene = F85,;
isopropylbenzene = F87, n_butylbenzene = F89, n_propylbenzene = F91, naphthalene = F93, sec_butylbenzene = F95, tert_butylbenzene = F97, tetrachloroethane_1_1_1_2 = F99,;
tetrachloroethane_1_1_2_2 = F101, trichlorobenzene_1_2_3 = F103, trichlorofluoromethane = F105, trichloropropane_1_2_3 = F107, trimethylbenzene_4_2_1 = F109, trimethylbenzene_5_3_1 = F111,;
total_trihalomethanes = F113, mp_xylene = F115, o_xylene = F117, dichloroethane_1_1 = F119, dichloropropane_3_1 = F121, dichloropropene_cis_1_3 = F123, dichloropropane_2_2 = F125
WHERE sample_number = F1
END SCAN
If you have any suggestions, I'm at a loss....
Thanks....