Importing array/matrix data into Access

[rowbro]

Hi all,

I have a massive matrix of pricing data (a sample below) in excel that I would like to import into and use in Access. The horizontal headers are "tickers" and the vertical are dates.

[TABLE="width: 348"]
<tbody>[TR]
[TD]Date
[/TD]
[TD]ACL SJ Equity
[/TD]
[TD]BAT SJ Equity
[/TD]
[TD]CSB SJ Equity
[/TD]
[TD]GRF SJ Equity
[/TD]
[/TR]
[TR]
[TD]1/2/2014
[/TD]
[TD]3764
[/TD]
[TD]658
[/TD]
[TD]12315
[/TD]
[TD]654
[/TD]
[/TR]
[TR]
[TD]1/3/2014
[/TD]
[TD]3771
[/TD]
[TD]660
[/TD]
[TD]12495
[/TD]
[TD]660
[/TD]
[/TR]
[TR]
[TD]1/6/2014
[/TD]
[TD]3800
[/TD]
[TD]680
[/TD]
[TD]12474
[/TD]
[TD]660
[/TD]
[/TR]
[TR]
[TD]1/7/2014
[/TD]
[TD]3860
[/TD]
[TD]680
[/TD]
[TD]12430
[/TD]
[TD]660
[/TD]
[/TR]
[TR]
[TD]1/8/2014
[/TD]
[TD]3826
[/TD]
[TD]707
[/TD]
[TD]12460
[/TD]
[TD]665
[/TD]
[/TR]
[TR]
[TD]1/9/2014
[/TD]
[TD]3788
[/TD]
[TD]744
[/TD]
[TD]12515
[/TD]
[TD]665
[/TD]
[/TR]
[TR]
[TD]1/10/2014
[/TD]
[TD]3579
[/TD]
[TD]745
[/TD]
[TD]12350
[/TD]
[TD]670
[/TD]
[/TR]
[TR]
[TD]1/13/2014
[/TD]
[TD]3634
[/TD]
[TD]738.8
[/TD]
[TD]12574
[/TD]
[TD]663
[/TD]
[/TR]
</tbody>[/TABLE]


I am not sure how to use the data in this form, and I assume it is better to have the tickers as IDs (so they can be linked to another table with more ticker data), as below:

[TABLE="width: 239"]
<tbody>[TR]
[TD]Ticker ID
[/TD]
[TD]Ticker
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]ACL SJ Equity
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]BAT SJ Equity
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]CSB SJ Equity
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]GRF SJ Equity
[/TD]
[/TR]
</tbody>[/TABLE]

Meaning the other table could be as follows:

[TABLE="width: 241"]
<tbody>[TR]
[TD]Date
[/TD]
[TD]ACL SJ Equity
[/TD]
[TD]Ticker ID
[/TD]
[/TR]
[TR]
[TD]1/2/2014
[/TD]
[TD]3764
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]1/3/2014
[/TD]
[TD]3771
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]1/6/2014
[/TD]
[TD]3800
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]1/7/2014
[/TD]
[TD]3860
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]1/8/2014
[/TD]
[TD]3826
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]1/9/2014
[/TD]
[TD]3788
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]1/10/2014
[/TD]
[TD]3579
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]1/13/2014
[/TD]
[TD]3634
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]1/2/2014
[/TD]
[TD]658
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]1/3/2014
[/TD]
[TD]660
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]1/6/2014
[/TD]
[TD]680
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]1/7/2014
[/TD]
[TD]680
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]1/8/2014
[/TD]
[TD]707
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]1/9/2014
[/TD]
[TD]744
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]1/10/2014
[/TD]
[TD]745
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]1/13/2014
[/TD]
[TD]738.8
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]1/2/2014
[/TD]
[TD]12315
[/TD]
[TD]3
[/TD]
[/TR]
[TR]
[TD]1/3/2014
[/TD]
[TD]12495
[/TD]
[TD]3
[/TD]
[/TR]
[TR]
[TD]1/6/2014
[/TD]
[TD]12474
[/TD]
[TD]3
[/TD]
[/TR]
[TR]
[TD]1/7/2014
[/TD]
[TD]12430
[/TD]
[TD]3
[/TD]
[/TR]
[TR]
[TD]1/8/2014
[/TD]
[TD]12460
[/TD]
[TD]3
[/TD]
[/TR]
[TR]
[TD]1/9/2014
[/TD]
[TD]12515
[/TD]
[TD]3
[/TD]
[/TR]
[TR]
[TD]1/10/2014
[/TD]
[TD]12350
[/TD]
[TD]3
[/TD]
[/TR]
[TR]
[TD]1/13/2014
[/TD]
[TD]12574
[/TD]
[TD]3
[/TD]
[/TR]
[TR]
[TD]1/2/2014
[/TD]
[TD]654
[/TD]
[TD]4
[/TD]
[/TR]
[TR]
[TD]1/3/2014
[/TD]
[TD]660
[/TD]
[TD]4
[/TD]
[/TR]
[TR]
[TD]1/6/2014
[/TD]
[TD]660
[/TD]
[TD]4
[/TD]
[/TR]
[TR]
[TD]1/7/2014
[/TD]
[TD]660
[/TD]
[TD]4
[/TD]
[/TR]
[TR]
[TD]1/8/2014
[/TD]
[TD]665
[/TD]
[TD]4
[/TD]
[/TR]
[TR]
[TD]1/9/2014
[/TD]
[TD]665
[/TD]
[TD]4
[/TD]
[/TR]
[TR]
[TD]1/10/2014
[/TD]
[TD]670
[/TD]
[TD]4
[/TD]
[/TR]
[TR]
[TD]1/13/2014
[/TD]
[TD]663
[/TD]
[TD]4
[/TD]
[/TR]
</tbody>[/TABLE]

Given the size of this matrix, however, I don't want to have to rearrange it manually. Is there a way to rearrange the data such using SQL (or any other means)?

Thanks

 

[ranman256]

yes Access would want it stored thus:
[TABLE="width: 304"]
<tbody>[TR]
[TD]Date[/TD]
[TD]equity[/TD]
[TD]ticker[/TD]
[TD]TickerID[/TD]
[/TR]
[TR]
[TD="align: right"]1/2/2014[/TD]
[TD="align: right"]3764[/TD]
[TD]ACL SJ[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]1/3/2014[/TD]
[TD="align: right"]3771[/TD]
[TD]ACL SJ[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]1/6/2014[/TD]
[TD="align: right"]3800[/TD]
[TD]ACL SJ[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]1/7/2014[/TD]
[TD="align: right"]3860[/TD]
[TD]ACL SJ[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]1/8/2014[/TD]
[TD="align: right"]3826[/TD]
[TD]ACL SJ[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]1/9/2014[/TD]
[TD="align: right"]3788[/TD]
[TD]ACL SJ[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]1/10/2014[/TD]
[TD="align: right"]3579[/TD]
[TD]ACL SJ[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]1/13/2014[/TD]
[TD="align: right"]3634[/TD]
[TD]ACL SJ[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]1/2/2014[/TD]
[TD="align: right"]12315[/TD]
[TD]CSB SJ[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="align: right"]1/3/2014[/TD]
[TD="align: right"]12495[/TD]
[TD]CSB SJ[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="align: right"]1/6/2014[/TD]
[TD="align: right"]12474[/TD]
[TD]CSB SJ[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="align: right"]1/7/2014[/TD]
[TD="align: right"]12430[/TD]
[TD]CSB SJ[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="align: right"]1/8/2014[/TD]
[TD="align: right"]12460[/TD]
[TD]CSB SJ[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="align: right"]1/9/2014[/TD]
[TD="align: right"]12515[/TD]
[TD]CSB SJ[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="align: right"]1/10/2014[/TD]
[TD="align: right"]12350[/TD]
[TD]CSB SJ[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="align: right"]1/13/2014[/TD]
[TD="align: right"]12574[/TD]
[TD]CSB SJ[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="align: right"]1/2/2014[/TD]
[TD="align: right"]654[/TD]
[TD]GRF SJ[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD="align: right"]1/3/2014[/TD]
[TD="align: right"]660[/TD]
[TD]GRF SJ[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD="align: right"]1/6/2014[/TD]
[TD="align: right"]660[/TD]
[TD]GRF SJ[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD="align: right"]1/7/2014[/TD]
[TD="align: right"]660[/TD]
[TD]GRF SJ[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD="align: right"]1/8/2014[/TD]
[TD="align: right"]665[/TD]
[TD]GRF SJ[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD="align: right"]1/9/2014[/TD]
[TD="align: right"]665[/TD]
[TD]GRF SJ[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD="align: right"]1/10/2014[/TD]
[TD="align: right"]670[/TD]
[TD]GRF SJ[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD="align: right"]1/13/2014[/TD]
[TD="align: right"]663[/TD]
[TD]GRF SJ[/TD]
[TD="align: right"]4
[/TD]
[/TR]
</tbody>[/TABLE]

And to send it you need a connection and post via recordset.
Connect the excel file as a table then run an append query.
like:

Dim con As ADODB.Connection
Dim rs As ADODB.Recordset
Dim vProvid


Set con = New ADODB.Connection

 

[rowbro]

Thanks for the response. So, does this mean that I will have to manually change the data, or will the code you posted do this for me? Also, where do I place that code, as it is VBA not SQL (I assume - sorry, quite new to this).

Thanks again