I know that this subject has been aired previously but I have not seen a solution to date. I wish to import multiple data from a csv file into a field called "parishname" which uses a lookup table providing a dropdown list of names. The query used by the lookup table is
SELECT "Parish"."parid","Parish"."parishname" FROM "Parish" ORDER BY 2
When importing the csv file, having previously filled in the required data in the parishname column, I find the data does not transfer to my database table although it showed correctly in the MyPhpAdmin table on my host server. I assume this is something to do with the field being assigned as an Integer and not a VARCHAR. Does anybody have a fix please?
Import data from csv file into a field using a lookup table
Re: Import data from csv file into a field using a lookup table
Please advise what version you are using:
Such as: I am using version 5.31 etc.
And you are right. An integer field is just that, an integer. You cannot import text data into it other than integers. You must be using a lookup field from another table. There has to be a link between the tables.
I am assuming you are storing the parish names in one table and then looking them up from another table. Is this correct?
If so then the lookup field references the id of the name of the parish from the table containing the list of parishes.
Here is another example using baseball.
A database has a table which contains a list of all baseball diamonds
Another table contains a list of all the teams
A third table is used to list all the games and their date.
In the third table when you enter a game and its date you then pick from a pop up list of the diamonds and a pop up list of teams - one for visitor and one for home team. You can then select the visiting team and the home team. If you look at the data in the third table using phpmyadmin you will see the date, game number which you entered and a number in the field for home team, visiting team and diamond. If you take the number for these items and look in the data for their tables you will see, for example 24 is the id for the Sox, 15 is the id for the Cardinals and 76 is the id for the High School diamond.
When the data is viewed in the third table the code references those numbers and in the third table (game listing table) it shows the data from the proper table rather than the number. Basically it says - oh a 15 in that field which is linked to the teams table and that references the team name Cardinals so I will show Cardinals in that record. It is still a 15 in the field but the code shows the data from the linked table.
So you need to set things up properly.
Not sure if this makes any sense but without your data file and more info it is all guessing at this point in time.
Alan
Such as: I am using version 5.31 etc.
And you are right. An integer field is just that, an integer. You cannot import text data into it other than integers. You must be using a lookup field from another table. There has to be a link between the tables.
I am assuming you are storing the parish names in one table and then looking them up from another table. Is this correct?
If so then the lookup field references the id of the name of the parish from the table containing the list of parishes.
Here is another example using baseball.
A database has a table which contains a list of all baseball diamonds
Another table contains a list of all the teams
A third table is used to list all the games and their date.
In the third table when you enter a game and its date you then pick from a pop up list of the diamonds and a pop up list of teams - one for visitor and one for home team. You can then select the visiting team and the home team. If you look at the data in the third table using phpmyadmin you will see the date, game number which you entered and a number in the field for home team, visiting team and diamond. If you take the number for these items and look in the data for their tables you will see, for example 24 is the id for the Sox, 15 is the id for the Cardinals and 76 is the id for the High School diamond.
When the data is viewed in the third table the code references those numbers and in the third table (game listing table) it shows the data from the proper table rather than the number. Basically it says - oh a 15 in that field which is linked to the teams table and that references the team name Cardinals so I will show Cardinals in that record. It is still a 15 in the field but the code shows the data from the linked table.
So you need to set things up properly.
Not sure if this makes any sense but without your data file and more info it is all guessing at this point in time.
Alan
Calgary, Alberta, Canada - Using Appgini 5.50 -
Re: Import data from csv file into a field using a lookup table
Thanks Alan for your quick reply. I am using version 5.30 rev 628. You are correct in assuming I am storing the parish names in one table and then looking them up from another table. The main table has, among many, a field called 'parishname' with datatype integer 2 and a lookup table called 'Parish' with two fields, (1)'parid' (primary key) data type integer 2, autoincrement, unsigned and (2) 'parishname' with datatype VAR 20.
I appreciate the example you have given (personally prefer cricket to baseball) and sort of understand what you are getting at but seems in some way more complicated than what I am trying to do with 2 tables. I am not the first person to ask about what appears to me a fairly common requirement to import existing data but Appgini do not seem to have picked up that idjits like me want to import into a lookup field or else they would say this is not possible or would provide a work round for us. After all, the basis of the program is for untrained ignoramuses like me to build a web based database. I think the program is great by the way so sorry for sounding off.
If there is anything specific I can tell you please let me know as I clearly don't know how to set things up correctly.
I appreciate the example you have given (personally prefer cricket to baseball) and sort of understand what you are getting at but seems in some way more complicated than what I am trying to do with 2 tables. I am not the first person to ask about what appears to me a fairly common requirement to import existing data but Appgini do not seem to have picked up that idjits like me want to import into a lookup field or else they would say this is not possible or would provide a work round for us. After all, the basis of the program is for untrained ignoramuses like me to build a web based database. I think the program is great by the way so sorry for sounding off.
If there is anything specific I can tell you please let me know as I clearly don't know how to set things up correctly.
Re: Import data from csv file into a field using a lookup table
I think the solution is very simple but too hard to type all into a text box. If you have a way I can look at your database and the url for the app that would help. Just send me a private message.
It may be just where you are putting your data.
I imported, for a city wide baseball league in Calgary, over 200 diamonds, 300+ teams and no issues. It may just be the way you are looking at things which is not making it work for you.
Hard to explain by typing it in.
It may be just where you are putting your data.
I imported, for a city wide baseball league in Calgary, over 200 diamonds, 300+ teams and no issues. It may just be the way you are looking at things which is not making it work for you.
Hard to explain by typing it in.
Calgary, Alberta, Canada - Using Appgini 5.50 -
Re: Import data from csv file into a field using a lookup table
I know what you mean. It's difficult describing what we want to get over by this medium. Good to know you think it can be done. My problem at the moment is that I'm off on holiday tomorrow for two weeks and don't know what the WiFi reception is likely to be. I hope you will bear with me if I pick this up again when I get back. I can send you my project file or the generated code file if you like. Thank you again for your help.
Re: Import data from csv file into a field using a lookup table
Sure, have fun on your holidays.
Calgary, Alberta, Canada - Using Appgini 5.50 -
Re: Import data from csv file into a field using a lookup table
Back from my holiday which must have cleared my mind since I have now found the solution to my problem. I was trying to import a parish name (e.g. 'Guildford') into an integer field. By changing the text to a number representing the parish name the parish name was displayed correctly in the database table. Hope that makes sense. Please ignore earlier private message and thanks for your help.