INSERT INTO myImportTable (UserName, Address, City) SELECT UserName, Address, City FROM OpenRowset('MSDASQL', 'Driver={Microsoft Text Driver (*.txt; *.csv)}; DefaultDir=C:\myDirec;','SELECT * FROM inputFile.txt') [allowsmilie] => 1 [showsignature] => 0 [ipaddress] => [iconid] => 0 [visible] => 1 [attach] => 0 [infraction] => 0 [reportthreadid] => 0 [isusenetpost] => 1 [msgid] => [ref] => [htmlstate] => on_nl2br [postusername] => mxstu [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] => ) --> EMPID = #cols[1]# EMPLOYEENAME = #cols[2]# EMPLOYEESUPERVISOR = #cols[3]# DEPT = #cols[4]#
[allowsmilie] => 1 [showsignature] => 0 [ipaddress] => [iconid] => 0 [visible] => 1 [attach] => 0 [infraction] => 0 [reportthreadid] => 0 [isusenetpost] => 1 [msgid] => [ref] => [htmlstate] => on_nl2br [postusername] => mxstu [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] => 4 [islastshown] => [isfirstshown] => [attachments] => [allattachments] => ) --> -brian "dj shane" wrote in message news:dm08dk$jro$macromedia.com...[ref] > Hi mx, thanks for the quick reply. > > How about just helping me out with this part then. I am reading for a tab > dilemeted text file. When I open the file in notepad, the tabbing is[/ref] obvious,[ref] > however when I read it in with CF, it seems to lose it's tabs. > > I'm thinking, read it in, insert into temp table, then run the routine of[/ref] the[ref] > update by selecting from temp table, then I can still automate this[/ref] process.[ref] > > Here's my code. > > file="E:\file.txt" > variable="var"> > > > #i#
>
>
> > It's giving me the following > > EMPID EMPLOYEENAME EMPLOYEESUPERVISOR DEPT > > without tabs, just spaces. Not sure how to sift through those and run an[/ref] insert[ref] >[/ref] [allowsmilie] => 1 [showsignature] => 0 [ipaddress] => [iconid] => 0 [visible] => 1 [attach] => 0 [infraction] => 0 [reportthreadid] => 0 [isusenetpost] => 1 [msgid] => [ref] => [htmlstate] => on_nl2br [postusername] => Brian [ip] => bhogueNOSPAM@cy [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] => 5 [islastshown] => [isfirstshown] => [attachments] => [allattachments] => ) --> Best way to handle this import - Coldfusion - Advanced Techniques

Best way to handle this import - Coldfusion - Advanced Techniques

Hey guys, I have a SQL db of an employee phone list, it stores their supervisor, job title, phone, etc. Once a month I want to update it with a tab dilemeted text file from HR without using DTS. The process has to be automated. So that HR can drop a file in a directory on a specific date, and my tasks runs and does the import. Basically, I need to take col1 from the text file which is the employee ID, and use it as my key when looping through the current employee SQL table and update the row ...

  1. #1

    Default Best way to handle this import

    Hey guys, I have a SQL db of an employee phone list, it stores their
    supervisor, job title, phone, etc.

    Once a month I want to update it with a tab dilemeted text file from HR
    without using DTS. The process has to be automated. So that HR can drop a file
    in a directory on a specific date, and my tasks runs and does the import.

    Basically, I need to take col1 from the text file which is the employee ID,
    and use it as my key when looping through the current employee SQL table and
    update the row with the data from the text file.

    I'm looking for syntax help / ideas.

    dj Guest

  2. #2

    Default Re: Best way to handle this import

    Sounds like you're using sql server. If you don't want to use DTS, you might
    take a look at either "linked servers" or the OPENROWSET() function. Both are
    simple and allow you to handle text files like regular tables. I don't
    typically used it with UPDATE statements, but it might be worth looking into.
    Worse case scenario, just insert the data into a temp table and use the temp
    table for updating.

    <!--- this is an old example I had lying around --->
    <cfquery name="addData" datasource="mySQLServerDSN">
    INSERT INTO myImportTable (UserName, Address, City)
    SELECT UserName, Address, City
    FROM OpenRowset('MSDASQL', 'Driver={Microsoft Text Driver (*.txt; *.csv)};
    DefaultDir=C:\myDirec;','SELECT * FROM inputFile.txt')
    </cfquery>

    mxstu Guest

  3. #3

    Default Re: Best way to handle this import

    Hi mx, thanks for the quick reply.

    How about just helping me out with this part then. I am reading for a tab
    dilemeted text file. When I open the file in notepad, the tabbing is obvious,
    however when I read it in with CF, it seems to lose it's tabs.

    I'm thinking, read it in, insert into temp table, then run the routine of the
    update by selecting from temp table, then I can still automate this process.

    Here's my code.

    <cffile action="read"
    file="E:\file.txt"
    variable="var">
    <cfoutput>
    <cfloop index="i" list="#var#" delimiters="#chr(10)#">
    #i#<br/>
    </cfloop>
    </cfoutput>

    It's giving me the following

    EMPID EMPLOYEENAME EMPLOYEESUPERVISOR DEPT

    without tabs, just spaces. Not sure how to sift through those and run an insert

    dj Guest

  4. #4

    Default Re: Best way to handle this import

    Split the data into rows (on the newline character) and columns (on the tab
    character). See attached example. It is long for clarity.

    I just tried the OPENROWSET() method and it does work with updates and doesn't
    require looping . You just use a schema.ini file to specify the tab delimited
    file format.





    <cffile action="read" file="#yourFilePath#" variable="data">
    <cfset newLine = chr(13) & chr(10)>
    <cfset tabChar = chr(9)>

    <cfoutput>
    <cfset counter = 1>
    <cfloop list="#data#" index="row" delimiters="#newLine#">
    <!--- skip row headers --->
    <cfif counter gt 1>
    <cfset cols = listToArray(row, tabChar)>
    <!---- replace this debug code with your insert query --->
    EMPID = #cols[1]#
    EMPLOYEENAME = #cols[2]#
    EMPLOYEESUPERVISOR = #cols[3]#
    DEPT = #cols[4]#<br>
    </cfif>
    <cfset counter = counter + 1>
    </cfloop>
    </cfoutput>

    mxstu Guest

  5. #5

    Default Re: Best way to handle this import

    dj shane -

    I have a monthly process similar to what you are asking for. In my
    situation, every record is an ADD. I have a limited number of records, so
    executing INSERT is not an issue. This code validates the input. The INSERTs
    do not start unless the file is validated. The INSERT is inside a CFloop of
    my "queryOut." You should notice that my delimiter is a comma ... see the
    CFloop tag.

    Hopefully this code will help you.


    <!---------------------------------------------- file ---->
    <!---------------------------------------------- read ---->
    <cffile action = "read" file = "#file_str#" variable = "inFile_str">
    <!---------------------------------------------- file ---->
    <!---------------------------------------------- read ---->
    <!----------------------------------------------- row ---->
    <cfset queryOut = QueryNew("field1, field2, field3, field4, field5,
    field6")>
    <cfset rowCounter_num = 1>
    <cfloop index="loopRow" list="#inFile_str#"
    delimiters="#chr(10)##chr(13)#"><!---- linefeed = Chr(10); carriage return =
    Chr(13) ---->
    <cfset dummy_str = QueryAddRow(queryOut, 1)>
    <cfset columnDataCounter_num = 1>
    <cfloop index="loopDataColumn" list="#loopRow#" delimiters=",">
    <cfswitch expression="#columnDataCounter_num#">
    <cfdefaultcase><!---- do nothing ----></cfdefaultcase>
    <cfcase value="1"><cfset dummy_str = QuerySetCell(queryOut, "field1",
    #loopDataColumn#)></cfcase>
    <cfcase value="2"><cfset dummy_str = QuerySetCell(queryOut, "field2",
    #loopDataColumn#)></cfcase>
    <cfcase value="3"><cfset dummy_str = QuerySetCell(queryOut, "field3",
    #loopDataColumn#)></cfcase>
    <cfcase value="4"><cfset dummy_str = QuerySetCell(queryOut, "field4",
    #loopDataColumn#)></cfcase>
    <cfcase value="5"><cfset dummy_str = QuerySetCell(queryOut, "field5",
    #loopDataColumn#)></cfcase>
    <cfcase value="6"><cfset dummy_str = QuerySetCell(queryOut, "field6",
    #loopDataColumn#)></cfcase>
    </cfswitch>
    <cfset columnDataCounter_num = columnDataCounter_num + 1>
    </cfloop>
    <cfset rowCounter_num = rowCounter_num + 1>
    </cfloop>

    -brian


    "dj shane" <com> wrote in message
    news:dm08dk$jro$macromedia.com... 
    obvious, 
    the 
    process. 
    insert 


    Brian Guest

  6. #6

    Default Re: Best way to handle this import

    Brian Hogue,

    Doesn't your database have a tool for importing data from a CSV file? It would save a lot of looping...


    mxstu Guest

  7. #7

    Default Re: Best way to handle this import

    Sine this has to be completely automated and I cannot use DTS to do the
    transfer myself, I think the best way to handle this is the following.


    1. User drops CSV file into a shared directory.
    2. Cold Fusion picks it up nightly, inserts it into a temp table in SQL
    3. Cold Fusion selects from the temp table and does the comparison to the real
    data and updates the rows accordingly, temp table is cleared out.

    We're talking about 500 rows max. Process shouldn't take longer then 20-30
    seconds if I get it right.

    dj Guest

  8. #8

    Default Re: Best way to handle this import

    dj shane,

    If you're going to be inserting into a temp table, I would probably lean
    towards using linked servers or OPENROWSET (with a schema.ini file) instead of
    all the cflooping. It seems much simpler. If you want an example, let me know.



    mxstu Guest

  9. #9

    Default Re: Best way to handle this import

    dj shane.

    Can your cold fusion talk to the hr database?

    These 500 records in a typical csv file, about how many are:
    a. brand new records?
    b. updates to existing records?
    c. exact matches of existing records.



    Dan Guest

Similar Threads

  1. How to import old file import filters in Illustrator CS.
    By Dani_Maning@adobeforums.com in forum Adobe Illustrator Windows
    Replies: 2
    Last Post: May 12th, 04:10 PM
  2. When to use a Ptr vs a Handle?
    By Peter in forum Mac Programming
    Replies: 20
    Last Post: February 4th, 07:18 AM
  3. how would i handle this?
    By The in forum PERL Miscellaneous
    Replies: 4
    Last Post: October 19th, 06:05 PM
  4. handle the IE.
    By Mr. x in forum Macromedia Flash
    Replies: 1
    Last Post: October 8th, 09:33 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
  •