Data not showing in the oracle Sql Database importing from .CSV

Question

I scraped a website and fetch the data into the .CSV file. This process has been successfully executed and working properly. But, when I tried to fetch the data in the table of Oracle Sql Database it only brings the first row of the .CSV file and don't retrieve the other lines of the data. I am trying to retrieve the data of CSV file through SQLLoader written in create script. When I call any other CSV through that script it is working fine and brings all the data. But, this matter only comes in the file which has been created through the java program.
Java code is as follows.

public static void parsingHTML() throws Exception {
  for (int i = 1; i <= 1; i++) {

    tbodyElements = doc.getElementsByTag("tbody");

    if (tbodyElements.isEmpty()) {
        throw new Exception("Table is not found");
    }
    elements = tbodyElements.get(0).getElementsByTag("tr");

    for (Element trElement : elements) {
        trElement2 = trElement.getElementsByTag("tr");
        tdElements = trElement.getElementsByTag("td");
        File fold = new File("C:\\convertedCSV9.csv");
        fold.delete();
        File fnew = new File("C:\\convertedCSV9.csv");
        FileWriter sb = new FileWriter(fnew, true);

        for (Iterator<Element> it = tdElements.iterator(); it.hasNext();) {

            Element tdElement1 = it.next();
            final String content2 = tdElement1.text();
            if (it.hasNext()) {
                sb.append("\n");

            }
            for (Iterator<Element> it2 = trElement2.iterator(); it.hasNext();) {
                Element tdElement2 = it.next();
                final String content = tdElement2.text();

                if (it2.hasNext()) {

                    sb.append(formatData(content));
                    sb.append("   ,   ");

                }
                if (!it.hasNext()) {
                    String content1 = content.replaceAll(",$", " ");
                    sb.append(formatData(content1));
                    it2.next();

                }

            }

            System.out.println(sb.toString());
            sb.flush();
            sb.close();

        }
        System.out.println(sampleList.add(tdElements));

    }
  }
}

And Create Script is

-- Create table
create table TEST_EXCEL_OPEN_END_SMRY
(
  fund_name     VARCHAR2(150),
  rating        VARCHAR2(50),
  validity_date VARCHAR2(50),
  fund_nav      VARCHAR2(50),
  ytd           VARCHAR2(50),
  mtd           VARCHAR2(50),
  day_1         VARCHAR2(50),
  days_15       VARCHAR2(50),
  days_30       VARCHAR2(50),
  days_90       VARCHAR2(50),
  days_180      VARCHAR2(50),
  days_270      VARCHAR2(50),
  days_365      VARCHAR2(50)
)
organization external
(
  type ORACLE_LOADER
  default directory DIR_KSE
  access parameters 
  (
    RECORDS DELIMITED BY NEWLINE
        badfile bad_dir:'revext%a_%p.bad'
        logfile log_dir:'revext%a_%p.log'
        FIELDS TERMINATED BY ','
        MISSING FIELD VALUES ARE NULL
        (
    FUND_NAME,
    RATING ,
    VALIDITY_DATE ,
    FUND_NAV ,
    YTD ,
    MTD ,
    DAY_1 ,
    DAYS_15 ,
    DAYS_30 ,
    DAYS_90 ,
    DAYS_180,
    DAYS_270,
    DAYS_365
        )
  )
  location (DIR_KSE:'convertedCSV9.csv')
)
reject limit UNLIMITED;

P.S: Path of the DIR_KSE is correct. I am also attaching the snapshot of CSV and Database result.

Did you check the input file? maybe there is an issue detecting end-of-line, making your file a single-liner. — J. Chomel
– J. Chomel, Commented Apr 21, 2016 at 7:26
I have attached the .csv file snapshot, there you can see, apparently there are no issues detected. — Ahmed Talha
– Ahmed Talha, Commented Apr 21, 2016 at 7:33
What does the log file say? Does Excel let you convert that text to data (since your screenshot has everying in cell A)? — Alex Poole
– Alex Poole, Commented Apr 21, 2016 at 7:39
@AlexPoole Sir actually this is a .CSV file (comma separated value file). As per my knowledge we cannot import data of excel directly into oracle sql database. For doing that we have to convert it in .CSV file. So, I scraped the data directly into CSV file so that I can skip that excel thing. — Ahmed Talha
– Ahmed Talha, Commented Apr 21, 2016 at 7:48
Yes, I understand it's CSV, but you've opened it in Excel to create the screenshot. I'm wondering if the commas are some other character, and whether dong 'Text to columns' from the Data ribbon complains. Telling us what the external table log file says would be more useful though. — Alex Poole
– Alex Poole, Commented Apr 21, 2016 at 7:51

Alex Poole · Accepted Answer · 2016-04-21 09:13:20Z

3

Your Java code is using a line feed character (LF) as a line separator:

            sb.append("\n");

That's fine in the Linux/UNIX world, but it seems your Oracle instance is running on Windows, and by default that expects Windows-style line separators, with both carriage return and line feed (CRLF).

When it reads the file it sees the LF on its own as just a character in the line, so it essentially sees the entire file as a single line, as J.Chomel suggested earlier.

You can either convert the line endings with a utility, or generate the file with the expected Windows separators by changing your code to:

            sb.append("\r\n");

answered Apr 21, 2016 at 9:13

Alex Poole

192k11 gold badges198 silver badges349 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Data not showing in the oracle Sql Database importing from .CSV

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related