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

[arpita]

Hi,

I want to know the easiest way to convert a CIM file into pipe delimited (or comma delimited) file.
I have a script with me that does this but it adds un-necessary pipes between the values that makes it difficult to validate the data.

 

[vinod_home]

How do you plan to validate the data? Can you give an example on what you plan to do.

TIA

 

[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.

 

[Cecil Snodgrass]

Hi,

I want to know the easiest way to convert a CIM file into pipe delimited (or comma delimited) file.
I have a script with me that does this but it adds un-necessary pipes between the values that makes it difficult to validate the data.

The extra pipes are necessary if it's a CIM file, the extra |'s are tabs to place the data in the correct field's in the frame. Take your output from the script into your favorite text editor and replace "||" with "|" twice.

 

[arpita]

The extra pipes are necessary if it's a CIM file, the extra |'s are tabs to place the data in the correct field's in the frame. Take your output from the script into your favorite text editor and replace "||" with "|" twice.

yes, i understnad what u say, but i require the file in the format as attached.

 

[Cecil Snodgrass]

So you are saying you can not use a text editor and replace "||" with "|" until you have the desired format?

 

[vinod_home]

go ahead and attach the code that your using to create the output file, it could be a simple change within your logic to get your desired output.

 

[arpita]

Hi,
Please check the attached script convscript.txt that converts the CIM file into a pipe delimited file. (i have converted the extension of this script from .sh to .txt for attachment purpose).
Also i have attached the desired output that i want (desiredoutput.txt).
please run the script in the OS shell by the command:
convscript.sh source.txt target.txt