PhpMyAdmin - Export different from query result

Question

I'm trying to get some data out of my mariadb database. Here's the simple query I'm making :

SELECT `sku`, `product_id`, sum(`row_invoiced`) as 'ca ht', sum(`qty_invoiced`) as 'qte commande' 
FROM `sales_order_item` 
WHERE `product_id` in ( 637 ) /*simplified*/
AND `created_at` >= '2021-01-01' 
AND `created_at` <= '2021-02-01' GROUP BY `product_id`

and here's the result from myadmin : link I'm like "sure this checks out"

so i tick the bo and hit export, choose csv, and here's the values I'm getting in my csv :link

I'm puzzled. How can the result from the query and the export have different values ?

Which program are you using to look at the CSV file (is it Excel)? What are the values of `ca ht` and `qte commande` if you open the CSV file in a text editor (such as Notepad++, Visual Code Studio, Vim/Emacs, etc)? — Isaac Bennetch, Jul 01 '21 at 13:33

score 0 · Answer 1 · answered Jul 01 '21 at 13:52

It's likely that what is happening here is that you have opened the CSV file in a spreadsheet program such as Excel which is attempting to pick the best format for your numbers without understanding the context of them. There are several ways to proceed, and frankly it starts to depend on which program you're using (and which version, too) as well as the specifics of your data.

What usually works for me is to simply force the column to the correct type. You seem to be on the import dialog where it's prompting you to specify the type for each column, so you can do it there, or you can do it after the import by changing the format (for an individual cell or the entire column) using the toolbar or context menu.

Highly related: Excel CSV - Number cell format

PhpMyAdmin - Export different from query result

1 Answers1