Importing data from an excel spreadsheet that is in the wrong form

[Guest]

Hello;

I am in the process of converting data that has been stored in excel
spreadsheets. The amount of data is HUGE and as time goes on we are in
danger of running out of space or crashing. The data is currently setup as:

Val Date Fund1 Fund2 Fund3 Fund4 Fund5 .....................
1961/01/01 10.00 11.89 12.34 13.46 14.56

we actually have data in the spreadsheet beginning in 1961 and every
business day to current. In total, I need to convert the data for 3 lines of
business which represents a total of approx 150 funds. Being as all the
funds are currently input on one row per business day, I cannot just import
the spreadsheet and go from there. The Data needs to be converted to a
columnar format with fields such as VAL_Date, Fund and Unit Value.

Does anyone have a suggestion as to how import the data and put it into the
correct format?

 

[John Nurick]

Hi Sherry,

If you have or can install Perl on your computer, there's a utility
"txtnrm.pl" at http://www.j.nurick.dial.pipex.com/Code/Perl/index.htm
for doing exactly this sort of thing. You just have to save the Excel
sheet as a CSV file and run the utility to create a "tall, narrow" text
file ready to import into the table you describe.

Otherwise I'd start by using File|Get External|Data to create a linked
table connected to the worksheet.

There may be problems doing this, especially if (as I imagine) many of
the funds were not in existence back in 1961. If so, try the following:

1) Make sure that all the "Fund" headings are unique, valid Access
fieldnames.
2) Create a dummy record for a date in 1960, with a value for every
fund.
3) Use Excel's Insert|Name|Define to define a named range including only
the columns and rows you need, then link to the range instead of the
worksheet.

Once the linked table is set up, it's just a matter of running about 150
append queries like this
INSERT INTO NewTable
SELECT Val_Date, "FundXX" AS Fund, FundXX AS Unit_Value
FROM LinkedTable
WHERE Len(Nz(FundXX,"")) > 0
;

which can of course be automated with a VBA procedure.

 

[PY & Associates]

Hi Sherry

If you like, we can convert the Excel file to your preferred format.
Currently we are unsure about the data layout and where "unit value" is

Regards
PY & Associates

 

[Guest]

Thanks Guys for the insight! A friend of mine has Perl on his comp and is in
the process of converting it for me now!

This site is the best thing since slice bread! I have learned so much from
many people either via my questions or reading previous posts!


--
SherryW

Hi Sherry

If you like, we can convert the Excel file to your preferred format.
Currently we are unsure about the data layout and where "unit value" is

Regards
PY & Associates