Import External data

[Nigel Molesworth]

I'm a tutor, I've got a "Student" database. The Student table has a
unique PK, the ID of the students. There are many fields within the
DB, all the normal stuff you would expect, address, phone etc.

When I have marked their assignments, I can download a CSV file
containing all the revised data, but I don't want to overwrite the
earlier data as I've changed some of it. All I want is to incorporate
the new data into the table. So my plan was to delete all the columns
in the CSV except the PK and the assignment mark, as below:

ID AS1
X3781154 95
X8245243 94
R9613369 92
W2740708 83
....

When I try to import this, I get a PK violation.

Is there a way around this?

 

[Douglas J. Steele]

Try importing the new data into a separate temporary table, and then run an
Update query to modify the data in the existing table.

However, it strikes me that you're bringing in new data, not replacing
existing data. Shouldn't the latest marks be an additional row in some
table?

 

[Joseph Meehan]

I'm a tutor, I've got a "Student" database. The Student table has a
unique PK, the ID of the students. There are many fields within the
DB, all the normal stuff you would expect, address, phone etc.

When I have marked their assignments, I can download a CSV file
containing all the revised data, but I don't want to overwrite the
earlier data as I've changed some of it. All I want is to incorporate
the new data into the table. So my plan was to delete all the columns
in the CSV except the PK and the assignment mark, as below:

ID AS1
X3781154 95
X8245243 94
R9613369 92
W2740708 83
...

When I try to import this, I get a PK violation.

Is there a way around this?

Could you provide a little more information. What exactly does
"incorporate the new data into the table" mean?

A more detailed description of your table design and what new data you
will be adding and or amending as well as the format of the new data might
be helpful.

 

[Nigel Molesworth]

Could you provide a little more information. What exactly does
"incorporate the new data into the table" mean?

The data in the ID field is the PK of the table, the data in the AS1
field is currently an empty column. I want the table to look like this
afterwards (much simplified):

ID Name AS1
X3781154 Fred 95
X8245243 Alice 94
R9613369 Bob 92
W2740708 Eve 83

I guess I'm looking for a merge of some kind, here is a tuple with a
PK and data, find that PK in the existing table and merge...

<FX: Light Bulb>

I should be adding this a new linked table, shouldn't I.

 

[Guest]

It sounds as though you are keeping your course results in the same table as
your student data. If that's the case, then add a new column for the
assignment (AS1), import your csv file and save as a temp table (csvTemp)
then run an update query.

e.g. assuming your student table is called tblSudent try:

UPDATE tblStudent, csvTemp SET tblStudent.AS1 = csvTemp.AS1 WHERE
tblStudent.ID=csvTemp.ID;

Thanks
Delordson
http://www.InstantSoftwareTraining.com

 

[Douglas J. Steele]

Note, though, that it's a violation of Database Normalization principles to
have repeated groups in a table to represent the marks.

See http://www.databaseanswers.org/data_models/student_assignments/index.htm
for one way to model this.

 

[Nigel Molesworth]

Note, though, that it's a violation of Database Normalization principles

When I first created this database I fully normalised it. I had tables
for names, addresses, phone numbers, assignment scores, etc all with a
FK to main table.

Problem was that updating the tables was a nightmare, so I went back
to a flat database. It's good enough for this application.

 

[Joseph Meehan]

When I first created this database I fully normalised it. I had tables
for names, addresses, phone numbers, assignment scores, etc all with a
FK to main table.

Problem was that updating the tables was a nightmare, so I went back
to a flat database. It's good enough for this application.

Properly normalized tables should be easier to update not harder.

Of course the tables need to be properly related and you need to use
properly designed forms and or queries to edit your data.

BTW the normalize issue was why I asked about more details. It was
sounding like your problems may well have been related to normalization and
choosing what data to keep.

 

[Guest]

It might initially seem easier to work with flat files but it will cost you
later as your system get bigger and more complex.

Thanks
Delordson Kallon

 

[Nigel Molesworth]

It might initially seem easier to work with flat files but it will cost you
later as your system get bigger and more complex.

I agree, but this will never grow past 40 entries.