Importing array/matrix data into Access

rowbro

New Member
Joined
Dec 16, 2010
Messages
43
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.

Date
ACL SJ Equity
BAT SJ Equity
CSB SJ Equity
GRF SJ Equity
1/2/2014
3764
658
12315
654
1/3/2014
3771
660
12495
660
1/6/2014
3800
680
12474
660
1/7/2014
3860
680
12430
660
1/8/2014
3826
707
12460
665
1/9/2014
3788
744
12515
665
1/10/2014
3579
745
12350
670
1/13/2014
3634
738.8
12574
663

<tbody>
</tbody>


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:

Ticker ID
Ticker
1
ACL SJ Equity
2
BAT SJ Equity
3
CSB SJ Equity
4
GRF SJ Equity

<tbody>
</tbody>

Meaning the other table could be as follows:

Date
ACL SJ Equity
Ticker ID
1/2/2014
3764
1
1/3/2014
3771
1
1/6/2014
3800
1
1/7/2014
3860
1
1/8/2014
3826
1
1/9/2014
3788
1
1/10/2014
3579
1
1/13/2014
3634
1
1/2/2014
658
2
1/3/2014
660
2
1/6/2014
680
2
1/7/2014
680
2
1/8/2014
707
2
1/9/2014
744
2
1/10/2014
745
2
1/13/2014
738.8
2
1/2/2014
12315
3
1/3/2014
12495
3
1/6/2014
12474
3
1/7/2014
12430
3
1/8/2014
12460
3
1/9/2014
12515
3
1/10/2014
12350
3
1/13/2014
12574
3
1/2/2014
654
4
1/3/2014
660
4
1/6/2014
660
4
1/7/2014
660
4
1/8/2014
665
4
1/9/2014
665
4
1/10/2014
670
4
1/13/2014
663
4

<tbody>
</tbody>

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
 

Some videos you may like

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

ranman256

Well-known Member
Joined
Jun 17, 2014
Messages
1,957
yes Access would want it stored thus:
DateequitytickerTickerID
1/2/20143764ACL SJ1
1/3/20143771ACL SJ1
1/6/20143800ACL SJ1
1/7/20143860ACL SJ1
1/8/20143826ACL SJ1
1/9/20143788ACL SJ1
1/10/20143579ACL SJ1
1/13/20143634ACL SJ1
1/2/201412315CSB SJ3
1/3/201412495CSB SJ3
1/6/201412474CSB SJ3
1/7/201412430CSB SJ3
1/8/201412460CSB SJ3
1/9/201412515CSB SJ3
1/10/201412350CSB SJ3
1/13/201412574CSB SJ3
1/2/2014654GRF SJ4
1/3/2014660GRF SJ4
1/6/2014660GRF SJ4
1/7/2014660GRF SJ4
1/8/2014665GRF SJ4
1/9/2014665GRF SJ4
1/10/2014670GRF SJ4
1/13/2014663GRF SJ4

<tbody>
</tbody>

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:

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


Set con = New ADODB.Connection
 
Last edited:

rowbro

New Member
Joined
Dec 16, 2010
Messages
43
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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,270
Messages
5,600,635
Members
414,398
Latest member
dhune

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top