Export data to MySQL database
Good day,
I need functionality similar to exporting data in Google Sheets, but with the ability to export directly to a MySQL database located on a web server.
Could you please provide an estimate of the scope of work to implement this solution? Let me know if additional details are needed.
I am waiting for your assessment.
Original question is available on version: ua
I need functionality similar to exporting data in Google Sheets, but with the ability to export directly to a MySQL database located on a web server.
Could you please provide an estimate of the scope of work to implement this solution? Let me know if additional details are needed.
I am waiting for your assessment.
Answers:
Good day. Please clarify what entities you want to export? What task do you want to perform with this data?
03.02.2025, 13:05
Original comment available on version: ru
Good afternoon.
You need to export data about processes that are currently being sent to Google Sheets (screenshot attached), as well as contact information.
Once exported, this data will be used for analysis in Metabase.
Let me know if you need any additional details.
You need to export data about processes that are currently being sent to Google Sheets (screenshot attached), as well as contact information.
Once exported, this data will be used for analysis in Metabase.
Let me know if you need any additional details.
05.02.2025, 07:53
Original comment available on version: ua
You can do an action similar to Export processes to Google tables. Only additionally there will be settings for connecting to the MySQL database. Accordingly, we will connect to the database and insert/update data in the specified table. Specify, do you want to upload the process products to which separate table or how to store them there? For example, in the upload to Google tables there are options to upload 1 line as a process product or as a process, but since we will have a database, it became possible to upload products and their additional fields to a separate table, for example.
05.02.2025, 12:42
Original comment available on version: ru
Thanks for the clarification.
I need the data structure in MySQL to be convenient for analysis in Metabase. Am I understanding correctly that it is possible to create a separate table for processes, a separate one for process products, and a separate table for customers, with additional fields for all tables? If so, then this option seems logical.
If there are any recommendations for a structure that would be more efficient for further work with the data, I would be happy to consider them.
I need the data structure in MySQL to be convenient for analysis in Metabase. Am I understanding correctly that it is possible to create a separate table for processes, a separate one for process products, and a separate table for customers, with additional fields for all tables? If so, then this option seems logical.
If there are any recommendations for a structure that would be more efficient for further work with the data, I would be happy to consider them.
07.02.2025, 07:40
Original comment available on version: ua
Yes, we can upload process products to separate tables, processes to separate tables, and clients themselves to separate tables, linking all entities by IDs. I will give you the opportunity to independently write in which tables and which fields to upload which entities in action. The action will take about 12 hours. As a result, you will be able to make a "copy" of the box data on orders in the database you need for subsequent analysis of this data.
Issue an invoice?
Issue an invoice?
07.02.2025, 12:49
Original comment available on version: ru
Yes, please bill me.
In the table below I have indicated what data I need now.
https://docs.google.com/spreadsheets/d/1wCfOhOBF3xQVeSiuHULz-l6qqb_JfU9fuK_n-w6R...
In the table below I have indicated what data I need now.
https://docs.google.com/spreadsheets/d/1wCfOhOBF3xQVeSiuHULz-l6qqb_JfU9fuK_n-w6R...
09.02.2025, 23:12
Original comment available on version: ua
I don't have access to private tables, make it public
10.02.2025, 12:10
Original comment available on version: ru
I shared the link. Please take a look.
If it's PostgreSQL instead of MySQL, will the price be the same?
If it's PostgreSQL instead of MySQL, will the price be the same?
10.02.2025, 21:00
Original comment available on version: ua
1. Why are the products in the Order table?
2. What is the warehouse table? Translated, it means warehouses, but above we did not discuss the transfer of any warehouse balances or warehouses - I was evaluating the transfer of only process products, customer, and process information.
3. If the database is PostgreSQL, the assessment is the same, provided that there is only one DB. That is, for the same assessment I will not do both mysql and postgre
2. What is the warehouse table? Translated, it means warehouses, but above we did not discuss the transfer of any warehouse balances or warehouses - I was evaluating the transfer of only process products, customer, and process information.
3. If the database is PostgreSQL, the assessment is the same, provided that there is only one DB. That is, for the same assessment I will not do both mysql and postgre
11.02.2025, 12:16
Original comment available on version: ru
The bill amount is satisfactory, but I don't fully understand what I'll get at the end.
Could you call me so we can quickly discuss these issues? We can correspond in Telegram and arrange a call.
The database will be PostgreSQL. I will provide the domain and user.
Currently, analytics are happening as indicated in the screenshot in the app.
Could you call me so we can quickly discuss these issues? We can correspond in Telegram and arrange a call.
The database will be PostgreSQL. I will provide the domain and user.
Currently, analytics are happening as indicated in the screenshot in the app.
11.02.2025, 14:29
Original comment available on version: ua
Grechukha Dmitro Sergiyovich wrote:
The sum is overwhelming, otherwise I don’t understand until the end what I take away at the exit.
I will finalize the action, it will contain:
1. Fields for IP and access to the database + the name of the database to which the information must be uploaded
2. Settings with a choice of which table and which field in the specified database to write information about the order, order products, and client
3. Selecting the statuses in which to unload processes.
After configuration, the action will be launched once per hour and will unload processes in the selected BPs to the third-party database in the fields specified in the settings. Thus, you will receive a "copy" of the specified data (about the order, client, order products) in the third-party database.
If something from the above is not clear, you can clarify it here in text mode.
11.02.2025, 15:11
Original comment available on version: ru
Thanks for the reply.
I paid the bill.
From what is written above, everything seems to fit. The only thing that is not clear is how I will link the data of different tables? Will there be such a possibility?
I paid the bill.
From what is written above, everything seems to fit. The only thing that is not clear is how I will link the data of different tables? Will there be such a possibility?
14.02.2025, 13:23
Original comment available on version: ua
Grechukha Dmitro Sergiyovich wrote:
The only thing that didn’t understand is how I’m going to knit these different tablets? Will there be such a possibility?
we have our own order and customer identifiers. Accordingly, you make columns in the necessary tables, for example, oneboxid, and we insert our id into this oneboxid in the order and then, during the next update, we search for this record by it. The same situation with process products, for example - there will be columns oneboxorderid oneboxproductid and I will write my product id into oneboxproductid and the order id into oneboxorderid. Accordingly, I will be able to update records by these same ids.
14.02.2025, 13:28
Original comment available on version: ru
in which column to insert our order, product, client identifier will naturally be in the form of a setting.
14.02.2025, 13:29
Original comment available on version: ru
Thanks for the answer. Now everything seems to be clear.
I confirm the technical specifications.
When can we expect it to be implemented?
I confirm the technical specifications.
When can we expect it to be implemented?
14.02.2025, 13:40
Original comment available on version: ua
I think it will be in a few weeks. I will write in this thread when everything is ready.
14.02.2025, 13:41
Original comment available on version: ru
Good day. The revision has been uploaded. Action once per hour "Export processes to the PostgreSQL database". If something is unclear about the settings - write, I will help within the framework of this task.
19.02.2025, 14:21
Original comment available on version: ru
Good day,
Tried to configure the update. Data is not being uploaded.
Please check. https://ceylon.crm-onebox.com/
Also, please let me know if I need to create tables and table structure for correct upload?
Tried to configure the update. Data is not being uploaded.
Please check. https://ceylon.crm-onebox.com/
Also, please let me know if I need to create tables and table structure for correct upload?
28.02.2025, 11:48
Original comment available on version: ua
Yes, it is necessary that ALL fields that you specify in "Field in database" and all tables that you specify were created. That is, the action does not update the structure of your database
05.03.2025, 12:10
Original comment available on version: ru
Made it, but it doesn't export.
Can you check the settings?
Can you check the settings?
05.03.2025, 16:03
Original comment available on version: ua
1. Each table id does not need to have "Update value" set. This is a unique identifier, it will not change and does not need to be updated.
2. "Configuring the unloading of information about process products into separate tables". There is a concept of a product and there is a process product. There may be a product with id=10 and the name "Petrushka". It can be added to an order with id=15 twice. Accordingly, you should have an orderproducts table with the fields orderproductid orderid productid count so that you can insert 2 records with different orderproductid for one order (the product can be added to the order twice).
3. For some reason you unload the process ID into two different fields.
Everything else seems okay, I launched the action - it works and it clearly unloads something
2. "Configuring the unloading of information about process products into separate tables". There is a concept of a product and there is a process product. There may be a product with id=10 and the name "Petrushka". It can be added to an order with id=15 twice. Accordingly, you should have an orderproducts table with the fields orderproductid orderid productid count so that you can insert 2 records with different orderproductid for one order (the product can be added to the order twice).
3. For some reason you unload the process ID into two different fields.
Everything else seems okay, I launched the action - it works and it clearly unloads something
05.03.2025, 16:30
Original comment available on version: ru
I don't know what I'm doing wrong, but the tables remain empty.
05.03.2025, 17:30
Original comment available on version: ua
Let's start by loading data into one table, for example, customers.
This table has all the entities specified in the "Export processes..." action created: customerid, name, email, phone, created_at.
We need to verify that the data is being successfully uploaded. So far, the customers table has no data.
This table has all the entities specified in the "Export processes..." action created: customerid, name, email, phone, created_at.
We need to verify that the data is being successfully uploaded. So far, the customers table has no data.
05.03.2025, 23:31
Original comment available on version: ua
insert into processes ("process_id") values ('10467')
[message:protected] => ERROR: null value in column "client_id" of relation "processes" violates not-null constraint
DETAIL: Failing row contains (10467, 2025-03-06 10:10:05.1457, null, 0, 0, null, null).
Probably you made the types in the DB mandatory to fill, so I can't insert an order without a client_id at the moment. Without creating an order, the client will not be created.
Please REMOVE from the order or user tables the columns you do not want to use or set up the action so that they are filled. After that, DO NOT CHANGE the action settings and I will see what happens next.
[message:protected] => ERROR: null value in column "client_id" of relation "processes" violates not-null constraint
DETAIL: Failing row contains (10467, 2025-03-06 10:10:05.1457, null, 0, 0, null, null).
Probably you made the types in the DB mandatory to fill, so I can't insert an order without a client_id at the moment. Without creating an order, the client will not be created.
Please REMOVE from the order or user tables the columns you do not want to use or set up the action so that they are filled. After that, DO NOT CHANGE the action settings and I will see what happens next.
06.03.2025, 12:12
Original comment available on version: ru
The processes table has started to be populated with process_id and salesdate, the remaining columns are empty.
I need data to be able to form Table 1, which is attached (order date, customer id, order id, product article number, unit price, quantity, order amount).
Please help with this.
I need data to be able to form Table 1, which is attached (order date, customer id, order id, product article number, unit price, quantity, order amount).
Please help with this.
06.03.2025, 18:04
Original comment available on version: ua
column "LTV" of relation "processes" does not exist
The column title had to be written in lower case letters.
ERROR: column "articul" of relation "processes" does not exist
The column name is incorrect
update customers set "name"='Anton', "email"='xxxx@gmail.com', "phone"='380972xxxxxx', "created_at"='2024-07-25 20:54:06' where customerid='2374'
[message:protected] => ERROR: value "380972774040" is out of range for type integer
You have a column phone integer for the user, but the phone number is beyond the limits of what is acceptable in int , make it either bigint or text.
The column title had to be written in lower case letters.
ERROR: column "articul" of relation "processes" does not exist
The column name is incorrect
update customers set "name"='Anton', "email"='xxxx@gmail.com', "phone"='380972xxxxxx', "created_at"='2024-07-25 20:54:06' where customerid='2374'
[message:protected] => ERROR: value "380972774040" is out of range for type integer
You have a column phone integer for the user, but the phone number is beyond the limits of what is acceptable in int , make it either bigint or text.
07.03.2025, 11:20
Original comment available on version: ru
I have corrected the first two errors in your settings, correct the third one yourself - I am not touching the database structure.
07.03.2025, 11:21
Original comment available on version: ru
Thanks for your help, everything is being downloaded correctly! You can now work with this.
I have a few more clarifications:
1. Do I understand correctly that the "update values" checkbox, if unchecked, allows only new data that has not been previously uploaded to be uploaded?
2. The formula for uploading process products is currently used to upload article numbers to the "Process Product Information" column. Can I similarly upload other data to the appropriate fields (for example, [currency] to the currency column)? What is the syntax for this?
I would be grateful for your answer!
I have a few more clarifications:
1. Do I understand correctly that the "update values" checkbox, if unchecked, allows only new data that has not been previously uploaded to be uploaded?
2. The formula for uploading process products is currently used to upload article numbers to the "Process Product Information" column. Can I similarly upload other data to the appropriate fields (for example, [currency] to the currency column)? What is the syntax for this?
I would be grateful for your answer!
07.03.2025, 12:54
Original comment available on version: ua
1. If the checkbox is not enabled, the field will be transferred only when creating a record and will not be updated. If it is enabled, it will be updated each time the action is launched.
2. You can unload the process products in different ways, below I will describe two options:
Input data: there are 3 products in the process: soap, shampoo and gel.
a) Specify the setting with the formula and select that the process products are uploaded to the product table. Then, when specifying the formula, the order table will have the following value in the selected field:
Soap 1pc, amount Хгрн
Shampoo 1pc, amount Hr
b) unload the process products into a SEPARATE table, there is an option for this in the action below. In this case, the quantity of the product, the amount, price and currency can be unloaded into separate fields. For currency in this case, this is the currencyid field - this is the currency id in the box. In your box, the hryvnia has id=1, i.e. in the currencyid column there will be 1 for the process product.
It is possible to combine both options and unload the process products both there and there.
2. You can unload the process products in different ways, below I will describe two options:
Input data: there are 3 products in the process: soap, shampoo and gel.
a) Specify the setting with the formula and select that the process products are uploaded to the product table. Then, when specifying the formula, the order table will have the following value in the selected field:
Soap 1pc, amount Хгрн
Shampoo 1pc, amount Hr
b) unload the process products into a SEPARATE table, there is an option for this in the action below. In this case, the quantity of the product, the amount, price and currency can be unloaded into separate fields. For currency in this case, this is the currencyid field - this is the currency id in the box. In your box, the hryvnia has id=1, i.e. in the currencyid column there will be 1 for the process product.
It is possible to combine both options and unload the process products both there and there.
07.03.2025, 13:27
Original comment available on version: ru
Thank you very much for your answer.
We are closing this thread.
We are closing this thread.
07.03.2025, 16:16
Original comment available on version: ua
After working with the data, the following questions arose:
1. Please provide error logs that occurred during data upload.
2. I would also like to clarify: do I understand correctly that the realprice parameter (process product field) is the product price that was specified in the process?
3. How to choose the product article number of the process?
Thank you!
1. Please provide error logs that occurred during data upload.
2. I would also like to clarify: do I understand correctly that the realprice parameter (process product field) is the product price that was specified in the process?
3. How to choose the product article number of the process?
Thank you!
08.03.2025, 21:37
Original comment available on version: ua
1. There is no such thing
2. You need to select the field "Price of the process product with discount"
3. There is no such field. The product itself has an article number, but the process product does not.
2. You need to select the field "Price of the process product with discount"
3. There is no such field. The product itself has an article number, but the process product does not.
10.03.2025, 11:14
Original comment available on version: ru
Please join the conversation. If you have something to say - please write a comment. You will need a mobile phone and an SMS code for identification to enter.
Log in and comment
Copyright © OneBox 2025
