Home » RDBMS Server » Server Utilities » SQL Loader handling different date formats in same csv file (10G, 10.2.0, Windows xp)
SQL Loader handling different date formats in same csv file [message #515480] Mon, 11 July 2011 17:42 Go to next message
rsager
Messages: 17
Registered: June 2011
Location: Sydney, Australia
Junior Member
Hi all you guru's..

I have another interesting SQL Loader issue. I have created 2 prior topics RE: "Trapping SQL Loader summary counts" and "Using Sql Loader issue". This new issue is along the same lines, but unfortunately the same input csv file has a date field that is has a different input format...let me explain...
The 10G Oracle Table looks like...
---------------------------
CREATE TABLE PTLIVE.MODULE_CSV_LOADS
( MODULE_ID NUMBER (20),
MODULE_TAGNUMBER VARCHAR2(100),
MODULE_SERIAL_NUMBER NUMBER (20,0),
LATITUDE VARCHAR2(20),
LONGITUDE VARCHAR2(20),
GMT_TIME VARCHAR2(10),
TAGCOUNT VARCHAR2(5),
DATEPICKED DATE,
GROWER VARCHAR2(40),
FARM VARCHAR2(40),
FIELD VARCHAR2(40),
VARIETY VARCHAR2(40),
PICKER VARCHAR2(40),
IMPORT_CSV_FILE_NAME VARCHAR2(256),
DATEIMPORTED DATE default SYSDATE,
EXPORTED_TO_PTLIVE NUMBER(1) default 0
)
-----------------
My initial control file looks like...
-----------------
OPTIONS (SKIP=1)
load data
infile 'J:\Grower\Round_Modules\Crop2011\Processed\Batch_2011Jul12_081326_746563.csv'
BADFILE 'J:\Grower\Round_Modules\Crop2011\Logs\Batch_2011Jul12_081326_746563.bad'
append into table PTLIVE.MODULE_CSV_LOADS
WHEN MODULE_TAGNUMBER != 'Total Modules'
AND MODULE_TAGNUMBER != 'Mis-reads'
AND MODULE_TAGNUMBER != ' '
AND MODULE_TAGNUMBER != 'No Module ID'
FIELDS TERMINATED BY ","
( MODULE_TAGNUMBER,
MODULE_SERIAL_NUMBER,
LATITUDE,
LONGITUDE,
GMT_TIME,
TAGCOUNT,
DATEPICKED "to_date(:DATEPICKED||:GMT_TIME, 'DD/MM/YYYYHH24:MI:SS')",
GROWER,
FARM,
FIELD,
VARIETY,
PICKER,
IMPORT_CSV_FILE_NAME CONSTANT "Batch_2011Jul12_081326_746563.csv"
)
into table PTLIVE.MODULE_CSV_LOADS_SUMMARY
WHEN (1) = 'Total Modules'
FIELDS TERMINATED BY ","
( IMPORT_CSV_FILE_NAME CONSTANT "Batch_2011Jul12_081326_746563.csv",
TOTAL_MODULES_READ position (15)
)
into table PTLIVE.MODULE_CSV_LOADS_SUMMARY
WHEN (1) = 'Mis-reads'
FIELDS TERMINATED BY ","
( IMPORT_CSV_FILE_NAME CONSTANT "Batch_2011Jul12_081326_746563.csv",
TOTAL_MODULES_MIS_READ position (11)
)
-------------------
The input csv data file (for this example) contains 3 records. The first is a heading record that is skipped. The 2nd record
is correct and the field entitled "GMT Date" contains a value of
"16/05/2011". The 3rd record, however, has a date of "2011/5/18", which get rejected by the above control file, as the output SQL Loader log indicates...
------------------
Record 2: Rejected - Error on table PTLIVE.MODULE_CSV_LOADS, column DATEPICKED.
ORA-01861: literal does not match format string
-------------------
Now we will be receiving literally hundreds of these csv files and in testing I have found that when I open the csv file (the default is Excel), and Excel must alter its display, as all date appear 100% okay. However, upon opening the csv file with Wordpad, I discovered the dates in the same file had these 2 different formats. So the control file was attempting to concat the input csv file "GMT Date" in one format with the input "GMT_TIME" to load the output value into the Oracle table column
"DATEPICKED" ... but different date formats cause some records to be rejected.

It would be super if SQl Loader could use a IF clause or an OR clause to execute loading the date in one or more input formats. We only expect the 2 foramts DD/MM/YYYY or YYYY/MM/DD....however, there could be 6 different combinations in theory. Tricky hey?

I thought about writing a Function or Procedure to analyst the input date and output a standard one to load into the Oracle column,...but I was hoping to get SQl Loader to handle these date issue. Might be stretching the friendship?

Any suggestion folks?

Cheers
Roger


Re: SQL Loader handling different date formats in same csv file [message #515481 is a reply to message #515480] Mon, 11 July 2011 17:56 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/
Re: SQL Loader handling different date formats in same csv file [message #515482 is a reply to message #515481] Mon, 11 July 2011 18:07 Go to previous messageGo to next message
rsager
Messages: 17
Registered: June 2011
Location: Sydney, Australia
Junior Member
Hello BlackSwan...
I have looked at the link on the Guidelines and do not quite understand which one(s) I have upset? If you compare my posting to your guidelines ...what am I doing incorrectly...from your point of view. If you point them out, then I will try to correct whatever I am doing wrong. I thought the more detailed the posting the better to explain it to others reading and replying?
Cheers
Re: SQL Loader handling different date formats in same csv file [message #515483 is a reply to message #515482] Mon, 11 July 2011 18:17 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Will any specific *CSV contain only a single date format?
Re: SQL Loader handling different date formats in same csv file [message #515484 is a reply to message #515483] Mon, 11 July 2011 18:25 Go to previous messageGo to next message
rsager
Messages: 17
Registered: June 2011
Location: Sydney, Australia
Junior Member
Hi...
In testing the input csv files that come from a number of our cotton growers, I have discovered that a single file contains a few hundred rows and the field "GMT DATE" has 2 different formats (so far) in the same csv file! Rather than reinvent the wheel...I found on Ask Tom a PL/SQL package that might do the trick to analysis 6 different formats possible and return a single format to load into the column table. So far and within the same csv file the date have been either dd/mm/yyyy or yyyy/mm/dd.
Cheers
Re: SQL Loader handling different date formats in same csv file [message #515486 is a reply to message #515484] Mon, 11 July 2011 20:14 Go to previous messageGo to next message
rsager
Messages: 17
Registered: June 2011
Location: Sydney, Australia
Junior Member
Hi all...

While waiting for a reply....I decided to write a package to format the date. Now the input csv file (and the oracle table column) have a time field called "GMT_TIME" and the csv file has a field called "GMT DATE",but the Oracle table column is DATEPICKED, which is the input ccsv file fields GMT DATE + GMT TIME.
So the Package is as follows:
------------------------------
create or replace package GET_DATEPICKED_PKG
as
function thedate
( date_string in varchar2,
time_string in varchar2 )
return date;
end;
/


create or replace package body GET_DATEPICKED_PKG
as
type fmtArray is table of varchar2(30);
g_fmts fmtArray :=
fmtArray( 'dd/mm/yyyy',
'yyyy/mm/dd',
'dd/yyyy/mm',
'mm/dd/yyyy',
'mm/yyyy/dd',
'yyyy/dd/mm' );

function thedate( date_string in varchar2, time_string in varchar2)
return date is return_value date;

begin
for i in 1 .. g_fmts.count
loop
begin
return_value := to_date(date_string,g_fmts(i) );
return_value := to_date((to_char(return_value,'dd/mm/yyyy')||time_string), 'dd/mm/yyyyHH24:MI:SS');
exit;
exception
when others then null;
end;
end loop;

if ( return_value is null ) then
raise PROGRAM_ERROR;
end if;
return return_value;
end;
end;
/
------------------------
The control file that is input into the SQL LOader is :
----------------------
OPTIONS (SKIP=1)
load data
infile 'J:\Grower\Round_Modules\Crop2011\Processed\Batch_2011Jul12_081326_746563.csv'
BADFILE 'J:\Grower\Round_Modules\Crop2011\Logs\Batch_2011Jul12_081326_746563.bad'
append into table PTLIVE.MODULE_CSV_LOADS
WHEN MODULE_TAGNUMBER != 'Total Modules'
AND MODULE_TAGNUMBER != 'Mis-reads'
AND MODULE_TAGNUMBER != ' '
AND MODULE_TAGNUMBER != 'No Module ID'
FIELDS TERMINATED BY ","
( MODULE_TAGNUMBER,
MODULE_SERIAL_NUMBER,
LATITUDE,
LONGITUDE,
GMT_TIME,
TAGCOUNT,
DATEPICKED "GET_DATEPICKED_PKG.thedate(:DATEPICKED,:GMT_TIME)",
GROWER,
FARM,
FIELD,
VARIETY,
PICKER,
IMPORT_CSV_FILE_NAME CONSTANT "Batch_2011Jul12_081326_746563.csv"
)
into table PTLIVE.MODULE_CSV_LOADS_SUMMARY
WHEN (1) = 'Total Modules'
FIELDS TERMINATED BY ","
( IMPORT_CSV_FILE_NAME CONSTANT "Batch_2011Jul12_081326_746563.csv",
TOTAL_MODULES_READ position (15)
)
into table PTLIVE.MODULE_CSV_LOADS_SUMMARY
WHEN (1) = 'Mis-reads'
FIELDS TERMINATED BY ","
( IMPORT_CSV_FILE_NAME CONSTANT "Batch_2011Jul12_081326_746563.csv",
TOTAL_MODULES_MIS_READ position (11)
)
-------------------------
Now this works and the data is as follows in the Oracle table:
-----------------------------
SQL> select IMPORT_CSV_FILE_NAME, DATEPICKED, GMT_TIME from MODULE_CSV_LOADS;

no rows selected

SQL> /

IMPORT_CSV_FILE_NAME DATEPICKE GMT_TIME
-------------------------------------------------- --------- ----------
Batch_2011Jul12_081326_746563.csv 16/MAY/11 5:55:43
Batch_2011Jul12_081326_746563.csv 18/MAY/11 07:09:06

CVheers
Re: SQL Loader handling different date formats in same csv file [message #515506 is a reply to message #515486] Tue, 12 July 2011 00:52 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
That's really nice, thank you for sharing!

A question, if I may: do you create control files dynamically? Because, you are storing CSV file name as a CONSTANT, so - do you modify control files manually? With many CSV files daily? I guess not (but I might be wrong).
Re: SQL Loader handling different date formats in same csv file [message #515520 is a reply to message #515486] Tue, 12 July 2011 01:26 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
A comment: do NOT use WHEN OTHERS, explicitly give all the errors that are related to DATE as this is the ones you want to trap not ALL errors and you may want to know if a line is not loaded this is not because of an incorrect format but of another error otherwise you will come here with the kind of question "I don't understand this line is correct but was not loaded".

Regards
Michel
Re: SQL Loader handling different date formats in same csv file [message #515665 is a reply to message #515506] Tue, 12 July 2011 16:41 Go to previous messageGo to next message
rsager
Messages: 17
Registered: June 2011
Location: Sydney, Australia
Junior Member
Hi Littlefoot...

Yes the control file is created on the fly dynamically. The csv files are emailed to a specific MS Outlook account and there is a VBA script created (in Outlook Alt+F11) to execute when a message arrives with the word "Round" (in any case). The VBA script then extracts the csv file(s), renames them to be unique, writes all attached csv to a file server, and calls a *.bat file the dynamically builts a Master *.bat, the control file and sqlplus file, then executes them. But I find that there is only so much that SQL Loader can do ...so it much rely on PL/SQL and other code to take the data input from the csv and manipulate it into Oracle tables.

Roger
Re: SQL Loader handling different date formats in same csv file [message #515666 is a reply to message #515665] Tue, 12 July 2011 16:52 Go to previous messageGo to next message
rsager
Messages: 17
Registered: June 2011
Location: Sydney, Australia
Junior Member
Hi again....

For all those interested in learning how to get MS Outlook to rename and save a csv file to a file server location from incoming email with csv files and other files attached to the email....here is the VBA script loaded into Outlook (ie Alt+F11)...
Option Compare Text
Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

Private WithEvents Items As Outlook.Items

Private Sub Application_Startup()
    Dim olApp As Outlook.Application
    Dim objNS As Outlook.NameSpace
    Set olApp = Outlook.Application
    Set objNS = olApp.GetNamespace("MAPI")
    Set Items = objNS.GetDefaultFolder(olFolderInbox).Items
End Sub

Private Sub Items_ItemAdd(ByVal Item As Object)
On Error GoTo ErrorHandler

    'Only act if it's a MailItem
    Dim Msg As Outlook.MailItem
    Dim i As Integer
    Dim ExecuteBatchScript As Boolean
    
    If TypeName(Item) = "MailItem" Then
        Set Msg = Item


    'Change variables to match need. Comment or delete any part unnecessary.
    'If (InStr(1, Msg.Subject, "", vbTextCompare) <> 0) And _
                '(Msg.Attachments.Count >= 1) Then
    If (InStr(1, Msg.Subject, "Round", vbTextCompare) <> 0) And _
        (Msg.Attachments.Count >= 1) Then
       
    'Set folder to save in.
    Dim olDestFldr As Outlook.MAPIFolder
    Dim myAttachments As Outlook.Attachments
    Dim FileName As String
    Dim FileNameNoExtension As String
    Dim RndID As String
        
    'location to save in.  Can be root drive or mapped network drive.
    'Const attPath As String = "C:\Downloads\Outlook_Round_Modules\"
    Const attPath As String = "J:\Grower\Round_Modules\Crop2011\Processed\"
       
       
    ' save attachment in format Batch literal + Datetime stamp + 6 digit random number
    '                            + '.' + file extension, which is either "csv" or "ERR" for unknown error file type
   Set myAttachments = Item.Attachments
   Randomize
    
    'Att = FindGrowerID(Msg.Subject) & "_" & _

    For i = 1 To myAttachments.Count
        RndID = CStr(Int((999999 + 1) * Rnd))
        
        FileNameNoExtension = "Batch_" & _
        Format(Now(), "YYYYMMMDD_hhmmss") & "_" & _
        RndID

        FileName = FileNameNoExtension & GetFileExtension(myAttachments.Item(i).FileName, ExecuteBatchScript)
        
        myAttachments.Item(i).SaveAsFile attPath & FileName
        
        If ExecuteBatchScript = True Then
            Shell "J:\Grower\Round_Modules\Crop2011\Master_scripts\RoundModule_Generate_Scripts_Master.bat "
 & FileNameNoExtension & " " & RndID, vbMaximizedFocus
        End If
       
       ' The sleep command pauses 5 server seconds in order to give the sql 
step 2 time to update the batch file name
       ' with the csv file name for the csv file processed, before the next 
csv is processed and appends more records to the submodules table
       ' Sleep 1000
    Next i
        
    ' mark as read
   Msg.UnRead = False
End If
End If
    

ProgramExit:
  Exit Sub
  
ErrorHandler:
  MsgBox Err.Number & " - " & Err.Description
  Resume ProgramExit
End Sub

'Private Function FindGrowerID(Subject As String) As String
'    Dim StartIndex As String
'    Dim i As Integer
'
'    Dim GrowerID As String
'
'    StartIndex = InStr(1, Subject, "grower", vbTextCompare)
'    For i = (StartIndex + 6) To Len(Subject)
'        If i = Len(Subject) Or (IsNumeric(Mid(Subject, i + 1, 1)) = False) Then
'            FindGrowerID = Mid(Subject, StartIndex, ((i + 1) - StartIndex))
'            Exit For
'        End If
'    Next i
'End Function

Private Function GetFileExtension(FileName As String, ByRef ExecuteBatchScript As Boolean) As String
    Dim i As Integer
    Dim extension As String

    For i = 1 To Len(FileName)
        If Mid(FileName, i, 1) = "." Then
            extension = Mid(FileName, i)
        End If
    Next i
    
    ExecuteBatchScript = True
    If Not extension = ".csv" Then
        ExecuteBatchScript = False
        extension = extension & ".ERR"
    End If
    
    GetFileExtension = extension
End Function


cheers
Roger
Note: the lines with a leading single quote (') is a comment line

[EDITED by LF: applied [code] tags]

[Updated on: Wed, 13 July 2011 02:49] by Moderator

Report message to a moderator

Re: SQL Loader handling different date formats in same csv file [message #515720 is a reply to message #515665] Wed, 13 July 2011 01:07 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
rsager wrote on Tue, 12 July 2011 23:41
I find that there is only so much that SQL Loader can do ...so it much rely on PL/SQL and other code to take the data input from the csv and manipulate it into Oracle tables.

As you use Oracle 10, I don't know whether you know its external tables concept. They are somewhat more flexible than SQL*Loader itself, as they allow you to manipulate input data (i.e. your CSV files) using SQL and/or PL/SQL capabilities because CSV file is - in that concept - considered to be an ordinary Oracle database table. (OK, read-only, but you just select from it anyway). With PL/SQL, you can do almost everything you want. So, perhaps you might take a look.

Also, thank you for sharing the VBA script.

A suggestion: see how to format your code guide and, in your next posts, include code into the [code] tags - it is easier to read and preserves formatting.
Re: SQL Loader handling different date formats in same csv file [message #515865 is a reply to message #515720] Wed, 13 July 2011 18:46 Go to previous message
rsager
Messages: 17
Registered: June 2011
Location: Sydney, Australia
Junior Member
Hey Littlefoot...
Thanks for the update....no I did not know about the external tables concept. Handy for the future. I hadn't use SQL Loader for about 7 years, but I am now coming to terms with it with this new csv file via Outlook etc. From time to time a new cricket ball comes down the pitch and you have to do what ever works to hit a 6.

I have my solution now and it works until some new issues arise in the input csv's. Like just yesterday, the csv cam in with no summary totals at the bottom of the file, so I write yet another dynamic SQl Loader routine to query the previous SQL Loader output log file to pick up the summary total ...like
Total logical records skipped: 1
Total logical records read: 1477
Total logical records rejected: 1
Total logical records discarded: 448

to store into a table of summary results.
Cheers
Roger
Previous Topic: How can i get the total time when using export a table(2 Merged)
Next Topic: Getting SQL Loader summary results from it log file
Goto Forum:
  


Current Time: Thu Mar 28 04:33:23 CDT 2024