importing spreadsheet that doesn't match table

[rickscr]

I need to get the data from Excel spreadsheets into an existing Access database table. The problem is that the fields don't match.

I can't really explain how it needs to work except to show an example (see attached).

This is a simplified representation. In reality, there are about 20 fields in the Excel files and 15 in the Access table. Data is collected for multiple sites. Each site has it's own spreadsheet for a specific time period. The 'Type' field corresponds to the spreadsheet 'Data' field.

I've been thinking that some sort of query could do it, but I don't know where to start.

[ajetrumpet]

the pictures really don't help. those look like you're own creations. are they at all related?? for instance, the second pic shows a TYPE, but the first one doesn't.

If this is a correct rep of the problem, I would manipulate the data in whatever program you're more comfortable with. More than likely, you're gonna be looking at the vba editor to some degree for code writing purposes.

[rickscr]

The images are my own creations to illustrate the problem simply. Hopefully these are less confusing.

This is what the Excel input files look like for Site A:


This is how the Access table needs to work:


Basically, the variables need to go top to bottom instead of left to right, but they still need to match up to the correct date.

[ajetrumpet]

Rick,

Have you experimented with the transpose function in the paste special menu of Excel??

[rickscr]

I tried transpose, but it's not what I need.

Here is some basic code I whipped up in Excel that does what I want. Is it possible to do something like this with Access VBA?

Private Sub CommandButton1_Click()

'count rows that have data
Dim Rcount As Integer
Rcount = Application.CountA(Range("A:A"))

Dim CopyRow As Integer
Dim PasteRow As Integer
Dim Variable As String
Dim VariableCol As Integer

PasteRow = 2

'loop through columns and row, copying data down
For VariableCol = 2 To 4
    For CopyRow = 2 To Rcount
        Variable = Cells(1, VariableCol)
        
        Cells(PasteRow, 7) = Cells(CopyRow, 1) 'Date
        Cells(PasteRow, 8) = Cells(CopyRow, VariableCol) 'Datavalue
        Cells(PasteRow, 9) = Variable 'Variable name
        
        PasteRow = PasteRow + 1
    Next CopyRow
Next VariableCol

End Sub

[ajetrumpet]

that's exactly what I was saying earlier, Rick. You already wrote the code. Why won't that work for you?? Just run it and then do your import. Or is that not sufficient enough?

YES, you can do it in access. With recordset code of course. Anyone can probably write it, but why write it if it's not necessary. Right?

[rickscr]

I would really prefer to do everything in Access. I have thousands of spreadsheets to import, each with 20+ variables. I will also have minimally trained users importing 25 similar spreadsheets every week this summer.

Ideally, they would click one button, select the file, choose the site name from a combobox, and be done.

Of course, if I can't figure it out, I'll go with plan B--using the code I created in Excel--which will make things much more tedious.

[rickscr]

Here's some thoughts:
Can I use docmd.TransferSpreadsheet and run a series of imports?
Each import in the loop would grab a different range.

For example:
Loop1 would import the dates and variable 1 values.
Loop2 would import the dates and variable 2 values.
Loop3 would import the dates and variable 3 values.

I think TransferSpreadsheet only works with contiguous ranges, but maybe there's some way to get around this?


Alternately, I import the spreadsheet exactly as it is into a temporary table. Then I could run an append query that grabbed the date, variable values, and got the site from a form. The downside is that I'd need to create about 20 queries; one for each variable.
I've attached an example of this method.

[ajetrumpet]

Rick,

your test db didn't make much sense to me. the tables do, but the append query I don't think is relevant to this scenario of yours.

My two ideas would be:

• run a function that manipulates the data via recordset objects
• use an ADO connection between Access and Excel to iterate and copy data via code directly

of course, there is always the 'copyfromrecordset' option available from Excel code, which I believe applies to named ranges as well as sheet names. I know for a fact that you can query named ranges from an Excel sheet when you're using ADO.

[rickscr]

Pardon my ignorance, but what are recordset objects and ADO?

[ajetrumpet]

Rick,

If you want to upload what you've got, you might get lucky. Someone might just give you a template to go by.

I could even do it for you, but I can't when I don't have time. If you do upload the file, I'm sure I could get some code back to you within the next 2 days or so, but of course that's not a promise. It's up to you.

Recordsets are objects in VBA and ADO is a VBA programming library/DLL.

[rickscr]

Well, here's what I have so far. It seems to work alright using a series of append queries. It may not be elegant, but might be the easiest solution.

I really appreciate your help with this. There's not really any rush for getting this done, but if you have the time and want to write some code, that would be awesome!

[rickscr]

I think I've solved it. I figured out how to run SQL in VBA. Now I can modify the SQL to accept variables and use just one query.

[ajetrumpet]

I think I've solved it. I figured out how to run SQL in VBA. Now I can modify the SQL to accept variables and use just one query.

Great! Post up again if you need assistance.