MySQL Expert needed!

[beanbag]

Hi all,

Hope you can help me out here. I've set up a MySQL database and everything is working fine however I've received some updated data (CSV) and need to import it into my database.

The problem is I've added extra fields into the table and whenever I update the data, it goes through ok, but deletes the data from existing that aren't included in the new updated CSV file i'm importing.

Just so I make sense, an example.....

Old Data said:

Name Address Postcode Nick 1 Street 123456

New Data said:

Name Postcode Nick 098733

Updated Table said:

Name Address Postcode Nick 098733

Now after importing the new data you loose the address information so you get a blank field although the postcode updates correctly. Does this make sense?

Now my question is how do I update my data without overwriting the old data????

Any answers would be greatly appreciated!

Thanks!

Nick

[Plotloss]

I would guess its because its reading the data automatically from the CSV and just dropping it in column order when it hits the DB.

Try putting an extra comma between Address and Postcode or mapping each field to each column explicitly.

[silverfox]

Yes, as it's a CSV file there does need to be a comma between every data item for the update to work for each field.

The only concern you might have is which data is valid for your application - the "old" data or the "new" data and to that end you might have some manual processing within the "new" data file prior to importing.

[beanbag]

No good! Tried that....

Btw....the table is far more complicated than the example.

There are about 50 fields in the database and maybe 30 in the CSV so I can't really add a few commas as I'd be here all week! (Plus there are about 3800 records!!!!)

I know the obvious thing would be to normalise the database but I really don't have the time to do this as it started out as a small project and grew out of proportion!

[Plotloss]

So a small job then...

Why are there 20 erroneous columns in the DB is my first question...

[lanciachris]

Plotloss said:

So a small job then... Why are there 20 erroneous columns in the DB is my first question...

[beanbag]

Plotloss said:

So a small job then... Why are there 20 erroneous columns in the DB is my first question...

They are extra fields I've added into the database but are not in the CSV. They perform extra functions, and the data is added once the PHP processess the database data or if the user adds extra data in, etc....

One of my solutions would be to create another table, put the extra fields in there and link them together. This in itself is a major task and unless it's not possible to update the data like I want to, i'd like to avoid this!!!!

[silverfox]

Another idea perhaps...

Can you import the *new* csv file into Excel, insert blank columns with headings (or field names that match the destination database field names) and that will pad out the 30 fields to 50.

Then export the data as CSV to a new file to then try importing into MySQL?

[beanbag]

Obviously can't show data as it's confidential but here is the field listing in the table:

Primary Key is Node and Domain!
###############################
node
priority
revision
system_owner
secondary_system_owner
###_data_requested ***
###_data_requested_date ***
###_do_we_have_data ***
###_source ***
###_date ***
###_data ***
#######_pre_reqs_met ***
Patches_Required ***
Special_Instructions ***
Diagnostics_Required ***
Reboot_Required ***
diags_prereqs_###_no ***
diags_prereqs_###_date ***
diags_prereqs_###_call_no ***
diags_prereqs_###_engineer ***
diags_prereqs_patches ***
diags_prereqs_###_notes ***
diags_installed ***
#######_install_###_no ***
#######_install_###_date ***
#######_install_patches_required ***
#######_install_patches_reboot_required ***
#######_install_###_notes ***
#######_install_complete ***
#######_install_complete_date ***
#######_config_date ***
#######_config_complete ***
#######_tested ***
#######_tested_date ***
#######_client_install_complete ***
#######_client_install_complete_date ***
domain
Environment
Vendor
Location
Floor
Grid_Ref
User_contact
Serial_number
Model
CPU_Speed
CPU_Count
RAM
source_data_ver
Array_connected
Roots_home
messages_present
Console
Short_Desc
Chassis
Cluster
Built_by
SysOwn_Email
App_Group
App_subgrp
App_support
Boot_up_time
Support_level
Maintenance_Slot
Complexity_rating
Stability_rating
Support_Team
System_Decomissioned ***
system_remove_from_source_data ***
date_removed_from_source_data ***
date_added_to_source_data ***

>> Edited by beanbag on Monday 4th April 13:15

[beanbag]

silverfox said:

Another idea perhaps... Can you import the *new* csv file into Excel, insert blank columns with headings (or field names that match the destination database field names) and that will pad out the 30 fields to 50. Then export the data as CSV to a new file to then try importing into MySQL?

Tried that! If you do this, it'll erase the fields that are already filled in. That's the problem I have and need to avoid!

[Plotloss]

Can you not move the extra 20 colums so the CSV and the DB structure match?

Whats the source of this CSV, can the order of them be changed easily?

Can the transform script be changed to include the target names of the columns in the DB?

[silverfox]

How are you importing the data - what command line?

[beanbag]

Plotloss said:

Can you not move the extra 20 colums so the CSV and the DB structure match? Whats the source of this CSV, can the order of them be changed easily? Can the transform script be changed to include the target names of the columns in the DB?

You'll have to help me out here!

1. Yes and i've done that. No good tho.

2. The source basically includes all the data that I showed above without the three asterix symbols (***). Those columns are the added ones that keep getting over-written.

3. What do you mean by the transform script?? (The import script?)

4. (Silverfox) - I'm not using a MySQL command. I'm using MySQL Front to help me!

[pdV6]

Don't know anything about MySQL, but it seems as though the IMPORT is treating the "missing" fields as valid data, i.e. a blank will overwrite data that's currently in that column. This wouldn't necessarily be an error as such, as who's to say that the updated data doesn't actually intend to update the field to be blank? (I know in your case, its not - but the generic process doesn't know this).

Sounds like what you need to do is either:
(a) get the "missing" columns into the source database and fill them with your required data

or

(b) process the incoming CSV in some other way so as to create an update that matches your implied business process.

Either way it seems that there's some work for you to do over and above fiddling with some settings.

[beanbag]

Just had a friend call me about this....if anyone knows how to write this in SQL (and it works), I will seriously buy you a crate of beer!

Get the SQL to update unless the field in NULL in the CSV data.

ie....update the data, however if the field is NULL in the CSV (no data in there), don't update the data. I'm sure you can do it but how?



(P&P not included - local delivery available)




>> Edited by beanbag on Monday 4th April 13:32

[beanbag]

OK....In pseudo code this is what I need in SQL:

add or amend table on columns where value from csv file is not null.

Hopefully this makes better sense.

Cheers!

[pdV6]

Its not really something you can do in SQL directly from the CSV. You'll need to import the CSV to a temporary table of appropriate structure and then write an update from the temp table to the "live" table.

Its easy enough once you have both sets of data in the same database. As I said, I don't know MySQL so won't confuse the issue by giving the update in MS-SQL syntax.

[Plotloss]

Whats the import script written in?

It will just be changing that to check if the read value from the CSV is NULL (most languages have an IS NULL operator I believe)

Should be fairly simple for someone compliant with the language.

If it was a DTS script I could possibly help but PHP is right out for me...

[beanbag]

I used PHP! Although if written in another language, I could translate over to PHP, or at least it would give me a better idea of how to solve the problem.

Also, the same goes for MS-SQL. It's shouldn't be a huge problem migrating one to the other.

Ideas?

[beanbag]

Morning all. Right. After much pondering and searching countless forums and MySQL manuals, I came up with this:

SQL update Query said:

UPDATE ####_data, csv_import SET ####_data.node = csv_import.node AND ####_data.domain = csv_import.domain AND ####_data.environment = csv_import.environment AND ####_data.vendor = csv_import.vendor AND ####_data.location = csv_import.location AND ####_data.floor = csv_import.floor AND ####_data.grid_ref = csv_import.grid_ref AND ####_data.user_contact = csv_import.user_contact AND ####_data.system_owner = csv_import.system_owner AND ####_data.serial_number = csv_import.serial_number AND ####_data.priority = csv_import.priority AND ####_data.revision = csv_import.revision AND ####_data.model = csv_import.model AND ####_data.cpu_speed = csv_import.cpu_speed AND ####_data.cpu_count = csv_import.cpu_count AND ####_data.ram = csv_import.ram AND ####_data.######_ver = csv_import.######_ver AND ####_data.array_connected = csv_import.array_connect AND ####_data.roots_home = csv_import.roots_home AND ####_data.messages_present = csv_import.messages_present AND ####_data.console = csv_import.console AND ####_data.short_desc = csv_import.short_desc AND ####_data.chassis = csv_import.chassis AND ####_data.cluster = csv_import.cluster AND ####_data.built_by = csv_import.built_by AND ####_data.sysown_email = csv_import.sysown_email AND ####_data.app_group = csv_import.app_group AND ####_data.app_subgrp = csv_import.app_subgrp AND ####_data.app_support = csv_import.app_support AND ####_data.boot_up_time = csv_import.boot_up_time AND ####_data.support_level = csv_import.support_level AND ####_data.maintenance_slot = csv_import.maintenance_slot AND ####_data.complexity_rating = csv_import.complexity_rating AND ####_data.stability_rating = csv_import.stability_rating AND ####_data.secondary_system_owner = csv_import.secondary_owner AND ####_data.support_team = csv_import.support_team WHERE ####_data.node = csv_import.node AND ####_data.domain = csv_import.domain

However.....it came up with an error and i can't see what's wrong!!!!! Again I've blanked stuff out for confidentiality reasons but it should work. I've tried it on a small test database.

The damned error said:

SQL execution error # 1062. Response from the database: Duplicate entry '0-########_##' for key 1

This so called duplicate doesn't exist let alone is duplicated! Any ideas chaps and chapettes?