DELETE * FROM tblAS400 WHERE WeekEnding = '#impThru#' INSERT INTO tblAS400 ( WeekEnding, EmpNo, WeekDay, ActShift, WorkDate, SelShift, PayGroup, PaySeq, JobCode, KeySub, DayDesc, TourSwing, Tour40, ProcCode, JobSpec, PrintCode, PosType, TourAssign, JobName, Dept, ShiftDiff, ActHH, Comp40HH, OTCode, OTHH, AllowCode, AllowHH, PayRate, RateCode, Element, PayrollCode, BlankLines, TrainCode, SwapFlag, TimeOffFlag ) VALUES ( '#impThru#', '#iEmpNo#', '#iWeekDay#', '#iActShift#', '#iWorkDate#', '#iSelShift#', '#iPayGroup#', '#iPaySeq#', '#iJobCode#', '#iKeySub#', '#iDayDesc#', '#iTourSwing#', '#iTour40#', '#iProcCode#', '#iJobSpec#', '#iPrintCode#', '#iPosType#', '#iTourAssign#', '#iJobName#', '#iDept#', '#Left(iShiftDiff,1)#.#Right(iShiftDiff,2)#', '#Left(iActHH,2)#.#Right(iActHH,1)#', '#Left(iComp40HH,2)#.#Right(iComp40HH,1)#', '#iOTCode#', '#Left(iOTHH,2)#.#Right(iOTHH,1)#', '#iAllowCode#', '#Left(iAllowHH,2)#.#Right(iAllowHH,1)#', '#Left(iPayRate,2)#.#Right(iPayRate,2)#', '#iRateCode#', '#iElement#', '#iPayrollCode#', '#iBlankLines#', '#iTrainCode#', '#iSwapFlag#', '#iTimeOffFlag#' ) [allowsmilie] => 1 [showsignature] => 0 [ipaddress] => [iconid] => 0 [visible] => 1 [attach] => 0 [infraction] => 0 [reportthreadid] => 0 [isusenetpost] => 1 [msgid] => [ref] => [htmlstate] => on_nl2br [postusername] => JMGibson3 [ip] => webforumsuser@m [isdeleted] => 0 [usergroupid] => [membergroupids] => [displaygroupid] => [password] => [passworddate] => [email] => [styleid] => [parentemail] => [homepage] => [icq] => [aim] => [yahoo] => [msn] => [skype] => [showvbcode] => [showbirthday] => [usertitle] => [customtitle] => [joindate] => [daysprune] => [lastvisit] => [lastactivity] => [lastpost] => [lastpostid] => [posts] => [reputation] => [reputationlevelid] => [timezoneoffset] => [pmpopup] => [avatarid] => [avatarrevision] => [profilepicrevision] => [sigpicrevision] => [options] => [akvbghsfs_optionsfield] => [birthday] => [birthday_search] => [maxposts] => [startofweek] => [referrerid] => [languageid] => [emailstamp] => [threadedmode] => [autosubscribe] => [pmtotal] => [pmunread] => [salt] => [ipoints] => [infractions] => [warnings] => [infractiongroupids] => [infractiongroupid] => [adminoptions] => [profilevisits] => [friendcount] => [friendreqcount] => [vmunreadcount] => [vmmoderatedcount] => [socgroupinvitecount] => [socgroupreqcount] => [pcunreadcount] => [pcmoderatedcount] => [gmmoderatedcount] => [assetposthash] => [fbuserid] => [fbjoindate] => [fbname] => [logintype] => [fbaccesstoken] => [newrepcount] => [vbseo_likes_in] => [vbseo_likes_out] => [vbseo_likes_unread] => [temp] => [field1] => [field2] => [field3] => [field4] => [field5] => [subfolders] => [pmfolders] => [buddylist] => [ignorelist] => [signature] => [searchprefs] => [rank] => [icontitle] => [iconpath] => [avatarpath] => [hascustomavatar] => 0 [avatardateline] => [avwidth] => [avheight] => [edit_userid] => [edit_username] => [edit_dateline] => [edit_reason] => [hashistory] => [pagetext_html] => [hasimages] => [signatureparsed] => [sighasimages] => [sigpic] => [sigpicdateline] => [sigpicwidth] => [sigpicheight] => [postcount] => 2 [islastshown] => [isfirstshown] => [attachments] => [allattachments] => ) --> insert flat file to database - Coldfusion - Advanced Techniques

insert flat file to database - Coldfusion - Advanced Techniques

Hi, I need to import data from a flat file to the Oracle database. The file is not a csv file. The file is not a comma delimited file. It is a fixed length file (each record is 450 characters). Some of the fields also contain ?zoned decimal? values. The size of the file is 300,000 K. In addition, each record is in a variant record format. That is each record has a primary record type, let?s say from position 1 to 6. Depends on the value of the primary record type, we then determine the sub record type (can ...

  1. #1

    Default insert flat file to database

    Hi,

    I need to import data from a flat file to the Oracle database. The file is
    not a csv file. The file is not a comma delimited file. It is a fixed length
    file (each record is 450 characters). Some of the fields also contain ?zoned
    decimal? values. The size of the file is 300,000 K. In addition, each record
    is in a variant record format. That is each record has a primary record type,
    let?s say from position 1 to 6. Depends on the value of the primary record
    type, we then determine the sub record type (can locate in different location).
    Then depends on the sub record type, we determine the rest of the data.

    I am using ColdFusion MX version 6. How can I use ColdFusion to perform the
    above task? If I can?t use ColdFusion to do this, does anyone one know what
    tool I can use? Thanks for your help in advance.


    chingc Guest

  2. #2

    Default Re: insert flat file to database

    I just happen to be working on a project like that right now.
    The typical Import method is CFFILE to read the file, and a List type CFLOOP,
    using CRLF's as the delimiter to process each record, and Mid() functions to
    split out fields based on record types. I've attached my current code as a
    starting point.

    Problem I forsee for you is File Size. My file is 500K and runs in 30 seconds
    on a fairly primitive server. At 300,000K, your file would take at least 5
    hours on my server. Certainly much much more with memory swapping. You will
    have to carve that beast into chunks, and/or reduce the 450 record length to
    the absolute minimum fields you need.

    <cfset impPath ="\\WBK_COMMON_FS1\CTM_DATA\COLDFUSION_IMPORTS\ ">
    <cfset impArchPath ="\\WBK_COMMON_FS1\CTM_DATA\COLDFUSION_SUCCESSFUL_ IMPORTS\">
    <cfset impFileName = "Payroll_Data.txt">
    <cfset impFile = "#impPath##impFileName#">

    <cfset impFrom = DateFormat(DateAdd("d","-6",Session.ImpDate),"YYMMDD")>
    <cfset impThru = DateFormat(Session.ImpDate,"YYMMDD")>
    <cfset wrkImpStat = "OK">

    <!--- Import Active AS400 Time Data --->
    <cffile ACTION ="read"
    FILE ="#impFile#"
    VARIABLE="varWork">


    <cfset impRowCtr = 0>
    <cfset impAddCtr = 0>
    <cfset impBypCtr = 0>
    <cfset impDupCtr = 0>
    <cfset impBadCtr = 0>

    <cfset impHiDate="01/01/00">
    <cfset logLStat = "OK">
    <cfset logLMsg = "Imported">
    <cfset logLeMsg = impFile>

    <!--- Delete what we're Importing --->
    <CFQUERY datasource=#constPrimaryDataSource# NAME="delAS400">
    DELETE *
    FROM tblAS400
    WHERE WeekEnding = '#impThru#'
    </CFQUERY>

    <!--- Insert Imports one by one --->
    <CFLOOP Index="varLine" List="#varWork#" Delimiters="#chr(13)##chr(10)#">
    <cfset impRowCtr = impRowCtr + 1>
    <cfset iWorkDate = Mid(varLine,001,006)>
    <cfif iWorkDate LT impFrom OR
    iWorkDate GT impThru>
    <cfset impBypCtr = impBypCtr + 1>
    <cfelse>
    <cfset iSelShift = Mid(varLine,007,001)>
    <cfset iPayGroup = Mid(varLine,008,003)>
    <cfset iPaySeq = Mid(varLine,011,003)>
    <cfset iJobCode = Mid(varLine,014,007)>
    <cfset iKeySub = Mid(varLine,021,001)>
    <cfset iWeekDay = Mid(varLine,022,001)>
    <cfset iDayDesc = Mid(varLine,023,011)>
    <cfset iActShift = Mid(varLine,034,001)>
    <cfset iTourSwing = Mid(varLine,035,001)>
    <cfset iTour40 = Mid(varLine,036,001)>
    <cfset iProcCode = Mid(varLine,037,001)>
    <cfset iJobSpec = Mid(varLine,038,001)>
    <cfset iPrintCode = Mid(varLine,039,001)>
    <cfset iPosType = Mid(varLine,040,001)>
    <cfset iTourAssign = Mid(varLine,041,001)>
    <cfset iJobName = Mid(varLine,042,018)>
    <cfset iDept = Mid(varLine,062,005)>
    <cfset iEmpNo = Mid(varLine,067,005)>
    <cfset iShiftDiff = Mid(varLine,072,003)>
    <cfset iActHH = Mid(varLine,075,003)>
    <cfset iComp40HH = Mid(varLine,078,003)>
    <cfset iOTCode = Mid(varLine,081,001)>
    <cfset iOTHH = Mid(varLine,082,003)>
    <cfset iAllowCode = Mid(varLine,085,001)>
    <cfset iAllowHH = Mid(varLine,086,003)>
    <cfset iPayRate = Mid(varLine,089,004)>
    <cfset iRateCode = Mid(varLine,093,001)>
    <cfset iElement = Mid(varLine,094,003)>
    <cfset iPayrollCode = Mid(varLine,097,001)>
    <cfset iBlankLines = Mid(varLine,098,002)>
    <cfset iTrainCode = Mid(varLine,100,001)>
    <cfset iSwapFlag = Mid(varLine,101,001)>
    <cfset iTimeOffFlag = Mid(varLine,102,001)>
    <!--- From here on, not captured --->
    <cfset iLastUpdBy = Mid(varLine,107,004)>
    <cfset iAS4RunDate = Mid(varLine,111,006)>
    <cfset iAS4RunTime = Mid(varLine,117,006)>
    <cfset iAS4RunUser = Mid(varLine,123,004)>
    <cfset iAS4RunTerm = Mid(varLine,127,002)>
    <cfset iTempJob = Mid(varLine,129,007)>
    <cfset iWKYJB = Mid(varLine,133,007)>
    <cfset iJBTRN = Mid(varLine,137,007)>
    <cfset iPYRAT = Mid(varLine,141,005)>
    <cfset iPRCD = Mid(varLine,144,001)>
    <cftry>
    <CFSET impAddCtr = impAddCtr + 1>
    <CFQUERY datasource=#constPrimaryDataSource# NAME="impAS400">
    INSERT INTO tblAS400 (
    WeekEnding,
    EmpNo,
    WeekDay,
    ActShift,
    WorkDate,
    SelShift,
    PayGroup,
    PaySeq,
    JobCode,
    KeySub,
    DayDesc,
    TourSwing,
    Tour40,
    ProcCode,
    JobSpec,
    PrintCode,
    PosType,
    TourAssign,
    JobName,
    Dept,
    ShiftDiff,
    ActHH,
    Comp40HH,
    OTCode,
    OTHH,
    AllowCode,
    AllowHH,
    PayRate,
    RateCode,
    Element,
    PayrollCode,
    BlankLines,
    TrainCode,
    SwapFlag,
    TimeOffFlag
    )
    VALUES (
    '#impThru#',
    '#iEmpNo#',
    '#iWeekDay#',
    '#iActShift#',
    '#iWorkDate#',
    '#iSelShift#',
    '#iPayGroup#',
    '#iPaySeq#',
    '#iJobCode#',
    '#iKeySub#',
    '#iDayDesc#',
    '#iTourSwing#',
    '#iTour40#',
    '#iProcCode#',
    '#iJobSpec#',
    '#iPrintCode#',
    '#iPosType#',
    '#iTourAssign#',
    '#iJobName#',
    '#iDept#',
    '#Left(iShiftDiff,1)#.#Right(iShiftDiff,2)#',
    '#Left(iActHH,2)#.#Right(iActHH,1)#',
    '#Left(iComp40HH,2)#.#Right(iComp40HH,1)#',
    '#iOTCode#',
    '#Left(iOTHH,2)#.#Right(iOTHH,1)#',
    '#iAllowCode#',
    '#Left(iAllowHH,2)#.#Right(iAllowHH,1)#',
    '#Left(iPayRate,2)#.#Right(iPayRate,2)#',
    '#iRateCode#',
    '#iElement#',
    '#iPayrollCode#',
    '#iBlankLines#',
    '#iTrainCode#',
    '#iSwapFlag#',
    '#iTimeOffFlag#'
    )
    </CFQUERY>
    <cfcatch type="Database">
    <CFSET impAddCtr = impAddCtr - 1>
    <cfif CFCatch.SQLSTATE eq 23000 or
    CFCatch.SQLSTATE eq 3022>
    <cfset impDupCtr = impDupCtr +1>
    <cfelse>
    <cfset logLStat = CFCatch.SQLSTATE>
    <cfset logLMsg = CFCatch.Message>
    <cfset logLeMsg = CFCatch.Detail>
    <cfset logLeMsg = ListGetAt(logLeMsg,1,"<")>
    <cfset impBadCtr = impBadCtr +1>
    </cfif>
    </cfcatch>
    </cftry>
    </cfif>
    </cfloop>

    JMGibson3 Guest

  3. #3

    Default Re: insert flat file to database

    Thank you very much. It helpsme a log.
    chingc Guest

Similar Threads

  1. read text file insert into database
    By navee in forum Macromedia ColdFusion
    Replies: 2
    Last Post: April 11th, 08:08 AM
  2. Replies: 1
    Last Post: June 30th, 08:47 PM
  3. Import Data from flat-database to relational-database
    By Dieter Schmidt in forum FileMaker
    Replies: 1
    Last Post: September 17th, 12:05 PM
  4. Replies: 2
    Last Post: September 16th, 10:30 AM
  5. Replies: 0
    Last Post: August 22nd, 10:37 PM

Bookmarks

Posting Permissions

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