alternative to Excel for database manipulation
Currently, we use Excel to copy / paste / drag / drop website page content data around, getting all the pertinent data for a webpage (url, title, product specs, header, category, sub-category, etc etc) into a spreadsheet that has multiple tabs,. After that file is done, we upload it to our MySQL database via a homegrown uploader tool.
This process has worked fine for a long time, but we are now re-designing our website to be mush more fluid, with more categories and product associations. This will necessitate a new design for our Excel files, due to the complexity of the data relationships now (much more complex than before). Nobody in house can get the Excel file to do what we need... it will take hiring an outside contractor to create and then maintain it.
I do not want to go that route.
I see this as an opportunity to move away from Excel into a much better data management tool, but I have no idea where to start looking while factoring in how we have done it in the past... users will still need to be able to drag / drop / copy / paste, create formulas (basic ones like stripping whitespaces or copying data from one field to another in bulk).
I know there are many GUI's out there, but I wanted to ask you experts your opinion, now that you have the backstory, on which tool I should try out to replace Excel as our data management system. Btw, we already have and use Access (old version, 2003) as the front end of our order management system, so I'm well versed in it, but working directly in the tables is not going to be good enough as far as data manipulation compared to Excel, so I'm looking for a different solution.
Thank you very much in advance for your input. If I could make this question worth 10,000 points I would, because this is crucial to our company and we can't move forward until I have decided which way to go on this (stick with Excel or change).
This process has worked fine for a long time, but we are now re-designing our website to be mush more fluid, with more categories and product associations. This will necessitate a new design for our Excel files, due to the complexity of the data relationships now (much more complex than before). Nobody in house can get the Excel file to do what we need... it will take hiring an outside contractor to create and then maintain it.
I do not want to go that route.
I see this as an opportunity to move away from Excel into a much better data management tool, but I have no idea where to start looking while factoring in how we have done it in the past... users will still need to be able to drag / drop / copy / paste, create formulas (basic ones like stripping whitespaces or copying data from one field to another in bulk).
I know there are many GUI's out there, but I wanted to ask you experts your opinion, now that you have the backstory, on which tool I should try out to replace Excel as our data management system. Btw, we already have and use Access (old version, 2003) as the front end of our order management system, so I'm well versed in it, but working directly in the tables is not going to be good enough as far as data manipulation compared to Excel, so I'm looking for a different solution.
Thank you very much in advance for your input. If I could make this question worth 10,000 points I would, because this is crucial to our company and we can't move forward until I have decided which way to go on this (stick with Excel or change).
Robert Geissler
You're talking about database management basically, and you need to learn SQL, or hire someone who knows SQL to help you accomplish what you're trying to do.
ASKER
I'm talking about getting text data into a database on a large scale, a tool so a user can move data around without knowing SQL. Our page content team is just that, content experts... they were not hired to be SQL experts.
It might help if you could expand a little on how you're using Excel. Your explanation is still a little abstract. Could you send sample files (before and after snapshots)?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Almost all dynamically updated webpages do have their contents in the database. You may buy this content from the owners directly and your copy/pasting team can do some real work instead... This way will also solve possible copyright problems.
ASKER
? Not sure what you mean, but all of our content is written in house or supplied by the manufacturer of the product of theirs that we are selling.
Right. That seemed clear to me in your original post.
Can you provide a before and after Excel example?
Can you provide a before and after Excel example?
ASKER
Working on an example, will post asap...
ASKER
Sample file attached. There are many tabs / sheets, the last two being how the tabs are used and guidelines. I left those last two tabs in to give a better picture of how all this ties together and what a mess it already is. As I said before, it works, but I feel we're working in the stone age at this point using Excel for all this.
Btw, once the Excel file has all the data correct, they copy / paste / into the corresponding MySQL tables. That's archaic too.
MasterSpreadsheetCopy.xlsx
Btw, once the Excel file has all the data correct, they copy / paste / into the corresponding MySQL tables. That's archaic too.
MasterSpreadsheetCopy.xlsx
OK, now I understand. Sorry.
After you describe your data then we will offer the right Content Management System (CMS) which can use one database for both purposes - product data storage and web content creation. You'll enter data directly to the database via administrative interface of your web. No Excel any more if you wish. (Each CMS allows CSV data import/export obviously.)
After you describe your data then we will offer the right Content Management System (CMS) which can use one database for both purposes - product data storage and web content creation. You'll enter data directly to the database via administrative interface of your web. No Excel any more if you wish. (Each CMS allows CSV data import/export obviously.)
ASKER
How do you need me to further describe the data? Pretty much all the data types are in that file.
And you say "product data storage and web content creation"... the product data storage IS the web content. The Intros tab is the best example of "content" as in words, paragraphs, but all the tabs (except the tabs referencing the data relationships, but even that applies here) are essentially what comprises the "content" of a finished page.
And you say "product data storage and web content creation"... the product data storage IS the web content. The Intros tab is the best example of "content" as in words, paragraphs, but all the tabs (except the tabs referencing the data relationships, but even that applies here) are essentially what comprises the "content" of a finished page.
QMBB, you stated the following:
"...we already have and use Access (old version, 2003) as the front end of our order management system, so I'm well versed in it, but working directly in the tables is not going to be good enough as far as data manipulation compared to Excel, so I'm looking for a different solution."
Where exactly does Access fall short for you? For your purposes, what does Excel do that Access cannot do?
ASKER
Not so much that it falls short. It just wouldn't be much of an improvement over the current process, if any at all. Most of the data we receive comes from suppliers already in Excel sheets, so it's easier to just transfer from that file to another file of ours and mush it all around to get it where it needs to go.
The users are well versed in Excel but not necessarily Access... Access is just the frontend of our order management system but the average user has no idea what's going on behind the scenes. Again, even if they did I don't see how Access would get me where I want to go, which is a true CMS system that doesn't require a brain surgeon to operate. Most data-entry / content staff here are not required to know DB stuff... they enter data and write content.
Our current system, whether its using Excel or Access, is getting too complicated and needs to be replaced with a much more streamlined, GUI oriented, user friendly approach.
The users are well versed in Excel but not necessarily Access... Access is just the frontend of our order management system but the average user has no idea what's going on behind the scenes. Again, even if they did I don't see how Access would get me where I want to go, which is a true CMS system that doesn't require a brain surgeon to operate. Most data-entry / content staff here are not required to know DB stuff... they enter data and write content.
Our current system, whether its using Excel or Access, is getting too complicated and needs to be replaced with a much more streamlined, GUI oriented, user friendly approach.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Okay, thanks. How do I do that?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Any other input from anybody else?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.