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
LVL 6
anumosesAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
If EVERY file will ONLY EVER have one header, why solve it in a BAT script?  Just issue a junk READ in the Oracle code.

You might think about adding a "number_of_header_rows" parameter to the load Oracle procedure to tell the code how many lines to skip.

something like (borrowing some of the Oracle code from your other question):

...
--skip header rows
for I in 1..number_of_header_rows loop
UTL_FILE.GET_LINE(F, V_LINE, 1000);
end loop;
--continue processing 'good' rows
...
anumosesAuthor Commented:
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

slightwv (䄆 Netminder) Commented:
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.
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

anumosesAuthor Commented:
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?
slightwv (䄆 Netminder) Commented:
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.
anumosesAuthor Commented:
It is always 1 header row. As per you comments, if 1 header row then the procedure should work correct?
slightwv (䄆 Netminder) Commented:
>>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.
anumosesAuthor Commented:
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.
slightwv (䄆 Netminder) Commented:
Post the actual code.
anumosesAuthor Commented:
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

slightwv (䄆 Netminder) Commented:
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

anumosesAuthor Commented:
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.
anumosesAuthor Commented:
select substr('W039715056673,156,218',1,instr('W039715056673,156,218',',',1,1)-1); from dual

Here is the error. Invalid number error.
slightwv (䄆 Netminder) Commented:
Check the input file and make sure there is only a single line before the CSV data.

Add more debugging to help.

...
		   for I in 1..p_number_of_header_rows loop
              UTL_FILE.GET_LINE(F, V_LINE, 1000);
dbms_output.put_line('Skipping:' || V_LINE || ':');

           end loop;
...

Open in new window

>>select substr('W039715056673,156,218',1,instr('W039715056673,156,218',',',1,1)-1); from dual

I get an invalid character error because there is a ';' in front of the from.

If I fix that I get: W039715056673

Now if you are trying to turn that into a number, it will fail...
anumosesAuthor Commented:
Please move it to oracle Zone. I will post the ftp section in powershell zone as a new question
anumosesAuthor Commented:
Yes it failed again as you mentioned
slightwv (䄆 Netminder) Commented:
>>Yes it failed again as you mentioned

What failed?
anumosesAuthor Commented:
Skipping:W039715056535,170,172
:
Skipping:W039715056704,168,197
:
Got: W039715056704,168,197
slightwv (䄆 Netminder) Commented:
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.
anumosesAuthor Commented:
Begin
LOAD_PURPLE_TOP_DATA(1);
End;

Skipping:W039715056673,156,218
:
Got: W039715056673,156,218
slightwv (䄆 Netminder) Commented:
>>Skipping:W039715056673,156,218

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

Sample ID No,HGB(g/L),PLT(10^3/uL)
slightwv (䄆 Netminder) Commented:
I see the other problem.

You commented out the:
         UTL_FILE.GET_LINE(F, V_LINE, 1000);

That was AFTER the loop.  You need that one!

After you skip the header, you need the next READ to get the good row.


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

         --GET THE FIRST ROW AFTER SKIPPING THE HEADER
         UTL_FILE.GET_LINE(F, V_LINE, 1000);
          v_location_i := 2000;
              IF V_LINE IS NULL THEN
            EXIT;
   ...
slightwv (䄆 Netminder) Commented:
>>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...
anumosesAuthor Commented:
Got: W039715056673,156,218

But still get error after fixing that uncommented  line.
slightwv (䄆 Netminder) Commented:
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.
anumosesAuthor Commented:
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));
anumosesAuthor Commented:
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
anumosesAuthor Commented:
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.
slightwv (䄆 Netminder) Commented:
>>V_PLT := to_number(substr(V_LINE, instr(V_LINE,',',1,2)+1));

Are you sure that is line 68?

Given the data you posted, that works for me.

>>I even tried this. But does not go to that message at all.

And it wouldn't.  The error happens in the lines above the output messages...

Change it to this and it should tell you where it is dying:
               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 in new window

anumosesAuthor Commented:
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

slightwv (䄆 Netminder) Commented:
>>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...
anumosesAuthor Commented:
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
slightwv (䄆 Netminder) Commented:
Change:
V_PLT := to_number(substr(V_LINE, instr(V_LINE,',',1,2)+1));

to:
V_PLT := to_number(trim(substr(V_LINE, instr(V_LINE,',',1,2)+1)));
anumosesAuthor Commented:
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

slightwv (䄆 Netminder) Commented:
There has to be something in the data that trim isn't stripping out.

Take a look at the value without TO_NUMBER.  Look at a DUMP of the data.

Just continue the debugging.  There is no magic wand here.

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

            -- put a colon before and after to see if there is 'something' else in there
            dbms_output.put_line('PLT STRING:' || trim(substr(V_LINE, instr(V_LINE,',',1,2)+1)) || ':');

            --just in case there is, DUMP it to see:
            --use v_email for grins since you already have it declared
            select dump(trim(substr(V_LINE, instr(V_LINE,',',1,2)+1))) into v_email from dual;
            dbms_output.put_line('PLT STRING DUMP:' || v_email || ':');

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
anumosesAuthor Commented:
Got: W039715056673,156,218

Got: W039715056673
Got: 156
PLT STRING:218
:
PLT STRING DUMP:Typ=1 Len=4: 50,49,56,13:
slightwv (䄆 Netminder) Commented:
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?
anumosesAuthor Commented:
Its carriage return or new line character.
anumosesAuthor Commented:
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
anumosesAuthor Commented:
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
slightwv (䄆 Netminder) Commented:
So, it works now?

I'm still concerned about the skipping of 'good' data but that is something you'll need to solve.
anumosesAuthor Commented:
Got data in the table. I am looking into the code for the skip. Thanks
anumosesAuthor Commented:
Thanks
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.