SELECT Name, NickName FROM [Sheet1$] ... if you are using an Access DSN, you don't need to create an Excel DSN.... SELECT ID, Name FROM [Sheet1$] IN 'c:\someDirec\someExcelFile.xls' 'EXCEL 8.0;' [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] => 5 [islastshown] => 1 [isfirstshown] => [attachments] => [allattachments] => ) --> Cleaning Up Excel Import - Coldfusion - Advanced Techniques

Cleaning Up Excel Import - Coldfusion - Advanced Techniques

I am trying to read an Excel File (2000 Version, that apparently uses us-ascii) using cffile and am getting a lot of garbage I don't want. I want to strip out everything that isn't a tab, newline, comma or A-Z and 0-9. Is there a regular expression that I could use along with a REReplace function that would do the trick. I tried using [[:space:]] but all it did was remove the spaces....

  1. #1

    Default Cleaning Up Excel Import

    I am trying to read an Excel File (2000 Version, that apparently uses us-ascii)
    using cffile and am getting a lot of garbage I don't want. I want to strip out
    everything that isn't a tab, newline, comma or A-Z and 0-9. Is there a regular
    expression that I could use along with a REReplace function that would do the
    trick. I tried using [[:space:]] but all it did was remove the spaces.

    Steve Guest

  2. #2

    Default Re: Cleaning Up Excel Import

    Excel files are binary, so trying to read it with cffile is probably not what you want do here. There are plenty of other methods for reading or importing xls files. What exactly is your goal?
    mxstu Guest

  3. #3

    Default Re: Cleaning Up Excel Import

    I was trying to read the data as if it were a tab delimited file. There are
    seven columnns of data which I was trubng to loop through. It would work as a
    tab-delimited file, but once saved as an Excel file, obviously the file becomes
    unreadable by cffile.

    Steve Guest

  4. #4

    Default Re: Cleaning Up Excel Import

    Without knowing much about your process (database type, is this a one time
    event or recurring, etc), some possiblities are:

    CFQUERY/ODBC
    You can typically read data from an excel file, into a query object, using an
    cfquery + odbc. The exact syntax may vary depending on what type of datasource
    you're using (ie. excel dsn, ms access dsn, sql server dsn, etc). Once the
    data is in a query object, you can loop through it as needed.

    Java
    There are also a number of java options that allow you to read an excel file,
    such as http://cfregex.com/cfcomet/excel is not really the best method for a
    server environment.


    NOTE: If you're trying to import the data into an MS database, there may be a
    few other alternatives (such as performing the import within a CFQUERY, or
    using DTS)

    mxstu Guest

  5. #5

    Default Re: Cleaning Up Excel Import

    Sorry for not responding sooner. It looks like an error occurred on your last
    post and I did not get a notification.

    Since I don't know what database you're trying to import the data into, there
    are probably better ways to do this. However, one option is to set up an Excel
    DSN and use something like this:

    <!--- where "Name" and "NickName" are columns in "Sheet1" --->
    <cfquery name="getData" datasource="yourExcelDSN">
    SELECT Name, NickName
    FROM [Sheet1$]
    </cfquery>

    ... if you are using an Access DSN, you don't need to create an Excel DSN....

    <cfquery name="getData" datasource="yourAccessDSN">
    SELECT ID, Name
    FROM [Sheet1$]
    IN 'c:\someDirec\someExcelFile.xls' 'EXCEL 8.0;'
    </cfquery>



    mxstu Guest

Similar Threads

  1. import from excel
    By emmim44 in forum Coldfusion Database Access
    Replies: 6
    Last Post: August 22nd, 12:43 PM
  2. Import excel
    By Kronin555 in forum Coldfusion - Advanced Techniques
    Replies: 2
    Last Post: August 16th, 12:13 PM
  3. Exporting and Import Excel
    By Kling in forum Coldfusion - Advanced Techniques
    Replies: 3
    Last Post: July 21st, 04:39 PM
  4. import excel
    By R.Kisoenpersad in forum Microsoft Access
    Replies: 1
    Last Post: July 8th, 09:51 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
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139