weekly import of data from csv

[dkemas]

I need to populate a table within mysql with data from an excel spreadsheet.

A new updated spreadsheet will be generated weekly, the data will need to be entered into the table weekly to reflect this new spreadsheet.

That in itself isn’t a problem, I have been doing this by preventing access to the system, removing data from the table then importing the csv via phpmyadmin from the new spreadsheet each week, open the system back up.

Now however the data in mysql needs to have more fields than the spreadsheet provides.

It is basically staff data, the csv provides data such as

Forename, surname, email address, job title, dept

But in the database I also need other fields such as is_manager and is_admin that provides info on what they can view on the system.

These extra fields cannot come from the csv as that info is not available in the system that csv is generated from, nor can it be added to that system.

Can anyone suggest how I should go about doing this? Also, is there a better way of me importing the new spreadsheet each week that how I explained I currently do it above?

 

[spamjim]

How is the CSV file being created? It is probably an unnecessary middle step to a simpler solution.

 

[spamjim]

...and if `is_manager` and `is_admin` are not being supplied in the CSV file, just make them in their own table and relate them to your CSV-generated table.

 

[dkemas]

The csv is generated from a third party oracle database, we have no access to the database but can only view data and export csv's.

 

[vacunita]

So where is the extra info for the CSV coming from?

You could probably create a PHP script to automate the process of importing the csv to the DB as well as adding the extra info to it.



----------------------------------
Phil AKA Vacunita
----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

Web & Tech

 

[dkemas]

The extra info is for this system only and will be a manually updated list. i.e. these people are current assigned as managers xxx, xxx.

Here is the process I see;

export csv from oracle database
remove current data from staff table
upload csv into now empty staff table
add extra columns
run script to look for names and assign is_manager as 1

I have been doing this manually but it would make better use of time to try to automate it.