Importing Meal Data admin defaults issue

[sbservices]

I have exported the data so it can be tidied up in a spreadsheet and reimported. However, when you reimport the data from csv file, it changes all the 'added_by' to '1' and all the 'added_by_admin' to '1'. However, I need to reimport the data so that the 'added_by' remains the same and that all the items we set to 'added_by_admin' are blank where we need them to be.

This is so that users only see their own entries and admins.

Currently when we import the data, everything is 'as admin' and everything is added_by user 1, so everyone can see all the entries.

I have tried importing the CSV as a UTF8 formatted file into MySQL directly but I get errors that the 1st column does not contain the right information. So I am wondering how am I going to get the data back in to the database with the entries exactly as we set them please?

Circled in Red are items visible to the user that created them, items in blue are admin entries that anyone should be able to find in a search. But after importing they all become admin entries because I'm the one importing them.

[alicolville]

I’m presuming you’ve not specified (not sure what tool you’re using), which column imports into which. For example it maybe trying to import an integer into a Boolean field L.

[sbservices]

I was importing using the import feature on the Meal Tracker in the Wordpress back office.

I don't know how to convert an excel csv spreadsheet back to a SQL file for importing directly to the database. But it prompted me that I needed to save the Excel CSV in UTF8 format. So I did that, but then importing direct to mySQL using the csv spreadsheet in UTF8 format does not ask to map fields or anything, but the column headings were same as the database.

Image of where I am clicking to import in mySQL

sample of the data it is importing

BEGIN; INSERT INTO wp_yk_mt_meals (id) VALUES ('id,added_by,added_by_admin,name,calories,quantity,fraction_parent,fraction,unit,description,deleted,favourite,source,ext_id,ext_serving_id,ext_image,ext_url,imported_csv,added,proteins,fats,carbs'), ('17042,2,,Aldi Brooklea Authentic 0% Fat Free Greek Yoghurt - 1/2,31,50,60,0.5,g,,0,0,user,,,,,0,20/03/2023 13:43,5,0,2'), ('8076,2,,Breafast conflakes ,204,0,,0,na,,0,0,user,,,,,0,20/10/2022 19:21,0,0,0'), ('7451,2,,Caraldine Shepherds Pie ,367,0,,0,na,,0,0,user,,,,,0,10/10/2022 17:35,0,0,0'), ('7502,2,,Cauliflower breakfast ,269,0,,0,na,,0,0,user,,,,,0,11/10/2022 13:34,0,0,0'), ('7528,2,,cinema treats at home WLW ,464,0,,0,na,,0,0,user,,,,,0,11/10/2022 18:05,0,0,0'), ('7735,2,,Daily Breakfast Carly,359,0,,0,na,,0,0,user,,,,,0,14/10/2022 18:58,0,0,0'), ('11664,2,,Dunnes oat milk - 1/2,46,100,2882,0.5,ml,Oat milk no sugars,0,0,user,,,,,0,12/01/2023 20:27,1,0,8'),

[alicolville]

My first test would be to drop the id. The ID is a primary key field that autoincrements i.e. you shouldn't specify it on an insert.

Also, your tool hasn't worked, you have basically got, insert into 'id':

'17042,2,,Aldi Brooklea Authentic 0% Fat Free Greek Yoghurt - 1/2,31,50,60,0.5,g,,0,0,user,,,,,0,20/03/2023 13:43,5,0,2'

Your quotes should be for each column, not everything within it.

[sbservices]

After about 15 attempts (because either data was missing in the fields that could not be NULL or because converting to CSV from excel resulted in a few backslash characters that broke the syntax) I finally got the csv file converted to mySQL script file for importing in to the database. I'm working on a Cloudways hosted server, but it looks the same as phpAdmin.

I used this website:-

https://www.convertcsv.com/csv-to-sql.htm

With these settings:-

And providing all required columns have data entered to them it will work. Including the ID number, the added_by user ID number and the added_by_admin data.

Having tested the search facility on the database it is also working great with no duplicates now.

Although not fully tested yet to see how it affects the history of tracked meals and also what food items a member user had previously created and are they successfully still registered to that user! I'll update this post after further testing in case anyone else intends to cleanse their meal tracker database.