Best way to import a spreadsheet with "junk" in numeric fields?

[Colleyville Alan]

I need to import a spreadsheet into an Access table. The spreadsheet has
performance for mutual funds for various periods. The problem is that if
there is no info for a particular period, the spreadsheet contains a dash in
the cell (for example if the fun is only 4 years old and I look in the
columns for 5-year and 10-year performance).

I cannot change the dashes to blank with a global change, the negative
numbers would lose the negative sign. I know there is an acExportDelim
specification that I can fool around with, but I am not sure that is the
best way to go. Currently, I sort the columns until for each column the
dashes are at the bottom and delete them. I do this one col at a time for
quite a few columns and it is a pain. Any ideas?

Thanks
Here is an example of what I am talking about:

Fund name: 1-year 3-years 5-years 10-years
XYZ Fund 20.42 -2.75 - -

[Salad]

Colleyville Alan wrote:

I need to import a spreadsheet into an Access table. The spreadsheet has
performance for mutual funds for various periods. The problem is that if
there is no info for a particular period, the spreadsheet contains a dash in
the cell (for example if the fun is only 4 years old and I look in the
columns for 5-year and 10-year performance).

I cannot change the dashes to blank with a global change, the negative
numbers would lose the negative sign. I know there is an acExportDelim
specification that I can fool around with, but I am not sure that is the
best way to go. Currently, I sort the columns until for each column the
dashes are at the bottom and delete them. I do this one col at a time for
quite a few columns and it is a pain. Any ideas?

Thanks
Here is an example of what I am talking about:

Fund name: 1-year 3-years 5-years 10-years
XYZ Fund 20.42 -2.75 - -

Maybe you can link the spreadsheet in access and write an append query
and use and expression to update the column of concern.

[Steve Jorgensen]

The technique I've arrived at for this kind of thing (exactly this kind of
thing, it sounds like <g>) is to insert a row of cells right under the column
headers, and fill each cell with ~~~. Now, when Access imports the data, it
will import all columns as text, and not try to guess the data types.

After importing, delete the row of ~~~, then use Update queries to fix known
types of data issues that will impede conversion. Finally, Insert that data
into another table with the correct target column types.

On Sat, 29 May 2004 14:37:22 GMT, "Colleyvill e Alan"
<ae***********@ nospam.comcast. net> wrote:

I need to import a spreadsheet into an Access table. The spreadsheet has
performance for mutual funds for various periods. The problem is that if
there is no info for a particular period, the spreadsheet contains a dash in
the cell (for example if the fun is only 4 years old and I look in the
columns for 5-year and 10-year performance).

I cannot change the dashes to blank with a global change, the negative
numbers would lose the negative sign. I know there is an acExportDelim
specificatio n that I can fool around with, but I am not sure that is the
best way to go. Currently, I sort the columns until for each column the
dashes are at the bottom and delete them. I do this one col at a time for
quite a few columns and it is a pain. Any ideas?

Thanks
Here is an example of what I am talking about:

Fund name: 1-year 3-years 5-years 10-years
XYZ Fund 20.42 -2.75 - -

[Colleyville Alan]

Thanks. After I posted this, I tried fooling around with the
TransferSpreads heet command and it seems to update the fields correctly and
leave out the dashes, but I was not sure if that way had any disadvantages
and it is nice knowing there is another way to do this.

BTW, you wrote: "(exactly this kind of > thing, it sounds like <g>) " - do
you work with mutual fund performance info on a regular basis?
"Steve Jorgensen" <no****@nospam. nospam> wrote in message
news:o5******** *************** *********@4ax.c om...

The technique I've arrived at for this kind of thing (exactly this kind of
thing, it sounds like <g>) is to insert a row of cells right under the column headers, and fill each cell with ~~~. Now, when Access imports the data, it will import all columns as text, and not try to guess the data types.

After importing, delete the row of ~~~, then use Update queries to fix known types of data issues that will impede conversion. Finally, Insert that data into another table with the correct target column types.

On Sat, 29 May 2004 14:37:22 GMT, "Colleyvill e Alan"
<ae***********@ nospam.comcast. net> wrote:

I need to import a spreadsheet into an Access table. The spreadsheet has
performance for mutual funds for various periods. The problem is that if
there is no info for a particular period, the spreadsheet contains a dash inthe cell (for example if the fun is only 4 years old and I look in the
columns for 5-year and 10-year performance).

I cannot change the dashes to blank with a global change, the negative
numbers would lose the negative sign. I know there is an acExportDelim
specificatio n that I can fool around with, but I am not sure that is the
best way to go. Currently, I sort the columns until for each column the
dashes are at the bottom and delete them. I do this one col at a time forquite a few columns and it is a pain. Any ideas?

Thanks
Here is an example of what I am talking about:

Fund name: 1-year 3-years 5-years 10-years
XYZ Fund 20.42 -2.75 - -

[Steve Jorgensen]

One of my clients does. I've written the code to import fund performance data
exported in Excel format into a database, and normalize the data.

On Sun, 30 May 2004 01:06:54 GMT, "Colleyvill e Alan"
<ae***********@ nospam.comcast. net> wrote:

Thanks. After I posted this, I tried fooling around with the
TransferSpread sheet command and it seems to update the fields correctly and
leave out the dashes, but I was not sure if that way had any disadvantages
and it is nice knowing there is another way to do this.

BTW, you wrote: "(exactly this kind of > thing, it sounds like <g>) " - do
you work with mutual fund performance info on a regular basis?
"Steve Jorgensen" <no****@nospam. nospam> wrote in message
news:o5******* *************** **********@4ax. com...

The technique I've arrived at for this kind of thing (exactly this kind of
thing, it sounds like <g>) is to insert a row of cells right under the

column

headers, and fill each cell with ~~~. Now, when Access imports the data,

it

will import all columns as text, and not try to guess the data types.

After importing, delete the row of ~~~, then use Update queries to fix

known

types of data issues that will impede conversion. Finally, Insert that

data

into another table with the correct target column types.

On Sat, 29 May 2004 14:37:22 GMT, "Colleyvill e Alan"
<ae***********@ nospam.comcast. net> wrote:

>I need to import a spreadsheet into an Access table. The spreadsheet has
>performance for mutual funds for various periods. The problem is that if
>there is no info for a particular period, the spreadsheet contains a dashin >the cell (for example if the fun is only 4 years old and I look in the
>columns for 5-year and 10-year performance).
>
>I cannot change the dashes to blank with a global change, the negative
>numbers would lose the negative sign. I know there is an acExportDelim
>specificatio n that I can fool around with, but I am not sure that is the
>best way to go. Currently, I sort the columns until for each column the
>dashes are at the bottom and delete them. I do this one col at a timefor >quite a few columns and it is a pain. Any ideas?
>
>Thanks
>
>
>Here is an example of what I am talking about:
>
>Fund name: 1-year 3-years 5-years 10-years
>XYZ Fund 20.42 -2.75 - -
>
>
>

[Colleyville Alan]

I've loaded Excel-based fund perf data into an Access database, matched it
against client holdings, wrote it out to formatted reports for the client
and created a presentation in PPT with the results. There's a lot of
spaghetti code, inefficiencies, and redundancies, but a 20-minute process
now takes less than 10 seconds. But it took 500 hours for a part-time VBA
hack like me to figure out how to do it! Many thanks to this forum for
solving some thorny problems.
"Steve Jorgensen" <no****@nospam. nospam> wrote in message
news:3d******** *************** *********@4ax.c om...

One of my clients does. I've written the code to import fund performance data exported in Excel format into a database, and normalize the data.

On Sun, 30 May 2004 01:06:54 GMT, "Colleyvill e Alan"
<ae***********@ nospam.comcast. net> wrote:

Thanks. After I posted this, I tried fooling around with the
TransferSpread sheet command and it seems to update the fields correctly andleave out the dashes, but I was not sure if that way had any disadvantagesand it is nice knowing there is another way to do this.

BTW, you wrote: "(exactly this kind of > thing, it sounds like <g>) " - doyou work with mutual fund performance info on a regular basis?
"Steve Jorgensen" <no****@nospam. nospam> wrote in message
news:o5******* *************** **********@4ax. com...

The technique I've arrived at for this kind of thing (exactly this kind of thing, it sounds like <g>) is to insert a row of cells right under the

column

headers, and fill each cell with ~~~. Now, when Access imports the data,

it

will import all columns as text, and not try to guess the data types.

After importing, delete the row of ~~~, then use Update queries to fix

known

types of data issues that will impede conversion. Finally, Insert that

data

into another table with the correct target column types.

On Sat, 29 May 2004 14:37:22 GMT, "Colleyvill e Alan"
<ae***********@ nospam.comcast. net> wrote:

>I need to import a spreadsheet into an Access table. The spreadsheet

has >performance for mutual funds for various periods. The problem is that if >there is no info for a particular period, the spreadsheet contains a dashin

>the cell (for example if the fun is only 4 years old and I look in the
>columns for 5-year and 10-year performance).
>
>I cannot change the dashes to blank with a global change, the negative
>numbers would lose the negative sign. I know there is an

acExportDelim >specificatio n that I can fool around with, but I am not sure that is the >best way to go. Currently, I sort the columns until for each column the >dashes are at the bottom and delete them. I do this one col at a time

for

>quite a few columns and it is a pain. Any ideas?
>
>Thanks
>
>
>Here is an example of what I am talking about:
>
>Fund name: 1-year 3-years 5-years 10-years
>XYZ Fund 20.42 -2.75 - -
>
>
>