Import Excel to Access when headers are verticle

[lahddah]

I am trying to import an excel spreadsheet into access. The problem is that spreadsheet is laid out as follows:

................net.........billed.......gross.....variance

fieldname1...1value1...1value2...1value3...1value4
fieldname2...2value1...2value2...2value3...2value4
fieldname3...3value1...3value2...3value3...3value4

(the '...' are just used for spacing in this post - not part of spreadsheet)


the 'variance' column contains a calculation. Can anyone tell me how to bring all of this into an access table?

Thanks, in advance!

 

[Rickster57]

Excel

Use the Docmd.TransferSpreadsheet command in Access to bring over your data.

 

[lahddah]

Thanks, Rickster57. I've searched through this forum and everywhere else I can find and I cannot find an example of the transferspreadsheet command needed to do what I need. Based on my example above, I think, basically, I need it to create a table with field names such as "fieldname1-net", "fieldname1-billed", "fieldname1-gross" and "fieldname1-var" and the same for "fieldname2-net"...and so on. Then I need the vaules transferred into their correct fields.

Maybe I'm totally misunderstanding the way transferspreadsheet works, but from what I've seen it looks like it just takes data from a spreadsheet and creates it, almost exactly as it is, in a table.

 

[Robert88]

I suppose you could always transpose the sheet into a format that is more readible by Access.

In Excel from help;

Transpose rows and columns
Data from the top row of the copy area appears in the left column of the paste area, and data from the left column appears in the top row.

Select the cells that you want to switch.
1. Click Copy .
2. Select the upper-left cell of the paste area. The paste area must be outside the copy area.
3. Click the arrow to the right of Paste and then click Transpose.

Good Luck

Robert88

 

[clerics]

take a look to this one ...may be it can help you a little more !
http://www.access-programmers.co.uk/forums/showthread.php?t=100394

 

[stephen81]

I suppose you could always transpose the sheet into a format that is more readible by Access.

I'm not sure you actually need to transpose your spreadsheet. Your data should import fine with very little changes. I think the confusion may be arising from your fieldname1, fieldname2 and fieldname3. These actually look as though they should be separate records (i.e. record1, record2, record3), not separate fields.
The field names are the net, billed, gross and whatever you decide to call the first column (I can't tell you what it should be cos I don't know what information is actually stored there i.e. invoice number or something).
As for the variance column, you shouldn't import that as it is calculated from the other fields. You can calculate it in access when you need it, otherwise you are storing duplicate information.

 

[lahddah]

Thanks, everyone. Stephen - you're correct - the 'fieldname1, 2, 3...' are to be records with each containing the 'fn1-net, fn1-gross, fn1-billed' fields. And, you're also right - the variance field will be calculated based on comparisons from the previous year's net/gross/billed info, but I thought I had to bring it in, too, so that the number of fields in the DB matched the number in excel. I was going to add the calculations later on. The first column would be something like 'inv-number' and unique.

I tried transposing it and playing with it a bit, but it's not going to work that way. I am trying to take about 40+ spreadsheets per year over the last 3-5 years and import them into access so that entry and report running would, hopefully, be simpler. So, whatever I find that works, I'd like it to be as simple as possible, obviously.

Thanks, again!

 

[Robert88]

Hi lahddah,

OK, I can see now to transpose is a bad idea.

I suppose you just need to give the column in your excel example above a name, the column with fieldname1, fieldname2, fieldname3.......

So table should have the following fields

fldfieldname
fldnet
fldbilled
fldgross
fldvariance

I suppose in your excel example above you are missing a name for the column fieldname1, fieldname2, fieldname3......, which I have named in the table structure above fldfieldname

Then all records in your example above should go into this table unless calulated then they are not required as you will calculate them later, as you have indicated.

Good Luck with it, I hope this helps.

Robert88

 

[lahddah]

Thanks, Robert88. I think you get what I'm trying to do....but still not sure how to move the fld* values from excel into the fld* fields in access.

 

[Robert88]

Hi Lahddah,

What is your intention with the excel table in Access if it is read in?

After looking at your excel sheet, it would appear that your filednames* form part of your record, and are not headers?

Is it possible to paste two lines as a sample from your sheet, rather than having general info, even if it is changed if it is sensitive info?

Robert88

 

[lahddah]

Sure. Here you go - the VAR is variance between last year (far left) and this year (middle) - actually, didn't know how to insert it here, so attached it.