Results 1 to 9 of 9
  1. #1
    Join Date
    Oct 2003
    Posts
    6

    Unanswered: not inserting last record

    I have a fixed-length flat file that I am loading into Oracle via SQL Loader. I need to be able to skip the last record. I am skipping the first record by using Skip=1. The problem with skipping the last record is that I do not ahead of time how many records are in the file. Basically, it is trailer record that needs to be deleted. How can I have my sql loader statement not insert that record?

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1

    Re: not inserting last record

    Originally posted by joee
    I have a fixed-length flat file that I am loading into Oracle via SQL Loader. I need to be able to skip the last record. I am skipping the first record by using Skip=1. The problem with skipping the last record is that I do not ahead of time how many records are in the file. Basically, it is trailer record that needs to be deleted. How can I have my sql loader statement not insert that record?
    Remove it from the file before invoking SQLLDR.

  3. #3
    Join Date
    Oct 2003
    Posts
    6

    removing data from flat-file

    I get the file via ftp from a partners of ours. How would I remove it from the falt file before invoking SQLLDR?

    I do know that the trailer always starts with the number 3. Can I add some logic into my ssqlldr statement that would exclude that record?

  4. #4
    Join Date
    Jul 2003
    Posts
    2,296
    r-click on the file that was FTP'd to you and click edit with notepad/textpad.
    goto the line you don't want and delete that line.


    If you could provide an example of that specific line and what makes it different than the other lines, then perhaps we could help you further.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  5. #5
    Join Date
    Oct 2003
    Posts
    6

    editing text file

    Actually, this is an automated process so I will not be able to right click on the file. Here is a my control file and a little bit of the text in the flat file. Again I need to exclude the last record.

    Control file:

    LOAD DATA
    INFILE 'F:\Web\portal\admin\payroll\hewitt\archive\200310 08.txt'
    INTO TABLE pyrl_hewitt_test
    APPEND
    (
    dte POSITION (1:8) char,
    ssn POSITION (9:17) char,
    id POSITION (18:32) char,
    emg POSITION (33:33) char,
    company POSITION (34:39) char,
    transcode POSITION (40:45) char,
    transdate POSITION (46:53) char,
    curroptid POSITION (54:57) char,
    prevoptid POSITION (58:61) char,
    transamt1 POSITION (62:68) char,
    transamt2 POSITION (69:75) char,
    transamt3 POSITION (76:82) char,
    ind1 POSITION (83:83) char,
    ind2 POSITION (84:84) char,
    ind3 POSITION (85:85) char
    )

    The flat-file:
    2003100612345678968375 040040GPE
    2003100712345678968407 050040GPX
    3000001049

  6. #6
    Join Date
    Sep 2003
    Location
    Milan, Italy
    Posts
    130
    You can't do it with sqlloader because it basically inserts a row, then another, and then says "hey no more records!" - but at that time, it has already inserted the last row.

    So you must find a way to remove the last record before, as The_Duck and anacedent suggested.

    You could perhaps write a small script that counts the number n of the rows, then instruct sqlloader to load just n-1 rows.

    In Unix/Linux, the program
    wc -l
    will count the lines for you.

  7. #7
    Join Date
    Oct 2003
    Location
    St.Louis,MO
    Posts
    120
    There is a VB loader out there called Visual Loader and I believe that it will allow you to specify a trailing row/footer to omit.
    Although it is not an automated procedure it may be of some help!?
    Last edited by tlael; 10-08-03 at 19:00.

  8. #8
    Join Date
    Jul 2003
    Posts
    2,296
    if the file gets FTP'd to Unix, then this is easy to solve.
    Windows is another problem.

    You can use a WHEN clause in your SQLloader control file however.
    That might work for you.

    WHEN column NOT LIKE '300%'

    WHEN column <> '3000001049'
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  9. #9
    Join Date
    Oct 2003
    Posts
    6
    We are using Windows. Can I still use the WHEN NOT LIKE clause? If so where does it go in the control file?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •