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.

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

 

[ranman256]

yes Access would want it stored thus:

Date	equity	ticker	TickerID
1/2/2014	3764	ACL SJ	1
1/3/2014	3771	ACL SJ	1
1/6/2014	3800	ACL SJ	1
1/7/2014	3860	ACL SJ	1
1/8/2014	3826	ACL SJ	1
1/9/2014	3788	ACL SJ	1
1/10/2014	3579	ACL SJ	1
1/13/2014	3634	ACL SJ	1
1/2/2014	12315	CSB SJ	3
1/3/2014	12495	CSB SJ	3
1/6/2014	12474	CSB SJ	3
1/7/2014	12430	CSB SJ	3
1/8/2014	12460	CSB SJ	3
1/9/2014	12515	CSB SJ	3
1/10/2014	12350	CSB SJ	3
1/13/2014	12574	CSB SJ	3
1/2/2014	654	GRF SJ	4
1/3/2014	660	GRF SJ	4
1/6/2014	660	GRF SJ	4
1/7/2014	660	GRF SJ	4
1/8/2014	665	GRF SJ	4
1/9/2014	665	GRF SJ	4
1/10/2014	670	GRF SJ	4
1/13/2014	663	GRF SJ	4

<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:

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