Excel formula or PHP function for editing a database?

So you need data from Excel file imported into WordPress database?

Than you for your attention.

I will import Excel using a plugin, but I need to prepare the database otherwise it will be a mess. This because my theme generates data as a mixture of custom-fields, custom-post-types and arrays.
I used the plugin to export a standard demo Company in order to have data under columns and edit them but before re-importing I need to edit all data… and the problem is the array, because it’s a wrap-up of otherwise separate entries.
I hope I have explained well ??

Hi arsenalemusica!

Is your plan to manage data in Excel and import modified data as needed, or do you only need do this one time to get the data into the database, where it will then be maintained directly?

I have to say that keeping a set of the same data in two places is a very poor scheme. Once the data is in the WP database, it should be maintained there, the Excel data will soon become obsolete and of little use. You should create a form in which the DB data can be managed directly without exporting to Excel. It may take some custom coding, but it doesn’t have to be that complicated.

To get the initial Excel data into PHP, go ahead and use the plugin. I’m not sure what it does with the data, but once it’s in PHP or the DB, PHP script can transform it into the desired format.

If you’re not yet committed to a particular database schema for this, think about it carefully. There’s a lot to be said for keeping data as post meta, then a custom form can be easily built with little programming skill with a plugin like Advanced Custom Fields. Post meta queries are easily defined using familiar WP_Query methods.

Even if you stick with a custom table, you should avoid putting data that you will need to use as query criteria in arrays. Arrays are great for consolidating data, but not if the data is part of a common query, especially if different keys in the array could have the same values.

hello!
My plan is to do this only once, in order to show dummy listings but with real data, as a hook for subscribers. Then they will update their data from their panel.
My task will end there, but with the plugin I’m going to use (the popular Wp-All-Import) I will still have the possibility to make any update in my Excel and the website will just read it.
But do I see your point.

I’m basically doing a reverse engineering. I initially exported a single demo listing so to have all the custom-fields in columns, then I inserted real data, then I will re-import. But I didn’t realize that the single entries are also replicated in the array, so I will have a crash once the system will see incoming data plus an array that doesn’t match with it… unless I do this copy-paste of corresponding data.

You are also right about the post-meta queries because, for instance, in a single Company’s backend I can see some data but not others, in a weird mixture of table/array. For this reason I must work with Excel

I do realize that I’m not explaining it well but I’m still trying to understand how the theme copies the users’ submitted data into the database.

Thanks for the explanation. I’ve made my concerns known, you are of course free to ignore them in whole or in part ?? It sounds like there are valid reasons to not do what is ideal. So be it.

After you’ve done this copy paste work to patch up the data; is the data, once imported into WP, usable without further processing? My inclination is to recommend a script that replaces the need for manual copy/pasting. If the imported data is immediately usable, I would suggest developing a VisualBASIC script that fixes up the Excel data as required. Whether this is worth doing depends on how much data is involved. If it’s faster to manually repair the data one time, it does not warrant developing a script. But if the same repair could be needed any number of times in the future, then it may be worth developing a script anyway.

OTOH, if the data needs to be processed in PHP or phpMyAdmin after import, it may make more sense to develop a repair script with PHP as part of the normal import routine. While it’s best to avoid redundant data, if data does require replication, it’s much preferable for it to be automated by script instead of requiring error prone manual replication.

I’ll try to summarize in a more logical way. I think you’re saying that while there must be an initial one time import, the option of future imports should be available. Thus, to avoid error prone manual data manipulation, ideally a script should be developed to do so.

It may not matter much if this script is VisualBASIC or PHP, but you will want to minimize the steps required for the best user experience. Which language you are more comfortable with could factor into the decision.

The only reason to not develop a script is if fixing the data manually will be faster and it’ll never need to be done again. That’s my story and I’m sticking with it ??

You ask me if data will be usable as is or need need further processing once imported: my answer is “yes it can be used, users will see the dummy data in their panels and edit/save at will”. Therefore (I think) I will not need a function. I’m not a developer therefore I wouldn’t know how to implement such a function anyway (allthough, that Importer plugin has options to write functions).

With my knowledge I was hoping to find a Excel formula instead. The database is huge but with that I would be able to check all data and be ready for import. If this fails then I would need to hire someone for this task.

Maybe if I send you the file as exported (with a single demo listing) you would understand what I must achieve?

I appreciate your willingness to share the file, but I must decline to help to that extent. Excel issues are well beyond the scope of these forums. I will say that formulas are a reasonable way to alter large amounts of data if coding VisualBASIC is too advanced. Once you determine the correct cell formula, copy it down the rest of the column. If the Excel to WP importer is able to import formula values, that’s essentially all you need to do.

If you do find a need for professional WP coding help and don’t have anyone in mind, jobs.wordpress.net and jetpack.pro are a couple resources.

thank you for your help and hints!

I will publish the solution here once I have it ??