Hi,
I am writing a procdeure to use the utl_file.get_line to read csv files and then do some processing before inserting the data into a table.
The csv file only has four colums. The third column data has a line break so when reading the file i get a value error.
The procedure cannot detect the length of the 3rd field, it cannot detect the 3rd comma because this column has a line break.
To differenciate the length of the column between each "," part of the code I use is below.
BEGIN
fileID := UTL_FILE.FOPEN ('D:\TEST','CT.CSV', 'R',32765);
loop
UTL_FILE.GET_LINE (fileID,input_String);
delChar varchar2(1):=',';
--first field
v_Pos := instr(input_String,delChar,1,1);
v_lenString := v_Pos - 1;
v_compname := substr(input_String,1,v_lenString);
v_startPos := v_Pos + 1;
-- this will get the second field
v_Pos := instr(input_String,delChar,1,2);
v_lenString := v_Pos - v_startPos;
v_comptype := substr(input_String,v_startPos,v_lenString);
v_startPos := v_Pos + 1;
-- 3rd field
v_Pos := instr(input_String,delChar,1,3);
v_lenString := v_Pos - v_startPos;
v_notes := substr(input_String,v_startPos,v_lenString);
v_startPos := v_Pos + 1;
-- last field -- there is no delimiter for last field
v_Pos := length(input_String) + 1;
v_lenString := v_Pos - v_startPos;
v_comptypemodel := substr(input_String,v_StartPos,v_lenString);
My csv file looks like this when open with notepad ++,
AC CHILLER VSM 89 E,VSM 89 E,"EL.MOTOR AC-UNITS.
ABB AMA 500L2L BSMH 11000V/1720KW/105A/3584RPM S1 60HZ
COOLING WATER FLOW 12.3 m3/h
AMBIENT TEMP. 45 DEG",123421
ABB AMA 500L2L BSMH 11000,V/1720KW/105A/3584RPM S1 60HZ,EL.MOTOR AC-UNITS. ABB AMA ,EL.MOTOR AC-UNITS.
The value for v_pos=0 and v_lenSting=-30 for the 3rd field.
Hope someone can point me in the right direction on how to fix this.
Thanks in advance.