Home » SQL & PL/SQL » SQL & PL/SQL » External tables (.csv) (Oracle 10g, Windows Server 2003)
External tables (.csv) [message #504568] |
Tue, 26 April 2011 11:18 |
Rodrignra
Messages: 63 Registered: April 2011
|
Member |
|
|
Hi everybody. I was wondering if someone could help me. I'm trying to load a csv file into an external table and when I select the table 0 rows is the result.
The log file has the following errors:
KUP-04021: field formatting error for field DEPTNO
KUP-04023: field start is after end of record
KUP-04101: record 1 rejected in file /usr/tmp\clie.csv
error processing column EMPNO in row 2 for datafile /usr/tmp\clie.csv
ORA-01722: invalid number
This is the script for the table:
create table emp_ext (
EMPNO NUMBER(4),
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPTNO NUMBER(2))
Organization external
(type oracle_loader
default directory testdir
access parameters (records delimited by newline
fields terminated by ',')
location ('emp_ext.csv'))
reject limit 1000;
And this is csv:
7369,SMITH,CLERK,7902,17-DEC-80,800,20
7499,ALLEN,SALESMAN,7698,20-FEB-81,1600,300,30
7521,WARD,SALESMAN,7698,22-FEB-81,1250,500,30
7566,JONES,MANAGER,7839,02-APR-81,2975,,20
7654,MARTIN,SALESMAN,7698,28-SEP-81,1250,1400,30
7698,BLAKE,MANAGER,7839,01-MAY-81,2850,,30
7782,CLARK,MANAGER,7839,09-JUN-81,2450,,10
7788,SCOTT,ANALYST,7566,19-APR-87,3000,,20
7839,KING,PRESIDENT,,17-NOV-81,5000,,10
7844,TURNER,SALESMAN,7698,08-SEP-81,1500,0,30
7876,ADAMS,CLERK,7788,23-MAY-87,1100,,20
Thanks for your help.
|
|
|
|
Re: External tables (.csv) [message #504572 is a reply to message #504571] |
Tue, 26 April 2011 11:39 |
John Watson
Messages: 8951 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
but now that I test it, it works for me:
SQL> select * from emp_ext;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
That was done with an exact copy/paste of your code and data file.
|
|
|
Re: External tables (.csv) [message #504574 is a reply to message #504572] |
Tue, 26 April 2011 11:49 |
Rodrignra
Messages: 63 Registered: April 2011
|
Member |
|
|
Thank you.
I'll show you the complete log file:
LOG file opened at 04/26/11 12:43:12
Field Definitions for table CLIENTE7
Record format DELIMITED BY NEWLINE
Data in file has same endianness as the platform
Rows with all null fields are accepted
Fields in Data Source:
EMPNO CHAR (255)
Terminated by ","
Trim whitespace same as SQL Loader
ENAME CHAR (255)
Terminated by ","
Trim whitespace same as SQL Loader
JOB CHAR (255)
Terminated by ","
Trim whitespace same as SQL Loader
MGR CHAR (255)
Terminated by ","
Trim whitespace same as SQL Loader
HIREDATE CHAR (255)
Terminated by ","
Trim whitespace same as SQL Loader
SAL CHAR (255)
Terminated by ","
Trim whitespace same as SQL Loader
COMM CHAR (255)
Terminated by ","
Trim whitespace same as SQL Loader
DEPTNO CHAR (255)
Terminated by ","
Trim whitespace same as SQL Loader
KUP-04021: field formatting error for field DEPTNO
KUP-04023: field start is after end of record
KUP-04101: record 1 rejected in file /usr/tmp\clie.csv
error processing column EMPNO in row 2 for datafile /usr/tmp\clie.csv
ORA-01722: invalid number
error processing column EMPNO in row 3 for datafile /usr/tmp\clie.csv
ORA-01722: invalid number
error processing column EMPNO in row 4 for datafile /usr/tmp\clie.csv
ORA-01722: invalid number
error processing column EMPNO in row 5 for datafile /usr/tmp\clie.csv
ORA-01722: invalid number
error processing column EMPNO in row 6 for datafile /usr/tmp\clie.csv
ORA-01722: invalid number
error processing column EMPNO in row 7 for datafile /usr/tmp\clie.csv
ORA-01722: invalid number
error processing column EMPNO in row 8 for datafile /usr/tmp\clie.csv
ORA-01722: invalid number
error processing column EMPNO in row 9 for datafile /usr/tmp\clie.csv
ORA-01722: invalid number
error processing column EMPNO in row 10 for datafile /usr/tmp\clie.csv
ORA-01722: invalid number
error processing column EMPNO in row 11 for datafile /usr/tmp\clie.csv
ORA-01722: invalid number
|
|
|
Re: External tables (.csv) [message #504576 is a reply to message #504574] |
Tue, 26 April 2011 12:01 |
John Watson
Messages: 8951 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
Please can you format your log file properly next time? It would be much easier to read. But it's the wrong logfile anyway, it refers to a table called CLENTE7 and a data file called clie.csv. This is my logfile:
[oracle@sbnf ~]$ cat EMP_EXT_14536.log
LOG file opened at 04/26/11 16:56:08
Field Definitions for table EMP_EXT
Record format DELIMITED BY NEWLINE
Data in file has same endianness as the platform
Rows with all null fields are accepted
Fields in Data Source:
EMPNO CHAR (255)
Terminated by ","
Trim whitespace same as SQL Loader
ENAME CHAR (255)
Terminated by ","
Trim whitespace same as SQL Loader
JOB CHAR (255)
Terminated by ","
Trim whitespace same as SQL Loader
MGR CHAR (255)
Terminated by ","
Trim whitespace same as SQL Loader
HIREDATE CHAR (255)
Terminated by ","
Trim whitespace same as SQL Loader
SAL CHAR (255)
Terminated by ","
Trim whitespace same as SQL Loader
COMM CHAR (255)
Terminated by ","
Trim whitespace same as SQL Loader
DEPTNO CHAR (255)
Terminated by ","
Trim whitespace same as SQL Loader
Date Cache Statistics for table EMP_EXT
Max Size: 1000
Entries : 11
Hits : 0
Misses : 0
|
|
|
Re: External tables (.csv) [message #504580 is a reply to message #504576] |
Tue, 26 April 2011 12:09 |
Rodrignra
Messages: 63 Registered: April 2011
|
Member |
|
|
Sorry I used a different names for the table and the .csv file. Those names are correct.
Anyway I created a new csv file with the name emp_ext and a new table, and this happened when I
create or replace directory succeeded.
create table succeeded.
Error starting at line 20 in command:
select *
from emp_ext
Error report:
SQL Error: ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-04063: unable to open log file EMP_EXT_3576_1028.log
OS error The system cannot find the file specified.
ORA-06512: at "SYS.ORACLE_LOADER", line 19
29913. 00000 - "error in executing %s callout"
*Cause: The execution of the specified callout caused an error.
*Action: Examine the error messages take appropriate action.
|
|
|
|
|
|
Re: External tables (.csv) [message #504590 is a reply to message #504572] |
Tue, 26 April 2011 12:28 |
joy_division
Messages: 4963 Registered: February 2005 Location: East Coast USA
|
Senior Member |
|
|
John Watson wrote on Tue, 26 April 2011 12:39but now that I test it, it works for me:
I ran it in SQL*Plus: Release 10.1.0.4.2, and got 10 of the records loaded with an exact cut and paste. The first record rejects with:
KUP-04021: field formatting error for field DEPTNO
KUP-04023: field start is after end of record
KUP-04101: record 1 rejected in file /vol02/opt/app/data/emp_ext.csv
but all the others loaded. Maybe you have control characters or and bad CR/LF combo in each record.
|
|
|
|
|
Re: External tables (.csv) [message #504620 is a reply to message #504592] |
Tue, 26 April 2011 15:55 |
John Watson
Messages: 8951 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
Sorry, but I can't understand what is going on. Your code worked for me, and it worked for Joy (with one problem - probably to do with copy/paste.) But your posts aren't consistent. The title says Windows Server 2003, and you mention the directory as being owned by Administrator (which sounds Windows-ish) but your log file refers to /usr/tmp (which sounds Unix-ish.) I think you need to start again, with a consistent and complete description of the problem.
|
|
|
|
Re: External tables (.csv) [message #504702 is a reply to message #504701] |
Wed, 27 April 2011 07:35 |
ThomasG
Messages: 3212 Registered: April 2005 Location: Heilbronn, Germany
|
Senior Member |
|
|
Rodrignra wrote on Wed, 27 April 2011 14:33I'm using Windows Server 2003 and I created the directory /usr/tmp
That is impossible. You can't have a /usr/tmp directory in Windows.
|
|
|
|
|
|
|
|
|
Re: External tables (.csv) [message #504713 is a reply to message #504710] |
Wed, 27 April 2011 08:06 |
joy_division
Messages: 4963 Registered: February 2005 Location: East Coast USA
|
Senior Member |
|
|
Rodrignra wrote on Wed, 27 April 2011 08:56The correct path could be 'C:\example'?
I'm not so sure. Your "faked" code with false filenames, etc. is only hurting you and making it longer for you to get a correct answer. Your csv file needs to reside on the server, not your local PC. It seems the oracle DIRECTORY that was created is defined as /usr/tmp but who knows since you already faked the filename.
I am guessing that you have a unix database server and a local windows PC. You do not put the file on your windows PC. You put it on the database server in /usr/tmp or whatever "testdir" is defined as.
|
|
|
|
Re: External tables (.csv) [message #504716 is a reply to message #504711] |
Wed, 27 April 2011 08:11 |
Rodrignra
Messages: 63 Registered: April 2011
|
Member |
|
|
I created the tables...
CREATE OR REPLACE DIRECTORY DAT_DIR AS 'C:\example';
CREATE TABLE SAMPLE_EXT
(
ID NUMBER,
BEGIN_DATE VARCHAR2(10),
FIRST_NAME VARCHAR2(30),
LAST_NAME VARCHAR2(30),
STATUS VARCHAR2(10)
)
ORGANIZATION EXTERNAL
(
TYPE ORACLE_LOADER
DEFAULT DIRECTORY DAT_DIR
ACCESS PARAMETERS
(
records delimited by newline LOAD WHEN (ID != ID)
badfile BAD_DIR:'SAMPLE_EXT%a_%p.bad'
logfile LOG_DIR:'SAMPLE_EXT%a_%p.log'
fields terminated by ',' optionally enclosed by '"' LRTRIM
MISSING FIELD VALUES ARE NULL
)
LOCATION ('sample.csv')
)
PARALLEL 4
REJECT LIMIT 1000;
CREATE TABLE SAMPLE_TAB
(
ID NUMBER,
BEGIN_DATE VARCHAR2(10),
FIRST_NAME VARCHAR2(30),
LAST_NAME VARCHAR2(30),
STATUS VARCHAR2(10)
);
...And then the function
CREATE OR REPLACE FUNCTION SAMPLE_ETL_FNC (P_FILE_PATH VARCHAR2 ) RETURN NUMBER AS
l_filename varchar2(100);
l_rowcount number;
l_sqlerrm varchar(2000);
l_filesize number;
BEGIN
DBMS_OUTPUT.PUT_LINE(to_char(sysdate, 'YYYY-MON-DD HH:MI:SS') || ' - BEGIN ETL PROCEDURE - Path: ' || p_file_path);
--Flexible to allow for file name or file path
if instr(p_file_path, '/') > 0 then
select substr(p_file_path, instr(p_file_path,'/', -1)+1,
length(p_file_path)-instr(p_file_path,'/', -1))
INTO l_filename
from dual;
else
l_filename := p_file_path;
end if;
--Get file size if required
--l_filesize := flength ('DAT_DIR',l_filename);
DBMS_OUTPUT.PUT_LINE('Filename: ' || l_filename);
BEGIN
--Alter the Log and Bad file names for debugging
--Can modify the bad and log file names if required
DBMS_OUTPUT.PUT_LINE('ALTER EXTERNAL TABLE DATA SOURCE');
execute immediate 'alter table SAMPLE_EXT location('''||l_filename||''')';
--Prepare Insert SQL
DBMS_OUTPUT.PUT_LINE('BEGIN INSERT FROM EXTERNAL TO DATA TABLE');
execute immediate 'Insert into sample_tab select * from sample_ext A';
l_rowcount := SQL%rowcount;
DBMS_OUTPUT.PUT_LINE(to_char(sysdate,'DD-MON-YYYY HH:MI:SS AM') || ': INSERT SUCCESSFUL... IN SERTED ' || l_rowcount || ' ROWS');
COMMIT;
RETURN 0;
exception
when others then
DBMS_OUTPUT.PUT_LINE('INSERT FAILED');
l_sqlerrm:=sqlerrm;
DBMS_OUTPUT.PUT_LINE(l_sqlerrm);
ROLLBACK;
commit;
RETURN 1;
END;
exception
when others then
DBMS_OUTPUT.PUT_LINE(SQLERRM);
RETURN 1;
END;
And finally:
select * from sample_tab
set serveroutput on
declare
l_return number;
begin
l_return := SAMPLE_ETL_FNC('july19.csv');
dbms_output.put_line('RETURN STATUS: ' || l_return);
end;
These are the scripts output:
CREATE OR REPLACE DIRECTORY succeeded.
CREATE TABLE succeeded.
CREATE TABLE succeeded.
FUNCTION SAMPLE_ETL_FNC Compiled.
ID BEGIN_DATE FIRST_NAME LAST_NAME STATUS
---------------------- ---------- ------------------------------ ------------------------------ ----------
0 rows selected
2011-APR-27 10:03:34 - BEGIN ETL PROCEDURE - Path: july19.csv
Filename: july19.csv
ALTER EXTERNAL TABLE DATA SOURCE
BEGIN INSERT FROM EXTERNAL TO DATA TABLE
INSERT FAILED
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-04080: directory object LOG_DIR not found
RETURN STATUS: 1
What am I doing wrong?
PS. I created the folder too in local disk c.
[Updated on: Wed, 27 April 2011 08:16] Report message to a moderator
|
|
|
|
|
Re: External tables (.csv) [message #504720 is a reply to message #504716] |
Wed, 27 April 2011 08:19 |
cookiemonster
Messages: 13943 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
KUP-04080: directory object LOG_DIR not found
You need to create an oracle directory called log_dir:
(
records delimited by newline LOAD WHEN (ID != ID)
badfile BAD_DIR:'SAMPLE_EXT%a_%p.bad'
logfile LOG_DIR:'SAMPLE_EXT%a_%p.log'
fields terminated by ',' optionally enclosed by '"' LRTRIM
MISSING FIELD VALUES ARE NULL
)
And obviously you'll need one called bad_dir as well. Vjain appears to have forgotten to mention that.
|
|
|
|
Re: External tables (.csv) [message #504723 is a reply to message #504721] |
Wed, 27 April 2011 08:26 |
Rodrignra
Messages: 63 Registered: April 2011
|
Member |
|
|
It worked! But, inserted 0 rows...
create or replace directory BAD_DIR as 'C:\example';
create or replace directory LOG_DIR AS 'C:\example';
declare
l_return number;
begin
l_return := SAMPLE_ETL_FNC('july19.csv');
dbms_output.put_line('RETURN STATUS: ' || l_return);
end;
create or replace directory succeeded.
create or replace directory succeeded.
2011-APR-27 10:15:49 - BEGIN ETL PROCEDURE - Path: july19.csv
Filename: july19.csv
ALTER EXTERNAL TABLE DATA SOURCE
BEGIN INSERT FROM EXTERNAL TO DATA TABLE
27-APR-2011 10:15:54 AM: INSERT SUCCESSFUL... IN SERTED 0 ROWS
RETURN STATUS: 0
|
|
|
Re: External tables (.csv) [message #504724 is a reply to message #504715] |
Wed, 27 April 2011 08:26 |
joy_division
Messages: 4963 Registered: February 2005 Location: East Coast USA
|
Senior Member |
|
|
cookiemonster wrote on Wed, 27 April 2011 09:10OP does specifically say windows server, but we'll see.
Yes, but for the database or the client?
I just find it too hard to try to decipher what is really happening unless like Michel says, we see the exact input and output in a full SQL*Plus session.
And faking output doesn't help either with what John pointed out with the table names.
|
|
|
Re: External tables (.csv) [message #504732 is a reply to message #504724] |
Wed, 27 April 2011 08:48 |
Rodrignra
Messages: 63 Registered: April 2011
|
Member |
|
|
These are the log and bad files. Apparently there's a problem with the column ID.
SAMPLE_EXT_3576_1028.log
LOG file opened at 04/27/11 10:15:49
Field Definitions for table SAMPLE_EXT
Record format DELIMITED BY NEWLINE
Data in file has same endianness as the platform
Rows with all null fields are accepted
Load when (ID != ID)
Fields in Data Source:
ID CHAR (255)
Terminated by ","
Enclosed by """ and """
Trim whitespace from left and right
BEGIN_DATE CHAR (255)
Terminated by ","
Enclosed by """ and """
Trim whitespace from left and right
FIRST_NAME CHAR (255)
Terminated by ","
Enclosed by """ and """
Trim whitespace from left and right
LAST_NAME CHAR (255)
Terminated by ","
Enclosed by """ and """
Trim whitespace from left and right
STATUS CHAR (255)
Terminated by ","
Enclosed by """ and """
Trim whitespace from left and right
LOG file opened at 04/27/11 10:16:11
Field Definitions for table SAMPLE_EXT
Record format DELIMITED BY NEWLINE
Data in file has same endianness as the platform
Rows with all null fields are accepted
Load when (ID != ID)
Fields in Data Source:
ID CHAR (255)
Terminated by ","
Enclosed by """ and """
Trim whitespace from left and right
BEGIN_DATE CHAR (255)
Terminated by ","
Enclosed by """ and """
Trim whitespace from left and right
FIRST_NAME CHAR (255)
Terminated by ","
Enclosed by """ and """
Trim whitespace from left and right
LAST_NAME CHAR (255)
Terminated by ","
Enclosed by """ and """
Trim whitespace from left and right
STATUS CHAR (255)
Terminated by ","
Enclosed by """ and """
Trim whitespace from left and right
SAMPLE_EXT000_3576_4548.log
LOG file opened at 04/27/11 10:15:53
Field Definitions for table SAMPLE_EXT
Record format DELIMITED BY NEWLINE
Data in file has same endianness as the platform
Rows with all null fields are accepted
Load when (ID != ID)
Fields in Data Source:
ID CHAR (255)
Terminated by ","
Enclosed by """ and """
Trim whitespace from left and right
BEGIN_DATE CHAR (255)
Terminated by ","
Enclosed by """ and """
Trim whitespace from left and right
FIRST_NAME CHAR (255)
Terminated by ","
Enclosed by """ and """
Trim whitespace from left and right
LAST_NAME CHAR (255)
Terminated by ","
Enclosed by """ and """
Trim whitespace from left and right
STATUS CHAR (255)
Terminated by ","
Enclosed by """ and """
Trim whitespace from left and right
error processing column ID in row 1 for datafile C:\example\july19.csv
ORA-01722: invalid number
error processing column ID in row 2 for datafile C:\example\july19.csv
ORA-01722: invalid number
error processing column ID in row 3 for datafile C:\example\july19.csv
ORA-01722: invalid number
error processing column ID in row 4 for datafile C:\example\july19.csv
ORA-01722: invalid number
error processing column ID in row 5 for datafile C:\example\july19.csv
ORA-01722: invalid number
error processing column ID in row 6 for datafile C:\example\july19.csv
ORA-01722: invalid number
LOG file opened at 04/27/11 10:16:11
Field Definitions for table SAMPLE_EXT
Record format DELIMITED BY NEWLINE
Data in file has same endianness as the platform
Rows with all null fields are accepted
Load when (ID != ID)
Fields in Data Source:
ID CHAR (255)
Terminated by ","
Enclosed by """ and """
Trim whitespace from left and right
BEGIN_DATE CHAR (255)
Terminated by ","
Enclosed by """ and """
Trim whitespace from left and right
FIRST_NAME CHAR (255)
Terminated by ","
Enclosed by """ and """
Trim whitespace from left and right
LAST_NAME CHAR (255)
Terminated by ","
Enclosed by """ and """
Trim whitespace from left and right
STATUS CHAR (255)
Terminated by ","
Enclosed by """ and """
Trim whitespace from left and right
error processing column ID in row 1 for datafile C:\example\july19.csv
ORA-01722: invalid number
error processing column ID in row 2 for datafile C:\example\july19.csv
ORA-01722: invalid number
error processing column ID in row 3 for datafile C:\example\july19.csv
ORA-01722: invalid number
error processing column ID in row 4 for datafile C:\example\july19.csv
ORA-01722: invalid number
error processing column ID in row 5 for datafile C:\example\july19.csv
ORA-01722: invalid number
error processing column ID in row 6 for datafile C:\example\july19.csv
ORA-01722: invalid number
SAMPLE_EXT000_3576_4584.bad
"ID, BEGIN_DATE, FIRST_NAME, LAST_NAME, STATUS"
"1, 19-JUL-07, John, Adams, Active"
"2, 19-JUL-07, Tyler, Howell, Active"
"3, 19-JUL-07, Jim, Lopez, Active"
"4, 19-JUL-07, Carlos, White, Inactive"
"5, 19-JUL-07, Scott, Tiger, Active"
|
|
|
Re: External tables (.csv) [message #504738 is a reply to message #504732] |
Wed, 27 April 2011 09:12 |
ThomasG
Messages: 3212 Registered: April 2005 Location: Heilbronn, Germany
|
Senior Member |
|
|
Are then (") signs in the *.csv file, too? That would be wrong. It should either be (for example the first like)
1, 19-JUL-07, John, Adams, Active
or
"1", "19-JUL-07", "John", "Adams", "Active"
|
|
|
Re: External tables (.csv) [message #504740 is a reply to message #504568] |
Wed, 27 April 2011 09:32 |
Rodrignra
Messages: 63 Registered: April 2011
|
Member |
|
|
When I opened the .csv in Excel it looked like this:
ID, BEGIN_DATE, FIRST_NAME, LAST_NAME, STATUS
1, 19-JUL-07, John, Adams, Active
2, 19-JUL-07, Tyler, Howell, Active
3, 19-JUL-07, Jim, Lopez, Active
4, 19-JUL-07, Carlos, White, Inactive
5, 19-JUL-07, Scott, Tiger, Active
If I open the .csv with the notepad:
"ID, BEGIN_DATE, FIRST_NAME, LAST_NAME, STATUS"
"1, 19-JUL-07, John, Adams, Active"
"2, 19-JUL-07, Tyler, Howell, Active"
"3, 19-JUL-07, Jim, Lopez, Active"
"4, 19-JUL-07, Carlos, White, Inactive"
"5, 19-JUL-07, Scott, Tiger, Active"
|
|
|
Re: External tables (.csv) [message #504741 is a reply to message #504740] |
Wed, 27 April 2011 09:35 |
ThomasG
Messages: 3212 Registered: April 2005 Location: Heilbronn, Germany
|
Senior Member |
|
|
Notepad is better than Excel when displaying how a file really looks. Excel seems to have saved the entire row as one column.
Remove the " in notepad and save the file again.
|
|
|
Re: External tables (.csv) [message #504743 is a reply to message #504740] |
Wed, 27 April 2011 09:37 |
joy_division
Messages: 4963 Registered: February 2005 Location: East Coast USA
|
Senior Member |
|
|
Rodrignra wrote on Wed, 27 April 2011 10:32When I opened the .csv in Excel it looked like this:
ID, BEGIN_DATE, FIRST_NAME, LAST_NAME, STATUS
1, 19-JUL-07, John, Adams, Active
2, 19-JUL-07, Tyler, Howell, Active
3, 19-JUL-07, Jim, Lopez, Active
4, 19-JUL-07, Carlos, White, Inactive
5, 19-JUL-07, Scott, Tiger, Active
If I open the .csv with the notepad:
"ID, BEGIN_DATE, FIRST_NAME, LAST_NAME, STATUS"
"1, 19-JUL-07, John, Adams, Active"
"2, 19-JUL-07, Tyler, Howell, Active"
"3, 19-JUL-07, Jim, Lopez, Active"
"4, 19-JUL-07, Carlos, White, Inactive"
"5, 19-JUL-07, Scott, Tiger, Active"
Jeez, why don't you explain more clearly and fully? Are all of those values in one cell? If yes, then that is why the entries string is surrounded by quotes as a csv file will put quotes around and value that has a comma in it.
You said "it looks like this in Excel" but excel is a spreadsheet, and you did not post a screen shot of a spreadsheet, you only posted a text representation of who knows what (eg. one cell, 2 cells, 3 cells, etc.)
|
|
|
|
|
Re: External tables (.csv) [message #504747 is a reply to message #504746] |
Wed, 27 April 2011 10:03 |
Rodrignra
Messages: 63 Registered: April 2011
|
Member |
|
|
cookiemonster wrote on Wed, 27 April 2011 11:52Presumably you have each line in one cell as already stated.
This was the problem, omg I'm so ashamed right now.
Thanks everybody for the helping hand!
|
|
|
|
|
Goto Forum:
Current Time: Sat Sep 28 16:55:50 CDT 2024
|