Link to home
Start Free TrialLog in
Avatar of anumoses
anumosesFlag for United States of America

asked on

Skip file headers

upload.bat

@echo off
setlocal enabledelayedexpansion
set FtpFile=%~dp0upload.ftp
set CsvFolder=F:\Purple_Top
set CsvFolder=C:\Temp
set logfile=upload.log

set CsvFile=
for /f "delims=" %%a in ('dir /o:d /b "%CsvFolder%\*.csv" ^| findstr.exe "^[0-9][0-9]_[0-9][0-9]_[0-9][0-9][0-9][0-9]_[0-9][0-9]_[0-9][0-9]\.csv$"') do (set CsvFile=%CsvFolder%\%%a)
if not defined CsvFile (
	echo No csv file found in '%CsvFolder%'.
	exit /b 1
)
echo Using '%CsvFile%' as input file.
for %%a in ("%CsvFile%") do (set UploadFile=%%~dpna-converted.csv)

echo Filtering unused columns, writing output to '%UploadFile%'.
powershell.exe -ExecutionPolicy RemoteSigned -Command "& {Import-Csv -Path '%CsvFile%' | Select-Object -Property 'Sample ID No', 'HGB(g/L)', 'PLT(10^3/uL)' | ConvertTo-Csv -NoTypeInformation | ForEach-Object {$_.Replace([char]34 + ',' + [char]34 , ',').Trim([char]34)} | Out-File -FilePath '%UploadFile%' -Encoding UTF8}"

call :ExportDataSection FTP "%FtpFile%"

echo Content of the ftp file:
type "%FtpFile%"
del "%logfile%"
ftp.exe -i -v -s:"%FtpFile%" >"%logfile%"



REM ================================================================================
REM Only functions after this line!
REM ================================================================================
goto :eof
:ExportDataSection
REM *** Reads all lines listed in %1 (section) and writes them to %2 (file name).
REM *** Environment variables will be expanded.
set Section=%~1
set FileName=%~2
if exist "%FileName%" del "%FileName%"
for /f "tokens=1 delims=[]" %%a in ('type "%~f0" ^| C:\Windows\system32\find.exe /n "[%Section%]"') do set DataStart=%%a
for /f "skip=%DataStart% delims=" %%a in ('type "%~f0"') do (
	call :Expand Line "%%a"
	if "!Line:~0,1!"=="[" (goto :eof) else (>>"%FileName%" echo !Line!)
)
goto :eof

:Expand
set %1=%~2
goto :eof

REM ================================================================================
REM Only data sections after this line!
REM ================================================================================
[FTP]
open heart1
hbc_data
hbc_data
cd /home/hbc_data/data/purple_top_data
mput %UploadFile%
bye

Open in new window


This code works. But few issues.

1. ftp csv has headers.

Sample ID No,HGB(g/L),PLT(10^3/uL)

Can we ftp file only with data? No headers. From the csv file I am using another programming language(oracle) to load the data into our database table. So headers were causing a problem. I edited the csv and removed the headers.

2.

1. 06_03_2015_14_49.csv  Modified date - 6/4/2015 9:13 AM

2. 06_02_2015_15_45.csv  Modified date - 6/3/2015 7:37 AM

As per the code if is based on modified date why am I not getting 6/3/2015 file after ftp to the server?
06-02-2015-15-45.csv
06-03-2015-14-49.csv
06-02-2015-15-45-converted.csv
SOLUTION
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of anumoses

ASKER

This is the modified code. I get error when I execute the procedure. So I know I am wrong.

ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "HBC_DATA.LOAD_PURPLE_TOP_DATA", line 48
ORA-06512: at line 2


CREATE OR REPLACE PROCEDURE LOAD_PURPLE_TOP_DATA IS
      F UTL_FILE.FILE_TYPE;
      V_LINE VARCHAR2 (1000);
      V_UNIT_ID VARCHAR2(25);
      V_HGB NUMBER;
      V_PLT NUMBER;
	  v_procedure_c constant varchar2(30) := 'load_purple_top_data';
	  v_location_i integer;	
v_donor_id varchar2(10);
v_gender   varchar2(1);
v_date_of_birth date;
v_first_name varchar2(25);
v_last_name varchar2(30);
v_middle_name varchar2(25);
v_blood_type varchar2(5);
v_ethnic_group varchar2(5);
v_street_name varchar2(50); 
v_suite_apt varchar2(10);
v_city   varchar2(40); 
v_state  varchar2(2); 
v_zip varchar2(5);
v_email varchar2(128);
v_d_phone_no varchar2(15); 
v_h_phone_no varchar2(15);
v_coll_date date;
number_of_header_rows VARCHAR2 (1000);
Cursor C1 is
select distinct d.donor_id,gender,date_of_birth,first_name,
       last_name,middle_name,blood_type,ethnic_group,
	   street_name, suite_apt,city, state, zip, da.email,
	   d_area_code||d_phone_no as d_phone_no, 
	   h_area_code||h_phone_no as h_phone_no,do.coll_date
  from donors_don d, donations_don do,
       donor_addresses_don da,
       valid_city_state_don vcs 
  where d.donor_id = do.donor_id
    and da.donor_id = d.donor_id
	and da.usps_id = vcs.usps_id
	and do.unit_id = v_unit_id;
	
BEGIN
v_location_i := 1000;
      F := UTL_FILE.FOPEN ('PURPLE_TOP_DIR', 'purple_top.csv', 'R');
    IF UTL_FILE.IS_OPEN(F) THEN
      LOOP
        BEGIN
		--skip header rows
           for I in 1..number_of_header_rows loop
              UTL_FILE.GET_LINE(F, V_LINE, 1000);
           end loop;
          --UTL_FILE.GET_LINE(F, V_LINE, 1000);
          IF V_LINE IS NULL THEN
            EXIT;
          END IF;
		  v_location_i := 2000;
		  V_UNIT_ID := substr(V_LINE,1,instr(V_LINE,',',1,1)-1);
		  V_HGB := to_number(substr(V_LINE, instr(V_LINE,',',1,1)+1, instr(V_LINE,',',1,2) - instr(V_LINE,',',1,1)-1 ));
		  V_PLT := to_number(substr(V_LINE, instr(V_LINE,',',1,2)+1));
        
		OPEN C1;
          LOOP
           FETCH C1 INTO v_donor_id,v_gender,v_date_of_birth,v_first_name,
                         v_last_name,v_middle_name,v_blood_type,v_ethnic_group,
	                     v_street_name, v_suite_apt,v_city, v_state, v_zip, v_email,
	   					 v_d_phone_no, v_h_phone_no,v_coll_date;
		  EXIT WHEN C1%NOTFOUND;
		    v_location_i := 3000;
			 INSERT INTO TEMP_PURPLE_TOP (DONOR_ID, 
			 			 				  GENDER, 
										  DATE_OF_BIRTH, 
										  FIRST_NAME, 
										  LAST_NAME, 
										  MIDDLE_NAME, 
										  BLOOD_TYPE, 
										  ETHNIC_GROUP, 
										  STREET_NAME, 
										  SUITE_APT, 
										  CITY, 
										  STATE, 
										  ZIP, 
										  EMAIL, 
										  D_PHONE_NO, 
										  H_PHONE_NO, 
										  UNIT_ID, 
										  HGB, 
										  PLT,
										  COLL_DATE)--HEIGHT_FT, HEIGHT_INCHES, WEIGHT 
			                     VALUES(v_donor_id,
								 		v_gender,
										v_date_of_birth ,
										v_first_name,
										v_last_name ,
										v_middle_name ,
										v_blood_type ,
										v_ethnic_group ,
										v_street_name ,
										v_suite_apt ,
										v_city  , 
										v_state  , 
										v_zip ,
										v_email ,
										v_d_phone_no , 
										v_h_phone_no,
										v_unit_id,
										v_hgb,
										v_plt,
										v_coll_date);
          COMMIT;
		  END LOOP;
		CLOSE C1;
		EXCEPTION
        WHEN NO_DATA_FOUND THEN
          EXIT;
        END;
      END LOOP;
    END IF;
    UTL_FILE.FCLOSE(F);
END;
/

Open in new window

Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Where do set number_of_header_rows?

Also, you declared it as a varchar2 and use it as a number.

I suggested you set it as a parameter to the procedure but how you do it is up to you.
CREATE OR REPLACE PROCEDURE LOAD_PURPLE_TOP_DATA (   p_number_of_header_rows IN NUMBER) IS

If I go this way...

BEGIN

v_location_i := 1000;
      F := UTL_FILE.FOPEN ('PURPLE_TOP_DIR', 'purple_top.csv', 'R');
    IF UTL_FILE.IS_OPEN(F) THEN
      LOOP
        BEGIN
            --skip header rows
          v_location_i := 1500;
               for I in 1..p_number_of_header_rows loop
              UTL_FILE.GET_LINE(F, V_LINE, 1000);
           end loop;

When I call the routine

Begin

LOAD_PURPLE_TOP_DATA(1);

End;

Will this ignore headers?
It should as long as there is 1 header row.

You'll need to make sure how the file is passed to you and that it is consistent from run to run or you may lose data.  for example:  next file you get doesn't have a header or has three header rows.
It is always 1 header row. As per you comments, if 1 header row then the procedure should work correct?
>>As per you comments, if 1 header row then the procedure should work correct?

I didn't test the code.  It 'looks' like it should work.

Set up a simple test case and try it.  You will either get the results you want or not.

If it always has 1 header row, then I probably wouldn't go with the parameter and just hard code the one read with no loop.
From my code header header was not ignored. Get the same error

ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at "HBC_DATA.LOAD_PURPLE_TOP_DATA", line 60
ORA-06512: at line 2

This was the error I was getting earlier because of the header.
Post the actual code.
CREATE OR REPLACE PROCEDURE LOAD_PURPLE_TOP_DATA (p_number_of_header_rows IN NUMBER) IS
      F UTL_FILE.FILE_TYPE;
      V_LINE VARCHAR2 (1000);
      V_UNIT_ID VARCHAR2(25);
      V_HGB NUMBER;
      V_PLT NUMBER;
	  v_procedure_c constant varchar2(30) := 'load_purple_top_data';
	  v_location_i integer;	
v_donor_id varchar2(10);
v_gender   varchar2(1);
v_date_of_birth date;
v_first_name varchar2(25);
v_last_name varchar2(30);
v_middle_name varchar2(25);
v_blood_type varchar2(5);
v_ethnic_group varchar2(5);
v_street_name varchar2(50); 
v_suite_apt varchar2(10);
v_city   varchar2(40); 
v_state  varchar2(2); 
v_zip varchar2(5);
v_email varchar2(128);
v_d_phone_no varchar2(15); 
v_h_phone_no varchar2(15);
v_coll_date date;
Cursor C1 is
select distinct d.donor_id,gender,date_of_birth,first_name,
       last_name,middle_name,blood_type,ethnic_group,
	   street_name, suite_apt,city, state, zip, da.email,
	   d_area_code||d_phone_no as d_phone_no, 
	   h_area_code||h_phone_no as h_phone_no,do.coll_date
  from donors_don d, donations_don do,
       donor_addresses_don da,
       valid_city_state_don vcs 
  where d.donor_id = do.donor_id
    and da.donor_id = d.donor_id
	and da.usps_id = vcs.usps_id
	and do.unit_id = v_unit_id;
	
BEGIN

v_location_i := 1000;
      F := UTL_FILE.FOPEN ('PURPLE_TOP_DIR', 'purple_top.csv', 'R');
    IF UTL_FILE.IS_OPEN(F) THEN
      LOOP
        BEGIN
		--skip header rows
          v_location_i := 1500;
		   for I in 1..p_number_of_header_rows loop
              UTL_FILE.GET_LINE(F, V_LINE, 1000);
           end loop;
          --UTL_FILE.GET_LINE(F, V_LINE, 1000);
          v_location_i := 2000;
		  IF V_LINE IS NULL THEN
            EXIT;
          END IF;
		  v_location_i := 3000;
		  V_UNIT_ID := substr(V_LINE,1,instr(V_LINE,',',1,1)-1);
		  V_HGB := to_number(substr(V_LINE, instr(V_LINE,',',1,1)+1, instr(V_LINE,',',1,2) - instr(V_LINE,',',1,1)-1 ));
		  V_PLT := to_number(substr(V_LINE, instr(V_LINE,',',1,2)+1));
        
		OPEN C1;
          LOOP
           FETCH C1 INTO v_donor_id,v_gender,v_date_of_birth,v_first_name,
                         v_last_name,v_middle_name,v_blood_type,v_ethnic_group,
	                     v_street_name, v_suite_apt,v_city, v_state, v_zip, v_email,
	   					 v_d_phone_no, v_h_phone_no,v_coll_date;
		  EXIT WHEN C1%NOTFOUND;
		    v_location_i := 4000;
			 INSERT INTO TEMP_PURPLE_TOP (DONOR_ID, 
			 			 				  GENDER, 
										  DATE_OF_BIRTH, 
										  FIRST_NAME, 
										  LAST_NAME, 
										  MIDDLE_NAME, 
										  BLOOD_TYPE, 
										  ETHNIC_GROUP, 
										  STREET_NAME, 
										  SUITE_APT, 
										  CITY, 
										  STATE, 
										  ZIP, 
										  EMAIL, 
										  D_PHONE_NO, 
										  H_PHONE_NO, 
										  UNIT_ID, 
										  HGB, 
										  PLT,
										  COLL_DATE)--HEIGHT_FT, HEIGHT_INCHES, WEIGHT 
			                     VALUES(v_donor_id,
								 		v_gender,
										v_date_of_birth ,
										v_first_name,
										v_last_name ,
										v_middle_name ,
										v_blood_type ,
										v_ethnic_group ,
										v_street_name ,
										v_suite_apt ,
										v_city  , 
										v_state  , 
										v_zip ,
										v_email ,
										v_d_phone_no , 
										v_h_phone_no,
										v_unit_id,
										v_hgb,
										v_plt,
										v_coll_date);
          COMMIT;
		  END LOOP;
		CLOSE C1;
		EXCEPTION
        WHEN NO_DATA_FOUND THEN
          EXIT;
        END;
      END LOOP;
    END IF;
    UTL_FILE.FCLOSE(F);
END;
/

Open in new window

Add the following dbms_output 'debug' statement above where you do the calculation and post what is displayed (line 58):

CREATE OR REPLACE PROCEDURE LOAD_PURPLE_TOP_DATA (p_number_of_header_rows IN NUMBER) IS
      F UTL_FILE.FILE_TYPE;
      V_LINE VARCHAR2 (1000);
      V_UNIT_ID VARCHAR2(25);
      V_HGB NUMBER;
      V_PLT NUMBER;
	  v_procedure_c constant varchar2(30) := 'load_purple_top_data';
	  v_location_i integer;	
v_donor_id varchar2(10);
v_gender   varchar2(1);
v_date_of_birth date;
v_first_name varchar2(25);
v_last_name varchar2(30);
v_middle_name varchar2(25);
v_blood_type varchar2(5);
v_ethnic_group varchar2(5);
v_street_name varchar2(50); 
v_suite_apt varchar2(10);
v_city   varchar2(40); 
v_state  varchar2(2); 
v_zip varchar2(5);
v_email varchar2(128);
v_d_phone_no varchar2(15); 
v_h_phone_no varchar2(15);
v_coll_date date;
Cursor C1 is
select distinct d.donor_id,gender,date_of_birth,first_name,
       last_name,middle_name,blood_type,ethnic_group,
	   street_name, suite_apt,city, state, zip, da.email,
	   d_area_code||d_phone_no as d_phone_no, 
	   h_area_code||h_phone_no as h_phone_no,do.coll_date
  from donors_don d, donations_don do,
       donor_addresses_don da,
       valid_city_state_don vcs 
  where d.donor_id = do.donor_id
    and da.donor_id = d.donor_id
	and da.usps_id = vcs.usps_id
	and do.unit_id = v_unit_id;
	
BEGIN

v_location_i := 1000;
      F := UTL_FILE.FOPEN ('PURPLE_TOP_DIR', 'purple_top.csv', 'R');
    IF UTL_FILE.IS_OPEN(F) THEN
      LOOP
        BEGIN
		--skip header rows
          v_location_i := 1500;
		   for I in 1..p_number_of_header_rows loop
              UTL_FILE.GET_LINE(F, V_LINE, 1000);
           end loop;
          --UTL_FILE.GET_LINE(F, V_LINE, 1000);
          v_location_i := 2000;
		  IF V_LINE IS NULL THEN
            EXIT;
          END IF;

dbms_output.put_line('Got: ' || V_LINE);
		  v_location_i := 3000;
		  V_UNIT_ID := substr(V_LINE,1,instr(V_LINE,',',1,1)-1);
		  V_HGB := to_number(substr(V_LINE, instr(V_LINE,',',1,1)+1, instr(V_LINE,',',1,2) - instr(V_LINE,',',1,1)-1 ));
		  V_PLT := to_number(substr(V_LINE, instr(V_LINE,',',1,2)+1));
        
		OPEN C1;
          LOOP
           FETCH C1 INTO v_donor_id,v_gender,v_date_of_birth,v_first_name,
                         v_last_name,v_middle_name,v_blood_type,v_ethnic_group,
	                     v_street_name, v_suite_apt,v_city, v_state, v_zip, v_email,
	   					 v_d_phone_no, v_h_phone_no,v_coll_date;
		  EXIT WHEN C1%NOTFOUND;
		    v_location_i := 4000;
			 INSERT INTO TEMP_PURPLE_TOP (DONOR_ID, 
			 			 				  GENDER, 
										  DATE_OF_BIRTH, 
										  FIRST_NAME, 
										  LAST_NAME, 
										  MIDDLE_NAME, 
										  BLOOD_TYPE, 
										  ETHNIC_GROUP, 
										  STREET_NAME, 
										  SUITE_APT, 
										  CITY, 
										  STATE, 
										  ZIP, 
										  EMAIL, 
										  D_PHONE_NO, 
										  H_PHONE_NO, 
										  UNIT_ID, 
										  HGB, 
										  PLT,
										  COLL_DATE)--HEIGHT_FT, HEIGHT_INCHES, WEIGHT 
			                     VALUES(v_donor_id,
								 		v_gender,
										v_date_of_birth ,
										v_first_name,
										v_last_name ,
										v_middle_name ,
										v_blood_type ,
										v_ethnic_group ,
										v_street_name ,
										v_suite_apt ,
										v_city  , 
										v_state  , 
										v_zip ,
										v_email ,
										v_d_phone_no , 
										v_h_phone_no,
										v_unit_id,
										v_hgb,
										v_plt,
										v_coll_date);
          COMMIT;
		  END LOOP;
		CLOSE C1;
		EXCEPTION
        WHEN NO_DATA_FOUND THEN
          EXIT;
        END;
      END LOOP;
    END IF;
    UTL_FILE.FCLOSE(F);
END;
/

Open in new window

Begin
LOAD_PURPLE_TOP_DATA(1);
End;


Got: Sample ID No,HGB(g/L),PLT(10^3/uL)

Begin
LOAD_PURPLE_TOP_DATA(2);
End;

Got: W039715056673,156,218

When I run the 2nd load I still get error.
select substr('W039715056673,156,218',1,instr('W039715056673,156,218',',',1,1)-1); from dual

Here is the error. Invalid number error.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Please move it to oracle Zone. I will post the ftp section in powershell zone as a new question
Yes it failed again as you mentioned
>>Yes it failed again as you mentioned

What failed?
Skipping:W039715056535,170,172
:
Skipping:W039715056704,168,197
:
Got: W039715056704,168,197
OK, then your file doesn't appear to be what you think it is.

It looks like you are skipping 'good' data.

If you call it with a parameter of 1, I don't see how you are getting multiple 'skipping' messages.
Begin
LOAD_PURPLE_TOP_DATA(1);
End;

Skipping:W039715056673,156,218
:
Got: W039715056673,156,218
>>Skipping:W039715056673,156,218

Your file doesn't have one header row.
But when I look at the csv I only see one header row.

Sample ID No,HGB(g/L),PLT(10^3/uL)
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
>>But when I look at the csv I only see one header row.

I don't know what to tell you.  The loop that displays the 'Skipping' message showed 'good' data and not a file header.  I can only go by what you post.

Make sure you are looking at the file you think you are looking at...
Got: W039715056673,156,218

But still get error after fixing that uncommented  line.
Look at the data displayed:  W039715056673,156,218

Then copy that into the simple substr selects from dual from the other question.

That will tell you where the problem is.
ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at "HBC_DATA.LOAD_PURPLE_TOP_DATA", line 68
ORA-06512: at line 2

 V_PLT := to_number(substr(V_LINE, instr(V_LINE,',',1,2)+1));
select  substr('W039715056673,156,218',1,instr('W039715056673,156,218',',',1,1)-1) as one from dual

W039715056673
             
select to_number(substr('W039715056673,156,218', instr('W039715056673,156,218',',',1,1)+1,
instr('W039715056673,156,218',',',1,2) - instr('W039715056673,156,218',',',1,1)-1 )) as two from dual

156
             
select to_number(substr('W039715056673,156,218', instr('W039715056673,156,218',',',1,2)+1)) as three from dual

218
V_UNIT_ID := substr(V_LINE,1,instr(V_LINE,',',1,1)-1);      
             
              V_HGB := to_number(substr(V_LINE, instr(V_LINE,',',1,1)+1, instr(V_LINE,',',1,2) - instr(V_LINE,',',1,1)-1 ));
             
              V_PLT := to_number(substr(V_LINE, instr(V_LINE,',',1,2)+1));
              dbms_output.put_line('Got: ' || V_UNIT_ID);
              dbms_output.put_line('Got: ' || V_HGB);
              dbms_output.put_line('Got: ' || V_PLT);

I even tried this. But does not go to that message at all.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ORA-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE", line 449
ORA-29283: invalid file operation
ORA-06512: at "HBC_DATA.LOAD_PURPLE_TOP_DATA", line 43
ORA-06512: at line 2



CREATE OR REPLACE PROCEDURE LOAD_PURPLE_TOP_DATA (p_number_of_header_rows IN NUMBER) IS
      F UTL_FILE.FILE_TYPE;
      V_LINE VARCHAR2 (1000);
      V_UNIT_ID VARCHAR2(25);
      V_HGB NUMBER;
      V_PLT NUMBER;
	  v_procedure_c constant varchar2(30) := 'load_purple_top_data';
	  v_location_i integer;	
v_donor_id varchar2(10);
v_gender   varchar2(1);
v_date_of_birth date;
v_first_name varchar2(25);
v_last_name varchar2(30);
v_middle_name varchar2(25);
v_blood_type varchar2(5);
v_ethnic_group varchar2(5);
v_street_name varchar2(50); 
v_suite_apt varchar2(10);
v_city   varchar2(40); 
v_state  varchar2(2); 
v_zip varchar2(5);
v_email varchar2(128);
v_d_phone_no varchar2(15); 
v_h_phone_no varchar2(15);
v_coll_date date;
Cursor C1 is
select distinct d.donor_id,gender,date_of_birth,first_name,
       last_name,middle_name,blood_type,ethnic_group,
	   street_name, suite_apt,city, state, zip, da.email,
	   d_area_code||d_phone_no as d_phone_no, 
	   h_area_code||h_phone_no as h_phone_no,do.coll_date
  from donors_don d, donations_don do,
       donor_addresses_don da,
       valid_city_state_don vcs 
  where d.donor_id = do.donor_id
    and da.donor_id = d.donor_id
	and da.usps_id = vcs.usps_id
	and do.unit_id = v_unit_id;
	
BEGIN

v_location_i := 1000;
      F := UTL_FILE.FOPEN ('PURPLE_TOP_DIR', 'purple_top.csv', 'R');
    IF UTL_FILE.IS_OPEN(F) THEN
      LOOP
        BEGIN
		 --skip header rows
          v_location_i := 1500;
               for I in 1..p_number_of_header_rows loop
              UTL_FILE.GET_LINE(F, V_LINE, 1000);
           end loop;
		   
		   
          UTL_FILE.GET_LINE(F, V_LINE, 1000);
         
		  v_location_i := 2000;
		  IF V_LINE IS NULL THEN
            EXIT;
          END IF;

           dbms_output.put_line('Got: ' || V_LINE);
		  
		 
		    V_UNIT_ID := substr(V_LINE,1,instr(V_LINE,',',1,1)-1);      
               dbms_output.put_line('Got: ' || V_UNIT_ID);
               
               V_HGB := to_number(substr(V_LINE, instr(V_LINE,',',1,1)+1, instr(V_LINE,',',1,2) - instr(V_LINE,',',1,1)-1 ));
               dbms_output.put_line('Got: ' || V_HGB);
              
               V_PLT := to_number(substr(V_LINE, instr(V_LINE,',',1,2)+1));
               dbms_output.put_line('Got: ' || V_PLT);
      
		OPEN C1;
		 
          LOOP
		 
           FETCH C1 INTO v_donor_id,v_gender,v_date_of_birth,v_first_name,
                         v_last_name,v_middle_name,v_blood_type,v_ethnic_group,
	                     v_street_name, v_suite_apt,v_city, v_state, v_zip, v_email,
	   					 v_d_phone_no, v_h_phone_no,v_coll_date;
		  EXIT WHEN C1%NOTFOUND;
		    v_location_i := 4000;
			 INSERT INTO TEMP_PURPLE_TOP (DONOR_ID, 
			 			 				  GENDER, 
										  DATE_OF_BIRTH, 
										  FIRST_NAME, 
										  LAST_NAME, 
										  MIDDLE_NAME, 
										  BLOOD_TYPE, 
										  ETHNIC_GROUP, 
										  STREET_NAME, 
										  SUITE_APT, 
										  CITY, 
										  STATE, 
										  ZIP, 
										  EMAIL, 
										  D_PHONE_NO, 
										  H_PHONE_NO, 
										  UNIT_ID, 
										  HGB, 
										  PLT,
										  COLL_DATE)--HEIGHT_FT, HEIGHT_INCHES, WEIGHT 
			                     VALUES(v_donor_id,
								 		v_gender,
										v_date_of_birth ,
										v_first_name,
										v_last_name ,
										v_middle_name ,
										v_blood_type ,
										v_ethnic_group ,
										v_street_name ,
										v_suite_apt ,
										v_city  , 
										v_state  , 
										v_zip ,
										v_email ,
										v_d_phone_no , 
										v_h_phone_no,
										v_unit_id,
										v_hgb,
										v_plt,
										v_coll_date);
          COMMIT;
		  END LOOP;
		CLOSE C1;
		EXCEPTION
        WHEN NO_DATA_FOUND THEN
          EXIT;
        END;
      END LOOP;
    END IF;
    UTL_FILE.FCLOSE(F);
END;

Open in new window

>>ORA-06512: at "HBC_DATA.LOAD_PURPLE_TOP_DATA", line 43

That is the FOPEN in the code you posted.  Check the file, directory, etc...
ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at "HBC_DATA.LOAD_PURPLE_TOP_DATA", line 70
ORA-06512: at line 2



Got: W039715056673,156,218

Got: W039715056673
Got: 156
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
After I changed the code

ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at "HBC_DATA.LOAD_PURPLE_TOP_DATA", line 71
ORA-06512: at line 2



Got: W039715056673,156,218

Got: W039715056673
Got: 156

CREATE OR REPLACE PROCEDURE LOAD_PURPLE_TOP_DATA (p_number_of_header_rows IN NUMBER) IS
      F UTL_FILE.FILE_TYPE;
      V_LINE VARCHAR2 (1000);
      V_UNIT_ID VARCHAR2(25);
      V_HGB NUMBER;
      V_PLT NUMBER;
	  v_procedure_c constant varchar2(30) := 'load_purple_top_data';
	  v_location_i integer;	
v_donor_id varchar2(10);
v_gender   varchar2(1);
v_date_of_birth date;
v_first_name varchar2(25);
v_last_name varchar2(30);
v_middle_name varchar2(25);
v_blood_type varchar2(5);
v_ethnic_group varchar2(5);
v_street_name varchar2(50); 
v_suite_apt varchar2(10);
v_city   varchar2(40); 
v_state  varchar2(2); 
v_zip varchar2(5);
v_email varchar2(128);
v_d_phone_no varchar2(15); 
v_h_phone_no varchar2(15);
v_coll_date date;
Cursor C1 is
select distinct d.donor_id,gender,date_of_birth,first_name,
       last_name,middle_name,blood_type,ethnic_group,
	   street_name, suite_apt,city, state, zip, da.email,
	   d_area_code||d_phone_no as d_phone_no, 
	   h_area_code||h_phone_no as h_phone_no,do.coll_date
  from donors_don d, donations_don do,
       donor_addresses_don da,
       valid_city_state_don vcs 
  where d.donor_id = do.donor_id
    and da.donor_id = d.donor_id
	and da.usps_id = vcs.usps_id
	and do.unit_id = v_unit_id;
	
BEGIN

v_location_i := 1000;
      F := UTL_FILE.FOPEN ('PURPLE_TOP_DIR', 'purple_top.csv', 'R');
    IF UTL_FILE.IS_OPEN(F) THEN
      LOOP
        BEGIN
		 --skip header rows
          v_location_i := 1500;
               for I in 1..p_number_of_header_rows loop
              UTL_FILE.GET_LINE(F, V_LINE, 1000);
           end loop;
		   
		   
          UTL_FILE.GET_LINE(F, V_LINE, 1000);
         
		  v_location_i := 2000;
		  IF V_LINE IS NULL THEN
            EXIT;
          END IF;

           dbms_output.put_line('Got: ' || V_LINE);
		  
		 
		    V_UNIT_ID := substr(V_LINE,1,instr(V_LINE,',',1,1)-1);      
               dbms_output.put_line('Got: ' || V_UNIT_ID);
               
               V_HGB := to_number(substr(V_LINE, instr(V_LINE,',',1,1)+1, instr(V_LINE,',',1,2) - instr(V_LINE,',',1,1)-1 ));
               dbms_output.put_line('Got: ' || V_HGB);
              
               --V_PLT := to_number(substr(V_LINE, instr(V_LINE,',',1,2)+1));
               V_PLT := to_number(trim(substr(V_LINE, instr(V_LINE,',',1,2)+1))); 
			   dbms_output.put_line('Got: ' || V_PLT);
      
		OPEN C1;
		 
          LOOP
		 
           FETCH C1 INTO v_donor_id,v_gender,v_date_of_birth,v_first_name,
                         v_last_name,v_middle_name,v_blood_type,v_ethnic_group,
	                     v_street_name, v_suite_apt,v_city, v_state, v_zip, v_email,
	   					 v_d_phone_no, v_h_phone_no,v_coll_date;
		  EXIT WHEN C1%NOTFOUND;
		    v_location_i := 4000;
			 INSERT INTO TEMP_PURPLE_TOP (DONOR_ID, 
			 			 				  GENDER, 
										  DATE_OF_BIRTH, 
										  FIRST_NAME, 
										  LAST_NAME, 
										  MIDDLE_NAME, 
										  BLOOD_TYPE, 
										  ETHNIC_GROUP, 
										  STREET_NAME, 
										  SUITE_APT, 
										  CITY, 
										  STATE, 
										  ZIP, 
										  EMAIL, 
										  D_PHONE_NO, 
										  H_PHONE_NO, 
										  UNIT_ID, 
										  HGB, 
										  PLT,
										  COLL_DATE)--HEIGHT_FT, HEIGHT_INCHES, WEIGHT 
			                     VALUES(v_donor_id,
								 		v_gender,
										v_date_of_birth ,
										v_first_name,
										v_last_name ,
										v_middle_name ,
										v_blood_type ,
										v_ethnic_group ,
										v_street_name ,
										v_suite_apt ,
										v_city  , 
										v_state  , 
										v_zip ,
										v_email ,
										v_d_phone_no , 
										v_h_phone_no,
										v_unit_id,
										v_hgb,
										v_plt,
										v_coll_date);
          COMMIT;
		  END LOOP;
		CLOSE C1;
		EXCEPTION
        WHEN NO_DATA_FOUND THEN
          EXIT;
        END;
      END LOOP;
    END IF;
    UTL_FILE.FCLOSE(F);
END;
/
Line 71 is 

V_PLT := to_number(trim(substr(V_LINE, instr(V_LINE,',',1,2)+1))); 

Open in new window

ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Got: W039715056673,156,218

Got: W039715056673
Got: 156
PLT STRING:218
:
PLT STRING DUMP:Typ=1 Len=4: 50,49,56,13:
Instead of me telling you, why not try this one solo?

What does that tell you?
Why is the ':' on the next line?
What is character '13' in the dump?
Its carriage return or new line character.
select to_number(trim(substr(replace(replace('W039715056673,156,218',chr(10),' '),chr(13),' '),
instr(replace(replace('W039715056673,156,218',chr(10),' '),chr(13),' '),',',1,2)+1))) as data1 from dual
Got: W039715056673,156,218

Got: W039715056673
Got: 156
PLT STRING:218
:
PLT STRING DUMP:Typ=1 Len=4: 50,49,56,13:
Got: 218
Got: W039715056704,168,197

Got: W039715056704
Got: 168
PLT STRING:197
:
PLT STRING DUMP:Typ=1 Len=4: 49,57,55,13:
Got: 197
Got: W039715060904,150,248

Got: W039715060904
Got: 150
PLT STRING:248
:
PLT STRING DUMP:Typ=1 Len=4: 50,52,56,13:
Got: 248
Got: W039715056566,158,309

Got: W039715056566
Got: 158
PLT STRING:309
:
PLT STRING DUMP:Typ=1 Len=4: 51,48,57,13:
Got: 309
Got: W039715060996,126,195

Got: W039715060996
Got: 126
PLT STRING:195
:
PLT STRING DUMP:Typ=1 Len=4: 49,57,53,13:
Got: 195
So, it works now?

I'm still concerned about the skipping of 'good' data but that is something you'll need to solve.
Got data in the table. I am looking into the code for the skip. Thanks
Thanks