www.webdeveloper.com
Results 1 to 10 of 10

Thread: Excel Database...

  1. #1
    Join Date
    Jan 2004
    Posts
    38

    Smile Excel Database...

    Very simple straight foward question...

    I have an excel spreadsheet (Excel 2002) and it cuts off the data after the 255th character. I realize that this is the character limit for excel 2002, but because of several reasons I won't bore you with I have to use this spreadsheet as my database.

    Does anyone have a suggestions how to still use this file and get my data to display completely?

    (I've tried using *.csv/*.txt files already as well as migrating to access using memo fields. No luck with either.)

    THANKS!

  2. #2
    Join Date
    Nov 2002
    Location
    Auburn, AL
    Posts
    9,222
    Do not use excel for data unless it is absolutly neccessary because the powers the be insist apon it, that is not what excel was designed for and it will do an abysmal job. You can copy excel to access... I do not see what the problem would be, just change the text feilds to memo in the design view. You can also use xml to store data, but if it is huge amounts of data this might cause problems as xml does not grab data in chunks like access so it will sneak up on you... they tend to grow rather fast. MSSQL server is the best if your host runs it... msde is tolderable. Why do you have to use excel?

  3. #3
    Join Date
    Jan 2004
    Posts
    38
    We are receiving a report out of peoplesoft containing class schedule information. Due to the length of some of the fields the only way we can export the data from peoplesoft is to a *.csv file. Unfortunately, Peoplesoft has some idiosyncrasies that cause problems when migrating to access using this file.

    I've tried several solutions, but none allow me to have the functionality that I must have in order to complete the application.

    A side note...can you connect to an excel file and an access database in the same ASP file? When I tried it told me the Microsoft Access Driver could not open my excel file, so I am guessing it took priority over the excel connect file and tried using that one driver...?
    Last edited by gccwebmaster; 03-03-2004 at 08:59 AM.

  4. #4
    Join Date
    Jan 2003
    Location
    Dundee, Scotland
    Posts
    1,367
    Hi,

    can you connect to an excel file and an access database in the same ASP file?
    Yes. This would be a solution to your problem. Stream the excel file into your ASP then write to database.
    Last edited by Ribeyed; 03-03-2004 at 09:51 AM.
    ----------------------------------

  5. #5
    Join Date
    Jan 2004
    Posts
    38
    When I tried connecting to both I could not get it working...excuse my ignorance, but what do you mean by stream the excel file into my ASP page? I am not following your wording....

  6. #6
    Join Date
    Jan 2004
    Posts
    38
    To give you a little more information...

    When I change my approach from using two different excel spreadsheets to using one spreadsheet and one from access I get the following error:

    Microsoft OLE DB Provider for ODBC Drivers error '80004005'

    [Microsoft][ODBC Microsoft Access Driver]General error Unable to open registry key 'Temporary (volatile) Jet DSN for process 0x444 Thread 0x64c DBC 0x2e337ac Jet'.

    Thanks for your help.

  7. #7
    Join Date
    Jan 2004
    Location
    Melbourne, Australia
    Posts
    5,298
    Just a note, you often receive that error when one or more of the following occurs:

    1: The database is active when the script is executed.
    2: The DB is password protected.
    3: You do not have file permissions set on the directory which the DB resides.

    Regards.

  8. #8
    Join Date
    Jan 2004
    Posts
    38
    I checked all of those options. Permissions are corrected, the database is not password protected, and it is not open anywhere. Strangely enough I still receive that error...

  9. #9
    Join Date
    Dec 2002
    Location
    St. Louis
    Posts
    55
    This would be simple to do in SQL Server. You should be able to obtain an Educational copy of SQL server for pretty cheap. If you can dump the file to a comma seperated file(CSV), then it would be easy to setup a nightly load into SQL Server through BCP.
    MY SERVER'S SETUP:
    Win NT 2000 Server(latest service pack)
    ASP/ADO/Javascript
    SQL Server 7.0

  10. #10
    Join Date
    Jan 2004
    Posts
    38
    Thanks, but unfortunately I don't have that option at this point...this application is nearing its deadline...

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

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



Recent Articles