SitePoint Sponsor

User Tag List

Results 1 to 23 of 23
  1. #1
    SitePoint Wizard creole's Avatar
    Join Date
    Oct 2000
    Location
    Nashvegas Baby!
    Posts
    7,845
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Upload Excel spreadsheet to Access DB?

    Hey Folks...

    I'm working on a project with one of my co-workers. She has a calendar of events in spreadsheet format that we want to put on our website. I'd like to know if it's possible to provide her an "upload file" button that would grab this spreadsheet and import it into our Access database. The sheet contains 5 fields with around 150 rows.

    Can someone provide code or a link to code or a tutorial? I know this has to be possible, I'm just not advanced enough in CF yet to know how to do it. I'm gonna look through my reference books too, but I want to tap all of sources for help.

    TIA
    Adobe Certified Coldfusion MX 7 Developer
    Adobe Certified Advanced Coldfusion MX Developer
    My Blog (new) | My Family | My Freelance | My Recipes

  2. #2
    SitePoint Addict -TheDarkEye-'s Avatar
    Join Date
    Feb 2001
    Location
    canada
    Posts
    286
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    why dont you just import it directly in access?

  3. #3
    Yugo full of anvils bronze trophy hillsy's Avatar
    Join Date
    May 2001
    Location
    :noitacoL
    Posts
    1,859
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Creole, I'm pretty sure that ColdFusion supports Excel files as datasources (I say "pretty sure" as I've never actually tried it myself )

    So you should be able to write a ColdFusion page that, when called, queries the appropriate values from the spreadsheet and drops them into your database.

    The spreadsheet would have to exist on the webserver of course. That's the downside.

    Another way to do it would be to export the spreadsheet as a CSV then import it through a form using some ColdFusion parsing routines to separate the values. This would be a bit trickier.

    Yet another way (the best, but also the hardest) would be to allow a file upload to the server, then dynamically map a DSN to it. You could then run queries to do the data import (as above). This one will only work on the full version of CF though.

    Hope that gives you some ideas. Let us know how you go.
    that's me!
    Now A Pom. And a Plone Nut
    Broccoli Martinez Airpark

  4. #4
    SitePoint Zealot easyrew's Avatar
    Join Date
    Nov 2001
    Location
    Milton Keynes, UK
    Posts
    186
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I guess the way to do it is allow the user to upload the file. Run your query directly on the Excel file using a DSN-less connection, and take the result of your query and insert it into Access. (easier said than done)

    I don't have any experience of using DSN-less connections, or running queries on Excel, but with CF5 there is a file called "DataDirect Connect ODBC Reference" (drive:\cfusion\bin\odbcref.pdf) . See page 133 for a great section which should contain all the information you need to build a connection string which will work with an Excel file.

    If you have any success, please post the answers here so others may benefit. Oh yeah - any further questions just ask.

    Regards
    Rich
    If a man stands alone in the forest
    and there's no woman around to hear him,
    is he still wrong?
    w: www.EasyRew.com

  5. #5
    SitePoint Wizard creole's Avatar
    Join Date
    Oct 2000
    Location
    Nashvegas Baby!
    Posts
    7,845
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    OK guys...

    Let me give you a little more detail.

    The original file will be an Excel spreadsheet. I would like to make this as easy as possible for the lady collecting the information. My options are:

    -= Get her to give me a CSV file, convert it into TABLEs and post it online.
    pros: Fast, simple
    cons: information will get old quickly, involves two people.

    -= Allow her to upload an excel spreadsheet with the data. Possibilities include slurping the data in Access or using the spreadsheet itself as the datasource.
    pros: fast (for her), simple (for her), logic could be written which would remove old events once their time has passed.
    cons: Transfer errors could corrupt spreadsheet, time consumiung to program (?)

    -= She converts excel spreadsheet into Access db then uploads db, overwriting previous version. Logic then uses new db as the datasource.
    pros: simple, efficient
    cons: ?

    What do you think guys? A I trying to make this too difficult? She has said that she doesn't mind doing a little of the work herself. Are there any possibilities that I might have left out?
    Adobe Certified Coldfusion MX 7 Developer
    Adobe Certified Advanced Coldfusion MX Developer
    My Blog (new) | My Family | My Freelance | My Recipes

  6. #6
    Your Lord and Master, Foamy gold trophy Hierophant's Avatar
    Join Date
    Aug 1999
    Location
    Lancaster, Ca. USA
    Posts
    12,305
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Run Microsoft Access...

    Create a new Database

    On the File Menu Click on "Get External Data" and choose Import.

    In the File Extension box, Select Microsoft Excel.

    Select Your File.

    Click Ok.

    Other than that you can assign a DSN to the EXCEL file and access it directly as the database through ODBC.
    Wayne Luke
    ------------


  7. #7
    SitePoint Wizard creole's Avatar
    Join Date
    Oct 2000
    Location
    Nashvegas Baby!
    Posts
    7,845
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yes, but I want to know if Cold Fusion offers a way of taking an Excel spreadsheet and slurping the data into Access programmtically. This is less a database issue than it is a programming (Cold Fusion) one.

    If you don't mind, I'd like to move this back into Cold Fusion.
    Adobe Certified Coldfusion MX 7 Developer
    Adobe Certified Advanced Coldfusion MX Developer
    My Blog (new) | My Family | My Freelance | My Recipes

  8. #8
    Yugo full of anvils bronze trophy hillsy's Avatar
    Join Date
    May 2001
    Location
    :noitacoL
    Posts
    1,859
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Why'd this get moved into databases?

    I think that the easiest option (based upon what you've said) is to allow the user access to one directory on your webserver. You can use NTFS permissions to tighten this down security-wise.

    Put the Excel spreadsheet in there and configure a ColdFusion datasource to point to it. If/when updates are made, the user just needs to upload the new file with the same name (or edit the old file directly) and you will have access to the new data through ColdFusion.

    Then you can either use the data directly in a page, or write some ColdFusion <cfquery> statements that:

    1) Grab the appropriate data from the spreadsheet using SELECT
    2) Insert it into your Access database using UPDATE

    You would need separate <cfquery> statements for each of these. They could both be in the one ColdFusion file, though I'd prefer to put them in individual files (I'm a sucker for modularity).

    I don't think it's too difficult. I'll have a bit of a play with Excel spreadsheets and ColdFusion later today (if I get time) and let you know of any traps etc.
    that's me!
    Now A Pom. And a Plone Nut
    Broccoli Martinez Airpark

  9. #9
    SitePoint Wizard creole's Avatar
    Join Date
    Oct 2000
    Location
    Nashvegas Baby!
    Posts
    7,845
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The information that we have is VERY simple. There are no relationships between them at this point. It's 5 columns and that's it.

    I think what I might do is just get her to put the information into Access from the start. Then she'll just need to upload the database.

    I will ask for information on how I can implement a "click here to upload a file" which would take the DB from her hard drive and put it in the correct directory.

    Can someone point me to some code that can help me with that?
    Adobe Certified Coldfusion MX 7 Developer
    Adobe Certified Advanced Coldfusion MX Developer
    My Blog (new) | My Family | My Freelance | My Recipes

  10. #10
    SitePoint Guru nagrom's Avatar
    Join Date
    Jul 2001
    Location
    Western CT, USA
    Posts
    803
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    why not make a simple web-based system with some forms for updating?

  11. #11
    SitePoint Wizard creole's Avatar
    Join Date
    Oct 2000
    Location
    Nashvegas Baby!
    Posts
    7,845
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The raw information is being pulled from a proprietary DB on our server. It gets extracted into CSV format. There's not TONS of data, but at least 150 columns. Why input all that by hand when I can just suck it into the DB.

    I will however, provide a basic add, edit, delete admin section so my co-worker won't have to upload a new DB everytime there's a small change.
    Adobe Certified Coldfusion MX 7 Developer
    Adobe Certified Advanced Coldfusion MX Developer
    My Blog (new) | My Family | My Freelance | My Recipes

  12. #12
    Yugo full of anvils bronze trophy hillsy's Avatar
    Join Date
    May 2001
    Location
    :noitacoL
    Posts
    1,859
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Creole,

    For a tutorial on uploading files (with sample code) have a look here
    that's me!
    Now A Pom. And a Plone Nut
    Broccoli Martinez Airpark

  13. #13
    SitePoint Wizard creole's Avatar
    Join Date
    Oct 2000
    Location
    Nashvegas Baby!
    Posts
    7,845
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)



    I found that one last night actually. That site has TONS of good tutorials. In fact, I emailed that link to myself at work. It's what I'll be working on first thing when I get in.
    Adobe Certified Coldfusion MX 7 Developer
    Adobe Certified Advanced Coldfusion MX Developer
    My Blog (new) | My Family | My Freelance | My Recipes

  14. #14
    SitePoint Guru nagrom's Avatar
    Join Date
    Jul 2001
    Location
    Western CT, USA
    Posts
    803
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Wow, that is a good site.
    Creole, I meant to get back to this last night, if its just one file, you don't need CF, just use the form in the first half of that tutorial. If the excel sheet is set up as a datasource on the server, and the form is used to overwrite the file, it should be work.

    To prevent collisions during the overwrite on the server though, you may want to pass the file to be uploaded to the CFFTP tag, with a CFLOCK around the cfftp tag...

    <cflock name="creoleUpload" type="exclusive" timeout="30">

    <cfftp ....>

    </cflock>

  15. #15
    SitePoint Wizard creole's Avatar
    Join Date
    Oct 2000
    Location
    Nashvegas Baby!
    Posts
    7,845
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    How do you mean collisions? You mean uploading the file while someone is accessing the DB?
    Adobe Certified Coldfusion MX 7 Developer
    Adobe Certified Advanced Coldfusion MX Developer
    My Blog (new) | My Family | My Freelance | My Recipes

  16. #16
    SitePoint Guru nagrom's Avatar
    Join Date
    Jul 2001
    Location
    Western CT, USA
    Posts
    803
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    right.
    I should rephrase... you should put a cflock around a cfftp tag that overwrites an existing file, or a cffile tag that overwrites an existing file.

  17. #17
    SitePoint Wizard creole's Avatar
    Join Date
    Oct 2000
    Location
    Nashvegas Baby!
    Posts
    7,845
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The CFFTP tag would go around the FORM that is uploading the file? I'll refere to this tomorrow when I have my reference materials in fromt mf me, but would you have a loose structure that you could type out or point me to?
    Adobe Certified Coldfusion MX 7 Developer
    Adobe Certified Advanced Coldfusion MX Developer
    My Blog (new) | My Family | My Freelance | My Recipes

  18. #18
    SitePoint Guru nagrom's Avatar
    Join Date
    Jul 2001
    Location
    Western CT, USA
    Posts
    803
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Sure, I'll put something up later, have to eat dinner and hose off my kids first.

  19. #19
    SitePoint Guru nagrom's Avatar
    Join Date
    Jul 2001
    Location
    Western CT, USA
    Posts
    803
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok creole, here ya go...

    to do it with cffile:

    make a form template:

    <form action="sendfile.cfm" method="post" enctype="multipart/form-data">
    <input type="file" name="uploadFile">
    <input type="submit" name="upload" value="upload">
    </form>

    sendfile.cfm template to receive form looks like this:
    <cflock name="creoleUpload" type="exclusive" timeout="30">

    <cffile action="upload"
    filefield="uploadFile"
    destination="d:\somedir\"
    nameconflict="overwrite">

    </cflock>

    all done!

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

    to do it with cfftp:
    if the file to be uploaded is in the same place all the time, you don't even need the form.

    <cflock name="creoleUpload" type="exclusive" timeout="30">

    <cfftp action="open"
    server="ftp.server.com"
    username="creole"
    password="pass"
    connection="creoleconnect">

    <cfftp action="PutFile"
    transfermode="binary"
    remotefile="d:\somedir\thefile.xls"
    localfile="c:\mydocuments\thefile.xls"
    connection="creoleconnect">

    <cfftp action="close" connection="creoleconnect">

    </cflock>

    not tested, but should point you the right way
    Last edited by nagrom; Feb 20, 2002 at 12:06.

  20. #20
    SitePoint Wizard creole's Avatar
    Join Date
    Oct 2000
    Location
    Nashvegas Baby!
    Posts
    7,845
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    GREAT!

    I don't have CF here at home, but I'm finishing up the admin section of the app and this will be part of that.

    I think I'll go for the first route as I don't know where the file will be all the time.
    Adobe Certified Coldfusion MX 7 Developer
    Adobe Certified Advanced Coldfusion MX Developer
    My Blog (new) | My Family | My Freelance | My Recipes

  21. #21
    SitePoint Guru nagrom's Avatar
    Join Date
    Jul 2001
    Location
    Western CT, USA
    Posts
    803
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    i was just building a pop window fora client to ftp files with, I forgot the connection="" attribute up there in the cfftp version

  22. #22
    SitePoint Wizard creole's Avatar
    Join Date
    Oct 2000
    Location
    Nashvegas Baby!
    Posts
    7,845
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks.
    Adobe Certified Coldfusion MX 7 Developer
    Adobe Certified Advanced Coldfusion MX Developer
    My Blog (new) | My Family | My Freelance | My Recipes

  23. #23
    SitePoint Guru nagrom's Avatar
    Join Date
    Jul 2001
    Location
    Western CT, USA
    Posts
    803
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    here's the revised ftp version. I'm using it in a clients admin section as a popup window.

    Notice I used the input type of 'file' without the enctype attribute of the form, to just pass the file name. This works in IE, not sure about other browsers.

    I found if you set the enctype in the form tag, the passed filename is a temp file name from the local machine (not very helpful).


    <html>
    <head>
    <style type="text/css">
    p,select,input {font-family : verdana, arial, sans-serif;
    font-size : 10px;
    }
    </style>

    </head>
    <body>

    <cfif isDefined("form.upload")>

    <!--- BEGIN FILE UPLOAD ROUTINE --->

    <cfset my_server="FTP_ADDRESS">
    <cfset my_username="FTP_LOGON">
    <cfset my_password="FTP_PASSWORD">
    <cfset my_connection="ANYOLDWORDWILLDO">

    <cflock name="UploadFile" type="exclusive" timeout="30">


    <cfftp action="open"
    server="#my_server#"
    username="#my_username#"
    password="#my_password#"
    connection="#my_connection#">

    <cfftp action="changedir"
    directory="#form.folder#"
    connection="#my_connection#">

    <cfftp action="PutFile"
    connection="#my_connection#"
    transfermode="binary"
    remotefile="#GetFileFromPath(form.uploadFile)#"
    localfile="#form.uploadFile#">

    <cfftp action="close"
    connection="#my_connection#">

    </cflock>

    <!--- END FILE UPLOAD ROUTINE --->

    <cfelse>


    <div style="width:230px; border-width : 1px; border : 2px; border-color : silver; border-style : solid; background-color : dedede; padding : 6px;">
    <p style="font-size:12px;"><b>Upload Files to the Website</b></p>

    <form action="jw_inncom_ftp.cfm" method="post">
    <p style="margin:0;">The file on your computer/p>
    <input type="file" name="uploadFile" style="width:220px;">
    <br>
    <p style="margin:0;">Upload to this folder on the site/p>
    <select name="folder">
    <option value="choose">choose a folder ----</option>
    <option value="d:\serverpath\folder\">http://www.sitepath.com/folder/</option>
    <option value="d:\serverpath\folder\">http://www.sitepath.com/folder/</option>
    </select>
    <br>
    <div style="width:230px;" align="center">
    <input type="submit" name="upload" value="click to upload file">
    </div>
    </form>

    </div>

    </cfif>

    </body>
    </html>


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
  •