Newbie Question - Updating fields from a CSV file

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Ok I have a database with several tables, but I need to update information in
only 1 table.
To be more exact, only in 1 field!

Example -->
The table: Products
Fields in the table:
ProdId (Prim Key and Autonumber)
ProdNumber (integer / unique, but cannot be used as prim. key!)
ProdDescr
ProdAmount

Now I have a CSV (or excel, I can do both) file exported from another program.
This CSV file I have to manually adjust a little, since the field names are
not the same.
Once that is done, I can import the information where it adds, but... here
comes my question: How do I update ?
I need to find a way that it will look for the ProdNumber and when it found
that, only updates the ProdAmount field where needed.
This database is not used on the same location where I need to update it.

Anyone here who can help me out on how to do this?
All help is very welcome!
 
General process I'd use for this:

1) Import the ".csv" file's data to an empty table in the ACCESS database.
2) Use an update query to read the data from the "csv" table and to update
the data in the permanent table accordingly, using the identifiers (primary
key) to find/match data and to test if data need to be updated.


To provide more specific suggestions, would need to know more about the
actual data and how to know if a data value needs to be updated.
 
So long as the data in the .csv "field" can be matched to the data in a
field in the ACCESS table, you can do what you seek even without a primary
key in the .csv file.

Assuming that the textfile is linked or imported as a table named TextFile:

UPDATE TableName
SET TableName.ProdAmount = TextFile.FieldWithNewValue
WHERE TableName.ProdNumber = TextFile.FieldWithProdNumberValue
AND TableName.ProdAmount <> TextFile.FieldWithNewValue;
 
Glad it worked.

For your followup question, you can use a query like this:

UPDATE [456] SET Background = "Blue"
WHERE Background="Navy" Or
Background="Baby Blue";

You can use multiple criteria in the WHERE clause.

Also, may I recommend that you NOT name tables with just numbers? Or if you
must, enclose the table names in [ ] characters when you use them in
queries.
 
If you're using multiple possible values for the same field, it might be
better to use:

UPDATE [456] SET Background = "Blue"
WHERE Background IN ("Navy", "Baby Blue");

It'll keep the SQL statement shorter.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Ken Snell (MVP) said:
Glad it worked.

For your followup question, you can use a query like this:

UPDATE [456] SET Background = "Blue"
WHERE Background="Navy" Or
Background="Baby Blue";

You can use multiple criteria in the WHERE clause.

Also, may I recommend that you NOT name tables with just numbers? Or if
you must, enclose the table names in [ ] characters when you use them in
queries.

--

Ken Snell
<MS ACCESS MVP>


Flipandboef said:
Hey Ken,
Thanks so much!!
It all worked out, thanks to your help... You rock!
Can I perhaps ask you one more question about an update query?
I try to replace some colors within two fields in a table.
BGColor and BRColor.
I was able to create an update query that does only one at the time..
This
is the code i used:
(here Navy will be replaced by Blue)

UPDATE 456 SET Background = "Blue"
WHERE Background="Navy";

Now I have a big list of colors that need to be changed, so I tried to
use
the code:
UPDATE 456 SET Background = "Blue"
WHERE Background="Navy";
UPDATE 456 SET Background = "Blue"
WHERE Background="Baby Blue";

But I get an error on this on this...
Any ideas what I am doing wrong?
Thanks again so much for all the help!
Click to expand...
Click to expand...
 
That's what I would have recommended.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Flipandboef said:
Hi Ken & Douglas,
Well I "think" I figured out a solution...!
I created an extra table ColorNames that has 2 fields: Org and Replace
In the Query I used the following:
Field: Background
Table: 456
Update to:
IIf([Background]=ColorNames.Org,ColorNames.Replace,[Background])

Of course for the Border I did the same, but replaced "Background" with
Border..
I did a small test with just 2 colors and that is working great!
You guys think it will go smooth will all my colors I mentioned before?
I will add all my colors to this new table and will do a try-out, so if I
have results before you can respond, I will let you know about the
results.
Till then: Thank you so much for all the help!

Ken Snell (MVP) said:
I concur.
--

Ken Snell
<MS ACCESS MVP>

Douglas J. Steele said:
If you're using multiple possible values for the same field, it might
be
better to use:

UPDATE [456] SET Background = "Blue"
WHERE Background IN ("Navy", "Baby Blue");

It'll keep the SQL statement shorter.
Click to expand...
Click to expand...
Click to expand...
 
Just add the additional colors to the "color" table that you created. You
should not be limited by how many colors are to be changed to a single color
using this setup.

--

Ken Snell
<MS ACCESS MVP>

Flipandboef said:
Ken and Douglas,
Thank you both for helping me out. You guys have no idea how much this
means
to me!...
However I have still some issues...
The idea Douglas gave me works fine, however I have a lot of colors that
need to be changed.
In the example you used, all will be set to Blue.. Fine if several need to
be changed to only one color, but, I have multiple colors that need to be
converted..
Example:
Navy & Baby Blue (& more) -must become- Blue
Wine & Maroon (& more) -must become- Red
Natural & Oatmeal (& more) -must become- Beige
And this list goes on a little more...
In total I have 140 colors that needs to be converted to these main
colors:
Beige, Black, Blue, Brown, Burgundy, Gold, Green, Grey, Ivory, Multi
Colored, Orange, Peach, Pink, Purple, Red, Rust & Sand...

I am so sorry to ask you guys one more time for help.
Thanks.


Douglas J. Steele said:
If you're using multiple possible values for the same field, it might be
better to use:

UPDATE [456] SET Background = "Blue"
WHERE Background IN ("Navy", "Baby Blue");

It'll keep the SQL statement shorter.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Ken Snell (MVP) said:
Glad it worked.

For your followup question, you can use a query like this:

UPDATE [456] SET Background = "Blue"
WHERE Background="Navy" Or
Background="Baby Blue";

You can use multiple criteria in the WHERE clause.

Also, may I recommend that you NOT name tables with just numbers? Or if
you must, enclose the table names in [ ] characters when you use them
in
queries.

--

Ken Snell
<MS ACCESS MVP>


Hey Ken,
Thanks so much!!
It all worked out, thanks to your help... You rock!
Can I perhaps ask you one more question about an update query?
I try to replace some colors within two fields in a table.
BGColor and BRColor.
I was able to create an update query that does only one at the time..
This
is the code i used:
(here Navy will be replaced by Blue)

UPDATE 456 SET Background = "Blue"
WHERE Background="Navy";

Now I have a big list of colors that need to be changed, so I tried to
use
the code:
UPDATE 456 SET Background = "Blue"
WHERE Background="Navy";
UPDATE 456 SET Background = "Blue"
WHERE Background="Baby Blue";

But I get an error on this on this...
Any ideas what I am doing wrong?
Thanks again so much for all the help!


:

So long as the data in the .csv "field" can be matched to the data in
a
field in the ACCESS table, you can do what you seek even without a
primary
key in the .csv file.

Assuming that the textfile is linked or imported as a table named
TextFile:

UPDATE TableName
SET TableName.ProdAmount = TextFile.FieldWithNewValue
WHERE TableName.ProdNumber = TextFile.FieldWithProdNumberValue
AND TableName.ProdAmount <> TextFile.FieldWithNewValue;

--

Ken Snell
<MS ACCESS MVP>



message
Hi Ken,
First of all, sorry for the late responds, I was not able to do
this
earlier.
Second, thank you so much for your answer.
I have played around with it a little but I am afraid I cannot
figure
it
out.. :(
I was successfull of course in linking / importing a new table from
the
CSV
file.
However, you described that you would use the Primary key to find
the
data
to be updated.
Maybe this is where I get stuck?
I have of course a primary key in the main table, but the CSV file
does
not
have the same primary key.

In the main table i need to update the ProdAmount. I have to
somehow
match
the ProdNumbers (that are unique, but not the prim key) and see if
the
amount
(integer) is changed and that needs to be updated..
As described before I have never used the queries in MS-Access so
I'm
a
total newbie to all of this...
Any further suggestions?
Thanks!

:

General process I'd use for this:

1) Import the ".csv" file's data to an empty table in the ACCESS
database.
2) Use an update query to read the data from the "csv" table and
to
update
the data in the permanent table accordingly, using the identifiers
(primary
key) to find/match data and to test if data need to be updated.


To provide more specific suggestions, would need to know more
about
the
actual data and how to know if a data value needs to be updated.

--

Ken Snell
<MS ACCESS MVP>


message
Ok I have a database with several tables, but I need to update
information
in
only 1 table.
To be more exact, only in 1 field!

Example -->
The table: Products
Fields in the table:
ProdId (Prim Key and Autonumber)
ProdNumber (integer / unique, but cannot be used as prim. key!)
ProdDescr
ProdAmount

Now I have a CSV (or excel, I can do both) file exported from
another
program.
This CSV file I have to manually adjust a little, since the
field
names
are
not the same.
Once that is done, I can import the information where it adds,
but...
here
comes my question: How do I update ?
I need to find a way that it will look for the ProdNumber and
when
it
found
that, only updates the ProdAmount field where needed.
This database is not used on the same location where I need to
update
it.

Anyone here who can help me out on how to do this?
All help is very welcome!
Click to expand...
Click to expand...
Click to expand...
 
Back
Top