Excel Spreadsheet to multiple tables

Databases
1

Hello,
I am really not sure if this is the correct place within the forum for my query but as its database related I thought this might be appropriate.

I am having an issue with a pricing of products that I have in MySQL but I generate spreadsheets of prices in an Excel Sheet. The changes need to be done at multiple places in MySQL so what is the best method to transfer from Excel to Spreadsheet.

There is actually an online form available with HTML generated rows and columns for adding pricing, but its not possible to directly copy / paste the data from Excel to the online form.

Is there any way we could directly copy / paste data from Excel to an Online form then my task could save me hours in retyping all prices each time they change? Right now copy pasting in one input box, all data is entered only within that input box. I tried using tab delimited text too so that for each tab it would automatically move to next input box but it does not. I guess that there might be some better method to do it

each input box within the form is generated in this manner

<td><input type="text" size="6" value="5.00" name="125_col3"></td>

the above is for product id 125 variant 3

There are around 40 columns(variants of products) and around 500 different products so each time there is a product change we need to manually go to each box and change which is almost 20000 entries.

But in excel its quite easy to create a formula and say add 5% to price then all prices are changed, this simply needs to be pasted in the online form.

Is there any easy method to do this?

Second option would be to create a script to convert each row / column into respective data so next best option is that.

2

An excel spreadsheet is not a database, and a database is not a spreadsheet.

The only way you might to get close to what you’re looking for is to use CSV files and use phpmyadmin to import/export for each individual table. You would not be able to merge data from different tables, change, and import it back.

However, you should have a front-end to this system of yours to make the changes you’re looking for without having to edit anything at the database level. Is this a pre-built software package (like magento) , or is it a custom project?

3

Thanks @Force Flow for your response. Actually if it were just one or two tables, I would have used CSV and imported it to the table. But the developer of the custom product table has made it a bit complicated and it uses close to 4 different tables to calculate the values.
Second issue I face regarding pricing is that in excel we store the content in rows as 1 product = 1 row and around 40 columns. But in the table each product and price is in a separate record so 40 prices = 40 records with each having other values linked to it.
Is there any way atleast I could convert all the values in the columns to rows? 1 at a time is possible If I select excel convert rows to columns or vice a versa it does that. Any simpler way?

Thanks