Converting spreadsheet to Access

[Jayee]

Hi,

I have an Excel spreadsheet where the field names are down the side rather than the more usual along the top.

For working in Excel I find this fomat much, much easier which is why I did it.

But it's causing me problems with importing it to Access because it doesn't understand my layout.

How do I get it to do so ? If not, perhaps there's a way Excel can reverse the orientation of my spreadsheet automatically before I do the import ?

I really hope you can help me, because it'd take me hours to reformat my Excel spreadsheet to be the opposite way around (and that's assuming it didn't get all messed up in the process!).

Regards,
Jay/UK

 

[PHV]

You may consider the Transpose worksheet function.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[ccv]

I don’t know of a way of doing this in Access but the
Edit/Paste Special/Transpose will do it in Excel. The only problem is that you can only do one row (or column) at a time. The following uses two spreadsheets – The source file (wsSrc) and the destination file (wsDest). Here’s an example. Hope it helps.

Set CopyRange = wsSrc.UsedRange.Columns
            
        For j = CopyRange.Columns.Count To 1 Step -1
        
            With wsSrc
                .Range(.Cells(1, j), .Cells(31, j)).Copy
            End With

            With wsDest
             	DestLastRow = Cells(65536, 1).End(xlUp).Row
.Range("A" & DestLastRow + 1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
            End With

        Next j

 

[pbrodsky]

Actually, you can paste-special/transpose and rectangular range. Just copy your data and paste it on a new sheet. It should work.

 

[BobRodes]

Ever since Lotus 3.0...

 

[Jayee]

Hi Pbrodsky,

I've just tried to do this, and this is the error message I'm getting.

The information cannot be pasted because the Copy and Paste area are not the same size and shape. Try one of the following:

Select a single cell and select Paste (tried that, but I don't understand, because I want the Paste Special command)

Select a rectangle that's the same size and shape, and then Paste (not sure how to do this).

Can you help me ?

BTW, I still can't understand why Access isn't able to understand a spreadsheet when the fields are along the side rather than the top.

I expected it to come up with an option saying "The fields are in in the "rows" or "columns" and I would choose and then it would import the data in the right way.

Regards,
Jay/UK

 

[pbrodsky]

Just select a single cell (the one you want as the top-left) before doing the paste special. If you select the entire area, you will need the number of rows that started out as columns and the number of columns that began as rows.

 

[BobRodes]

<BTW, I still can't understand why Access isn't able to understand a spreadsheet when the fields are along the side rather than the top.

That's kind of a philosophical thing: the idea would be that if you try to be all things to all people, you wind up being useless. Spreadsheets are built to manipulate stuff first, and store stuff second. Databases are the reverse. Data import routines are based on the idea that the first row is the field name, and the next rows are the data. Databases don't generally go to the trouble of supporting preformatting into this format. Quite rightly, too, since there's always some other program (Excel, for example) that does it better.

HTH

Bob

 

[Jayee]

Hi,

Would you believe it !

Just after I posted that last message asking for help I did it again and it worked ! Mind you, I have no idea what I did different that last time, because I was doing exactly what you suggested (just having one cell selected at the top of the sheet).

Anyway, I now have my data in the correct format and it's now successfully been imported into Access, which is exactly what I wanted!

Thanks for your help.

Regards,
Jay/UK

 

[Jayee]

Hi again,

I've come "unstuck" again so I need your assistance please.

My fleet table has run out of spare fields as the total allowed is 255 and I need more (lots more, in fact). Each field represents a separate piece of data about a vehicle in my fleet - some of which is basic data such as tax due and MOT due, but others are financial, such as the date, amount and invoice number for each of the 36 payments to the leasing company and to the company for the maintenance contract.

Can you help me ?

Regards,
Jay/UK

 

[PHV]

Have a look here:

http://r937.com/relational.html

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[BobRodes]

What PHV is suggesting is very important, Jayee. I strongly second the suggestion.

Bob

 

[Jayee]

Hi,

The page explaining about relational databases is very, very interesting.

But it's hard for me to see how it can help me with my current database requirement, which is really quite simple - I want to store ALL the data about each vehicle in my fleet, and be able to create customised reports using Queries which extract the data I want.

Can you help me further ?

Regards,
Jay/UK

 

[MajP]

Jay,
If you are not seeing the relevance, you probably need to reread the article, also you can look at the Northwind sample data base that comes with Access. This has an example of orders, and payments.
You want to store all the data about your fleet, but it is highly unlikely that it makes sense to store it all in one table. You will at a minimum have a vehicle table has fields that are common to describing vehicles. You will also have an invoice table that has information about invoices, and a pointer (foriegn key) back to the vehicle. Each vehicle has many invoices, which immediately suggests that this should not all be in a flat table.

Hope that helps.

 

[BobRodes]

Look for one to many relationships in your stuff. This is 95% of relational database theory, and is extremely important. MajP is absolutely right.

Bob