anumoses
asked on
Skip file headers
upload.bat
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
@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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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.
ASKER
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?
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
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.
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.
ASKER
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.
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.
ASKER
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.
ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at "HBC_DATA.LOAD_PURPLE_TOP_
ORA-06512: at line 2
This was the error I was getting earlier because of the header.
Post the actual code.
ASKER
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;
/
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;
/
ASKER
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.
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.
ASKER
select substr('W039715056673,156, 218',1,ins tr('W03971 5056673,15 6,218',',' ,1,1)-1); from dual
Here is the error. Invalid number error.
Here is the error. Invalid number error.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Please move it to oracle Zone. I will post the ftp section in powershell zone as a new question
ASKER
Yes it failed again as you mentioned
>>Yes it failed again as you mentioned
What failed?
What failed?
ASKER
Skipping:W039715056535,170 ,172
:
Skipping:W039715056704,168 ,197
:
Got: W039715056704,168,197
:
Skipping:W039715056704,168
:
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.
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.
ASKER
Begin
LOAD_PURPLE_TOP_DATA(1);
End;
Skipping:W039715056673,156 ,218
:
Got: W039715056673,156,218
LOAD_PURPLE_TOP_DATA(1);
End;
Skipping:W039715056673,156
:
Got: W039715056673,156,218
>>Skipping:W039715056673,1 56,218
Your file doesn't have one header row.
Your file doesn't have one header row.
ASKER
But when I look at the csv I only see one header row.
Sample ID No,HGB(g/L),PLT(10^3/uL)
Sample ID No,HGB(g/L),PLT(10^3/uL)
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
>>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...
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...
ASKER
Got: W039715056673,156,218
But still get error after fixing that uncommented line.
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.
Then copy that into the simple substr selects from dual from the other question.
That will tell you where the problem is.
ASKER
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));
ORA-06512: at "HBC_DATA.LOAD_PURPLE_TOP_
ORA-06512: at line 2
V_PLT := to_number(substr(V_LINE, instr(V_LINE,',',1,2)+1));
ASKER
select substr('W039715056673,156, 218',1,ins tr('W03971 5056673,15 6,218',',' ,1,1)-1) as one from dual
W039715056673
select to_number(substr('W0397150 56673,156, 218', instr('W039715056673,156,2 18',',',1, 1)+1,
instr('W039715056673,156,2 18',',',1, 2) - instr('W039715056673,156,2 18',',',1, 1)-1 )) as two from dual
156
select to_number(substr('W0397150 56673,156, 218', instr('W039715056673,156,2 18',',',1, 2)+1)) as three from dual
218
W039715056673
select to_number(substr('W0397150
instr('W039715056673,156,2
156
select to_number(substr('W0397150
218
ASKER
V_UNIT_ID := substr(V_LINE,1,instr(V_LI NE,',',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.
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:
dbms_output.put_line('Got:
dbms_output.put_line('Got:
I even tried this. But does not go to that message at all.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
ORA-06512: at "SYS.UTL_FILE", line 449
ORA-29283: invalid file operation
ORA-06512: at "HBC_DATA.LOAD_PURPLE_TOP_
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;
>>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...
That is the FOPEN in the code you posted. Check the file, directory, etc...
ASKER
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
ORA-06512: at "HBC_DATA.LOAD_PURPLE_TOP_
ORA-06512: at line 2
Got: W039715056673,156,218
Got: W039715056673
Got: 156
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at "HBC_DATA.LOAD_PURPLE_TOP_
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)));
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Got: W039715056673,156,218
Got: W039715056673
Got: 156
PLT STRING:218
:
PLT STRING DUMP:Typ=1 Len=4: 50,49,56,13:
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?
What does that tell you?
Why is the ':' on the next line?
What is character '13' in the dump?
ASKER
Its carriage return or new line character.
ASKER
select to_number(trim(substr(repl ace(replac e('W039715 056673,156 ,218',chr( 10),' '),chr(13),' '),
instr(replace(replace('W03 9715056673 ,156,218', chr(10),' '),chr(13),' '),',',1,2)+1))) as data1 from dual
instr(replace(replace('W03
ASKER
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
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.
I'm still concerned about the skipping of 'good' data but that is something you'll need to solve.
ASKER
Got data in the table. I am looking into the code for the skip. Thanks
ASKER
Thanks
ASKER
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "HBC_DATA.LOAD_PURPLE_TOP_
ORA-06512: at line 2
Open in new window