Excel Database...

[gccwebmaster]

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!

[PeOfEo]

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?

[gccwebmaster]

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...?

[Ribeyed]

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.

[gccwebmaster]

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....

[gccwebmaster]

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.

[buntine]

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.

[gccwebmaster]

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...

[Will192]

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.

[gccwebmaster]

Thanks, but unfortunately I don't have that option at this point...this application is nearing its deadline...