IT - a Challenge or an impossibility?

[Speicher]

I have been asked if I can take a Spreadsheet and "transfer" into a Database.

I have used spreadsheets, and databases, and set up fairly straight forward ones. The current one has about seven colunms for the stock situation, buying and selling prices etc of approx 200 items.

The problem arises because the spread sheet now needs to be arranged in alphabetical order according to the description of the item. An attempt was made to do this, but the amendment to alphabetical order did not retain the correct Identification number with the correct item. (which then had to be manually corrected) .

It is possible to do this, or is someone going to have to start again inputting the data as a database? If so, that someone might well be me. (Unpaid ).

I would add that there are now about three back-up copies of the corrected spreadsheet, so that any amendments can be tried out without jeopardising the Correct One. My colleague successfully transfered one row of the spreadsheet into a database, but subsequent rows just overwrote the first row. Any suggestions would be gratefully received.

Please feel free to ask any details that I may have missed out. I know the weekend is here, but would like some idea by next Thursday if some very very kind person, is very kind and helps out.

 

[fossyant]

So long as the spreadsheet is tabular, no silly gaps etc then yes -Access will import it.

If you did a sort and it messed up the reference number then there is a gap in your spreadsheet - e.g. a column with no data or headder in the middle - this will need sorting before you import !

 

[Speicher]

Speicher - which database are you using? Access usually plays very nicely with Excel and others like Oracle have pretty good ways of getting structured/tabular data in.

I am fairly certain it is Access and Excel. Yes, we had hoped they would talk to each other.

 

[peanut]

why can't you simply highlight all the rows you wish to sort by highlightling the numbers down the left. Then Data...Sort and select which columns you wish to sort by and how (recommend one at a time) then how you want it presented

 

[Speicher]

That is how I thought we might be able to do it. But my colleague was reluctant to experiment with it, and then muck it up again. It may be as Fossyant says that there is a gap in the spreadsheet, she may have left a column blank in case she needed to add another column. This may have been (inadvertently) a mistake.

Now that we have so many back up copies we can risk messing one up, in trying to sort it out.

Thank you all for your suggestions so far, sound like it is possible. I am logging off now to get something to eat.

 

[Speicher]

Speicher, I'm sure you'll get lots of good advice here, but if you're not sorted by Monday please feel free to send me a PM and I can most likely get it solved for you. I have a lot of experience with this kind of thing. I'm off out now though!

Cheers
Mort

That is extremely kind of you, Mort. The office is open on Saturdays, but then closed until Wednesdays, so at least we have time to sort this out.
Thank you

 

[Speicher]

A special thank you to all of you for your advice. I will talk to my colleague tomorrow, and see if Fossyant is right about the gap. I think that may be the case, as she would not have realised the significance of a gap that she left in case she needed another column.

I think she may also have tried to sort it into alphabetical order by highlighting the column of the description, then asking it to sort. Also having got in a big muddle, and had to put it right, it is very easy to get more confused and make more mistakes.

I think, as Peanut suggests, we must try highlighting the numbers on the left, and then asking it to sort the descriptive column alphabetically. Or do we highlight the whole spreadsheet? It sounds like we can retain it as a Spreadsheet, rather than convert it to a database.

We will try those suggestions, at least now we have back up copies of the correct version. I think we have until at least next Thursday to sort it out, as that is when the Resource Centre is next open for "selling" things. It is a Resource Exchange Centre for reusing "scrap" materials in all lots of art and craft projects. The items sold are like paintbrushes, glue, paint, etc that people might need to complete those projects in schools etc, so that they can get everything in one place. I am a (unpaid) Volunteer working in partnership with the Resource Centre, and my colleague has asked for my help. You may well have saved me two days in front of a computer, as her skills are needed on more "Artistic" projects. I will let you know what happens.

I did not want to just experiment, and waste time and patience, if it was not feasible. Thank you for your help.

 

[Sh4rkyBloke]

The problem arises because the spread sheet now needs to be arranged in alphabetical order according to the description of the item. An attempt was made to do this, but the amendment to alphabetical order did not retain the correct Identification number with the correct item. (which then had to be manually corrected) .

As long as a single column of data is not selected then using Data - Sort in Excel and then choosing the column to sort by should take care of it. If it pops up a dialogue box asking if you want to extend the selection then choose yes and it should then encompass all columns that have data. Alternatively just click the top left hand box of the spreadsheet (left of the A in Column A and above the 1 of row 1) to select the whole spreadsheet, then choose Data - Sort and again choose the relevant column to sort by.

This should be fine.

If it's not then something is wrong with the data (as Fossyant says) and this would also cause a problem when trying to import it into Access too I would suspect.

Hope this helps.

 

[fossyant]

Any gap will fcuk it up..me is an excel god.........no seriously, I know it pretty well.....

If you have an issue, PM me and then email the file to me - you don't need to use a database at all - excel is great, once you know it's faults.......

You can do loads of stuff with excel....I get in trouble at work exporting the SAP system to excel cos I can do more with the data... heh.... heh.... heh.....

 

[Speicher]

I am going to print off this thread (now that I know I can get a printable version) and talk with my colleague about the suggestions everyone has kindly made. I do very much appreciate all your efforts, especially on a Friday night, when you might be trying to relax after a busy week.

If I could format a FairTrade chocolate bar for transmission through the Internet, I would send you all one.

 

[Peter]

If the file is in Excel, highlight the header rows by clicking on the corresponding number on the left hand side. Now click on DATA on the toolbar, then FILTER and then AUTOFILTER. This places a fliter on each column. Click on the down arrow on the column you want to sort and select either ascending or descending (you may need to click several times until this option appears).
Hope this helps.

 

[Speicher]

I spoke to my colleague today. She was extremely pleased (and that is an understatement) to find out that we probably will not need to translate the spreadsheet into a database.

Fortunately she has planned ahead for when it is needed in alphabetical order, so we can work on it together next week. Thank you all for your help, I will let you know when it is a success.

 

[Speicher]

My colleague and I managed to put the spreadsheet in the order she wanted it, thanks to all the helpful instructions that I received.

We now have a "practice" version of the spreadsheet, so that we can try things out, and not muddle up the correct version, the correct version is also backed up.

I do have another question tho'. If the spreadsheet is very long, there is a way of "retaining" the headings so that it moves down the screen, as you scroll down, IIRC. However, I could not find it - any ideas? Thank you.

 

[Sh4rkyBloke]

Yes - simply place the cursor in cell B2 (or any cell under the row you wish to keep at the top) and click on Window - Freeze Panes. This puts a horizontal line above the row you are in, and also a vertical one to the left of the cell chosen - any scrolling then keeps stuff above and to the left of that line stationary.

Hope this helps.

EDIT - to remove the lines click Window - Unfreeze panes... this may be useful if you get the lines in the wrong place, for example.

 

[Sh4rkyBloke]

Excel is, as Fossyant says, an excellent tool which is capable (given enough prodding and poking) of doing pretty much whatever you want. However, a database will leave it standing where you have more complex data which needs to be related to other data and manipulated...

For example: a simple task of keeping student names and addresses along with what course they are studying and where the course is being held will be infinitely easier to keep in a database than in a spreadsheet. It's not impossible to keep it in the latter, but any alterations (change of address for example) can get tricky as you either need to repeat the students address for each time they choose a course (and thus would have to update each and every instance of the address), or you have to link a student with an address somehow...