473,326 Members | 2,061 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

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

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


Nov 13 '05 #1
5 1904
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.

Nov 13 '05 #2
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, "Colleyville 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
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 - -


Nov 13 '05 #3
Thanks. After I posted this, I tried fooling around with the
TransferSpreadsheet 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, "Colleyville 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
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 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 - -

Nov 13 '05 #4
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, "Colleyville Alan"
<ae***********@nospam.comcast.net> wrote:
Thanks. After I posted this, I tried fooling around with the
TransferSpreadsheet 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, "Colleyville 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
>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 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 - -
>
>
>


Nov 13 '05 #5
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.com...
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, "Colleyville Alan"
<ae***********@nospam.comcast.net> wrote:
Thanks. After I posted this, I tried fooling around with the
TransferSpreadsheet 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, "Colleyville 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 >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 - -
>
>
>

Nov 13 '05 #6

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
by: shuhsien | last post by:
Hi, I am confused by the junk parameter in the difflib.sequencematcher. I thought it would simply ignore everything that's returned true by the junk function. However, I have results as follows:...
19
by: Peter A. Schott | last post by:
I've got a file that seems to come across more like a dictionary from what I can tell. Something like the following format: ###,1,val_1,2,val_2,3,val_3,5,val_5,10,val_10...
2
by: wen | last post by:
and, in which case, the following case will happen: if __name__!='__main__': do_sth() any help would be appreciated.
1
by: jmev7 | last post by:
Greetings Data Gurus! I've been asked to help find and delete junk records from a database table that comes from online data request forms: you know, those annoying forms you have to fill out to...
2
by: nutthatch | last post by:
I want to be able to import an Excel spreadsheet into Access 2K using the macro command Transferspreadsheet. However, the file I am importing (over which I have no control) contains some records...
40
by: aku | last post by:
I'm looking for the absolute fastest way to count the nr of bits that are set to "1" in a string. Presumably I then first need the fastest way to do this in a byte. I think this is it, but...
40
by: Dave Hansen | last post by:
Please note crosspost. Often when writing code requiring function pointers, it is necessary to write functions that ignore their formal parameters. For example, a state machine function might...
8
by: Ian Davies | last post by:
Hello I have the following sql string to run as a command in my VB6 project to update mysql table strSQL = "LOAD DATA INFILE " & ImportFile & " INTO TABLE tPupils FIELDS TERMINATED BY ','...
1
by: =?Utf-8?B?VGFsYWxTYWxlZW0=?= | last post by:
i have facility in my website which sends an e-mail to the cleint's inbox..... i am getting a problem when ever i sends an e-mail.. i tried with Hotmail and Yahoo, e-mail goes to the "Junk Mail"...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.