Convert CIM file into a comma (or pipe) delimited file for validation of data

[arpita]

i will get a cim file which i will convert into a delimited (pipe/comma) file. i will then take this data into a temp table and validate the same.

[vinod_home]

Example cim file:
"FirstName" "LastName"
"Address Line1" - "City" "State" "Country"
"Phone1" - "Fax1"

Now how would you want this to end up, and how would you know what value is for which field, considering there are different cim files formats for different programs.

[arpita]

Example cim file:
"FirstName" "LastName"
"Address Line1" - "City" "State" "Country"
"Phone1" - "Fax1"

Now how would you want this to end up, and how would you know what value is for which field, considering there are different cim files formats for different programs.

Hi,
i will take a CIM file from a specific path. this CIM file will be in a fixed format as shown below. i will convert this cim file into delimited file, take it in a temp table and validate the data. all this i will do in a single program. as told earlier i have a script that does this but the output of this script is as attached, it inserts extra pipes.

@@batchload gltrmt.p
-
08/03/09
4228.87
1
"2103" "05" ""
- "1025" "Trans" "USD"
-171.52
2
"2104" "05" ""
- "1025" "Trans" "USD"
-2957.25
3
"2105" "05" ""
- "1025" "Trans" "USD"
-1100.10
4
"2453" "05" ""
- "1025" "Trans" "USD"
4228.87
@@end
@@batchload gltrmt.p
-
08/04/09
1075.78
1
"6012" "20" "603"
- "1025" "Trans" "USD"
163.46
2
"6010" "20" "603"
- "1025" "Trans" "USD"
608.38
3
"6007" "20" "603"
- "1025" "Trans" "USD"
50.97
4
"2430" "05" ""
- "1025" "Trans" "USD"
112.66
5
"2401" "05" ""
- "1025" "Trans" "USD"
140.31
6
"2373" "05" ""
- "1025" "Trans" "USD"
-19.23
7
"2372" "05" ""
- "1025" "Trans" "USD"
-50.00
8
"1013" "05" ""
- "1025" "Trans" "USD"
-705.30
9
"2103" "05" ""
- "1025" "Trans" "USD"
-70.37
10
"2104" "05" ""
- "1025" "Trans" "USD"
-74.22
11
"2105" "05" ""
- "1025" "Trans" "USD"
-9.61
12
"2109" "05" ""
- "1025" "Trans" "USD"
-10.12
13
"2355" "05" ""
- "1025" "Trans" "USD"
-126.00
14
"2357" "05" ""
- "1025" "Trans" "USD"
-10.93
@@end
@@batchload gltrmt.p
-
08/07/09
736843.34
1
"7076" "05" ""
- "1007" "Trans" "USD"
2322.46
2
"7076" "05" ""
- "1007" "Trans" "USD"
783.68
3
"7074" "05" ""
- "1007" "Trans" "USD"
111.75
4
"7074" "05" ""
- "1007" "Trans" "USD"
125.12
5
"7074" "05" ""
- "1007" "Trans" "USD"
82.48
6
"7074" "05" ""
- "1007" "Trans" "USD"
73.35
7
"7074" "05" ""
- "1007" "Trans" "USD"
104.69
8
"7074" "05" ""
- "1007" "Trans" "USD"
40.96
9
"7074" "05" ""
- "1007" "Trans" "USD"
23.43
10
"7074" "05" ""
- "1007" "Trans" "USD"
11.01
11
"6002" "90" "803"
- "1007" "Trans" "USD"
43400.76
12
"6002" "90" "840"
- "1007" "Trans" "USD"
7255.55
13
"7076" "05" ""
- "1007" "Trans" "USD"
1314.95
14
"7076" "05" ""
- "1007" "Trans" "USD"
432.52
15
"7076" "05" ""
- "1007" "Trans" "USD"
348.72
16
"7076" "05" ""
- "1007" "Trans" "USD"
1833.60
17
"7076" "05" ""
- "1007" "Trans" "USD"
1733.92
18
"7076" "05" ""
- "1007" "Trans" "USD"
1611.48
19
"7076" "05" ""
- "1007" "Trans" "USD"
2678.41
20
"6002" "90" "842"
- "1007" "Trans" "USD"
26954.23
21
"7074" "05" ""
- "1007" "Trans" "USD"
44.19
22
"7073" "05" ""
- "1007" "Trans" "USD"
76.78
23
"7073" "05" ""
- "1007" "Trans" "USD"
133.48
24
"7073" "05" ""
- "1007" "Trans" "USD"
156.99
25
"7073" "05" ""
- "1007" "Trans" "USD"
38.82
26
"7073" "05" ""
- "1007" "Trans" "USD"
88.95
27
"7073" "05" ""
- "1007" "Trans" "USD"
85.89
28
"7073" "05" ""
- "1007" "Trans" "USD"
15.88
29
"7073" "05" ""
- "1007" "Trans" "USD"
13.22
30
"7073" "05" ""
- "1007" "Trans" "USD"
72.30
31
"7073" "05" ""
- "1007" "Trans" "USD"
137.90
32
"7073" "05" ""
- "1007" "Trans" "USD"
124.07
33
"7074" "05" ""
- "1007" "Trans" "USD"
73.19
34
"7074" "05" ""
- "1007" "Trans" "USD"
99.98
35
"7074" "05" ""
- "1007" "Trans" "USD"
13.81
36
"7074" "05" ""
- "1007" "Trans" "USD"
58.65
37
"7074" "05" ""
- "1007" "Trans" "USD"
47.66
38
"7074" "05" ""
- "1007" "Trans" "USD"
71.10
39
"7074" "05" ""
- "1007" "Trans" "USD"
39.27
40
"7074" "05" ""
- "1007" "Trans" "USD"
16.81
41
"7074" "05" ""
- "1007" "Trans" "USD"
35.06
42
"6002" "90" "896"
- "1007" "Trans" "USD"
3070.11
43
"6002" "90" "848"
- "1007" "Trans" "USD"
5811.90
44
"6002" "90" "852"
- "1007" "Trans" "USD"
7208.65
45
"6002" "90" "849"
- "1007" "Trans" "USD"
27442.05
46
"7077" "05" ""
- "1007" "Trans" "USD"
225.00
47
"7077" "05" ""
- "1007" "Trans" "USD"
398.26
48
"7077" "05" ""
- "1007" "Trans" "USD"
1169.98
49
"7077" "05" ""
- "1007" "Trans" "USD"
805.74
50
"7077" "05" ""
- "1007" "Trans" "USD"
140.74
51
"2401" "05" ""
- "1007" "Trans" "USD"
-3606.01
52
"7077" "05" ""
- "1007" "Trans" "USD"
633.36
53
"7077" "05" ""
- "1007" "Trans" "USD"
798.48
54
"7077" "05" ""
- "1007" "Trans" "USD"
174.35
55
"7077" "05" ""
- "1007" "Trans" "USD"
317.71
56
"7077" "05" ""
- "1007" "Trans" "USD"
773.20
57
"7077" "05" ""
- "1007" "Trans" "USD"
330.65
58
"2393" "05" ""
- "1007" "Trans" "USD"
-360181.06
59
"7077" "05" ""
- "1007" "Trans" "USD"
337.30
60
"7077" "05" ""
- "1007" "Trans" "USD"
694.21
61
"2387" "05" ""
- "1007" "Trans" "USD"
-9629.33
62
"6002" "90" "930"
- "1007" "Trans" "USD"
36404.78
63
"6002" "90" "845"
- "1007" "Trans" "USD"
25566.09
64
"6002" "90" "853"
- "1007" "Trans" "USD"
15787.60
65
"6002" "90" "950"
- "1007" "Trans" "USD"
12251.02
66
"6002" "90" "301"
- "1007" "Trans" "USD"
43985.22
67
"6002" "90" "844"
- "1007" "Trans" "USD"
18252.47
68
"6002" "90" "868"
- "1007" "Trans" "USD"
15653.82
69
"6002" "90" "843"
- "1007" "Trans" "USD"
1296.00
70
"6002" "90" "900"
- "1007" "Trans" "USD"
22772.69
71
"6002" "90" "846"
- "1007" "Trans" "USD"
13991.25

[vinod_home]

Have you tried to read the data directly into the temp-table using the import statement.