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.)
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?
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.
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)
SQL Server 7.0