Import csv

RonP · Post by **RonP** » 2023-09-03 18:09

Hi,
I've some troubles to get just three records to be imported.
I get these records from a XLSX file and saved this AS .csv (there are more types of CSV)
Is there (for DOS) a preferable one?
I just get the last record into the table. The .csv-file has 3 records
If I try to import just one record than the record is skipped and I get the message:
csv-index:293 action: Skipped

What does csv-index mean ?

I work with AppGini 23.14 revision 1435

Ron

onoehring · Post by **onoehring** » 2023-09-04 04:51

Hi Ron,

a couple of questions first that might help to answer your question:
1. are you using the "new" or the "old" CVS Import wizard
2. are you using some weird enconding (UTF-8 should probably work best)
4. does your file use BOM (if utf8) or not?
5. what are your seperators?
6 are text fields encapsulated in doublequotes?

Can you attach the csv file (header, first line of (anonymized) data)?

Olaf

RonP · Post by **RonP** » 2023-09-04 10:04

Hi Olaf,
Thank you for your reply.
My answers and some screenshots and a test import file
(Strange thing is: I have another AppGini application running and no problem there with importing records, zo I think I miss something , blind spot or so)

1. are you using the "new" or the "old" CVS Import wizard

Both options: Same results, just one record imported out of 3

2. are you using some weird encoding (UTF-8 should probably work best)

No, CSV.MS-DOS, just as my other AppGini application

4. does your file use BOM (if utf8) or not?

No, I have windows

5. what are your separatorâ€™s?

I adjust , for ; and leave ""

6 are text fields encapsulated in double quotes?

No, just as my other AppGini application

To be imported:

Code: Select all

Id;Soortaanvraag_Id;Woningtype_Id;Voornaam;Achternaam;Tussenvoegsel;Adres;Huisnummer;Datum Aangevraagd;Postcode;Actie_Id;Telefoon;Email;Creatiedatum;Creator;Opmerking
102;1;9;A ;Name-1;;Street;1;2023-09-01;9999XC;1;06-12345678;[email protected];2023-09-01;joop;Ik wil de mogelijkheid onderzoeken om mijn woning (met een Brink luchtverwarmingsysteem) te verwarmen met een warmtepomp met het bestaande luchtverwarmingsysteem als warmteafgifte medium. Tevens oriâ€°nteren op het verder luchtdicht maken van het huis, ventilatie en verdere isolatie.
103;1;3;B;Name-2;;Street;2;2023-08-31;9999CT;1;06-12345678;[email protected];2023-08-31;joop;Wij willen kijken waar we de meeste warmte mee verliezen. Aan de hand van de uitkomst van het onderzoek willen we gaan investeren om ons huis meer energie vriendelijk te maken.
104;1;8;C;Name-3;;Street;4;2023-08-30;9999RB;1;06-12345678;[email protected];2023-08-30;joop;Ik wil weten wat er voor mijn situatie nodig is om van het gas af te kunnen

Hereby some screenshots and the file with the 3 records and
the table lay-out

Code: Select all

CREATE TABLE `aanvragen` (
  `Id` int(11) NOT NULL,
  `Woningtype_Id` smallint(11) NOT NULL DEFAULT 1,
  `Actie_ID` smallint(3) NOT NULL DEFAULT 1,
  `Datum_aangevraagd` date NOT NULL,
  `Soortaanvraag_Id` smallint(6) NOT NULL,
  `Voornaam` varchar(25) NOT NULL,
  `Tussenvoegsel` varchar(25) DEFAULT NULL,
  `Achternaam` varchar(50) NOT NULL,
  `Adres` varchar(75) NOT NULL,
  `Huisnummer` int(3) NOT NULL,
  `Toevoeging` varchar(40) DEFAULT NULL,
  `Woonplaats` varchar(50) NOT NULL DEFAULT 'Heiloo',
  `Postcode` varchar(6) NOT NULL,
  `Telefoon` varchar(30) NOT NULL,
  `Email` varchar(100) NOT NULL,
  `Dossier` varchar(255) DEFAULT NULL,
  `Link_pCloud` varchar(255) NOT NULL,
  `Mutator` varchar(50) DEFAULT NULL,
  `Opmerking` text NOT NULL,
  `Mutatiedatum` date DEFAULT NULL,
  `Creator` varchar(50) DEFAULT NULL,
  `Creatiedatum` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;

--
-- Indexen voor tabel `aanvragen`
--
ALTER TABLE `aanvragen`
  ADD PRIMARY KEY (`Id`),
  ADD UNIQUE KEY `Link_pCloud_unique` (`Link_pCloud`),
  ADD UNIQUE KEY `Datum_aangevraagd` (`Datum_aangevraagd`,`Achternaam`,`Adres`) USING BTREE,
  ADD UNIQUE KEY `Huisnummer` (`Huisnummer`,`Toevoeging`,`Postcode`,`Soortaanvraag_Id`),
  ADD UNIQUE KEY `Dossier` (`Dossier`),

onoehring · Post by **onoehring** » 2023-09-04 12:53

Hi,

did you try to input some records, export those as CSV and reimport them? Same problem?

Just a thought: Shouldn Woningtype_Id, Actie_ID and Soortaanvraag_Id not contain "real" values instead of references only? I thought AG is taking care of setting the correct references.

Olaf

RonP · Post by **RonP** » 2023-09-04 13:46

Olaf,

I've always used the references also in the other application. I think it should be of no matter.

Exporting records from AppGini to a CSV gives records like

Code: Select all

Id,"Voornaam","Tussenvoegsel","Achternaam","Woningtype_Id","Soortaanvraag_Id","Adres","Huisnummer","Toevoeging","Datum_aangevraagd","Dossier","Actie_ID","Woonplaats","Postcode","Telefoon","Email","Link_pCloud","Opmerking","Mutator","Mutatiedatum","Creator","Creatiedatum"							
104,"C","","Name-3","Hoekwoning","QSE","Street","4","","30-08-2023","","N.v.t","Heiloo","9999RB","06-12345678","[email protected]","","Ik wil weten wat er voor mijn situatie nodig is om van het gas af te kunnen","","","joop","30-08-2023"

As I re-import that file than I get this: (so "" will be imported as well) (The field mapping is allright!)

Code: Select all


"104	""C""	""""	""Name-3""	""Hoekwoning""	""QSE""	""Street""	""4""	""""	""30-08-2023""	""""	""N.v.t""	""Heiloo""	""9999RB""	""06-12345678""	""[email protected]""	""""	""Ik wil weten wat er voor mijn situatie nodig is om van het gas af te kunnen""

I don't see how I can achieve this with Save As from .XLSX

onoehring · Post by **onoehring** » 2023-09-05 05:14

Hi,

interesting.
When I clicked on a CSV export, even the ID (autonumber) is exported in double quotes.

For saving from XLS, I want to recommend the free XLToolbox. This can (also) save CSV with options: https://www.xltoolbox.net

Did you try to import on a new testserver? Maybe this turns out good and could hint that regenerating and reuploading is worth it. Just an idea.
Olaf

RonP · Post by **RonP** » 2023-09-19 15:01

Hi,
Back after some while.
I've picked up my importing problem again.
Same table definitions

Whatever for .csv format I try to import, the record will be skipped.
The data is valid unless I oversee something
It doesn't matter if I import as a user or in the "admin environment"

In the meanwhile: I use V2315 now, V2314 didnâ€™t work as well
Ron
@Onoehring:
I've tried to use toolbox, however I had some installing troubles with Excel 2019 and it took me to much time to find out what is went wrong

RonP · Post by **RonP** » 2023-09-26 13:44

Hi Anyone a clue for me?

I figured this out:
There are just 3 records in my .CSV file.
These records are obtained from a Wordpress plugin Gravity Forms -->GravityLite Export as a .CSV File
(It doesnâ€™t matter if the export file is a .XLSX and later on saved as a .CSV (Windows)

Appgini just import the first record !
If I place the third record as the first, just that record wil be imported.

So, what is the logic ???

The mapping of fields to be imported:

Voornaam
Tussenvoegsel
Achternaam
Woningtype_Id
Soortaanvraag_Id
Adres
Huisnummer
Toevoeging
Datum_aangevraagd
Opmerking

Once more the table definition:

Code: Select all

Field	AppGini definition	Current definition in the database	
 
Aanvragen
Id				INT(11) 		NOT NULL PRIMARY KEY AUTO_INCREMENT	INT(11) not null primary key auto_increment	
Voornaam			VARCHAR(25) 	NOT NULL							VARCHAR(25) not null	
Tussenvoegsel		VARCHAR(25) 	NULL							VARCHAR(25)	
Achternaam		VARCHAR(50) 	NOT NULL							VARCHAR(50) not null	
Woningtype_Id		SMALLINT(11) 	NOT NULL 	DEFAULT '1'			SMALLINT(11) not null default '1'	
Soortaanvraag_Id	SMALLINT(6) 	OT NULL							SMALLINT(6) not null	
Adres			VARCHAR(75) 	NOT NULL							VARCHAR(75) not null	
Huisnummer		INT(3)		NOT NULL							INT(3) not null	
Toevoeging		VARCHAR(40) 	NULL							VARCHAR(40)	
Datum_aangevraagd	DATE 		NOT NULL							DATE not null	
Dossier			VARCHAR(255) 	NULL UNIQUE						VARCHAR(255) unique	
Actie_ID			SMALLINT(3)	NOT NULL 	DEFAULT '1'			SMALLINT(3) not null default '1'	
Woonplaats		VARCHAR(50) 	NOT NULL 	DEFAULT 'Heiloo'		VARCHAR(50) not null default 'Heiloo'	
Postcode			VARCHAR(6) 	NOT NULL							VARCHAR(6) not null	
Telefoon			VARCHAR(30) 	NOT NULL							VARCHAR(30) not null	
Email			VARCHAR(100) 	NOT NULL							VARCHAR(100) not null	
Link_pCloud		VARCHAR(255) 	NOT NULL UNIQUE					VARCHAR(255) not null unique	
Opmerking		TEXT 		NOT NULL							TEXT not null	
Mutator			VARCHAR(50) 	NULL							VARCHAR(50)	
Mutatiedatum		DATE 		NULL							DATE	
Creator			VARCHAR(50) 	NULL							VARCHAR(50)	
Creatiedatum		DATE 		NULL							DATE

ron

onoehring · Post by **onoehring** » 2023-09-27 05:32

Hi Ron,

so did you solve the problem (you wrote you "figured it out")?
If not, maybe you could send me the AG file (clipped to only that table) and the data once more. I would try to recreate the effect (but I can do this only until Friday, or after Oct. 16th)

OLaf

RonP · Post by **RonP** » 2023-09-27 08:29

Olaf,
Bit confusing indeed "figured it out"
What I meant was that just the first record will be imported, and none of the others.

Very kind of you to dig in it.
Ron

pbottcher · Post by **pbottcher** » 2023-09-27 19:32

Hi Ron,

is it possible that you do not have a value in the field Link_pCloud?
This field seems to be unique field and if you do not have a different value per row, only one row will be imported.

I didn't see the field in the first dataset with the 3 records and no value in the second one with only one record.

onoehring · Post by **onoehring** » 2023-09-28 04:17

Hi pbÃ¶tcher,

funny. Taking a look at the data he sent me, I came to the same assumption.
Let's what if he can confirm this.

Olaf

RonP · Post by **RonP** » 2023-09-28 09:39

Hi Olaf and pbÃ¶tcher

Thank you so much !
Indeed, wrong field definition within the table.
The reason why I donâ€™t import that field is that the value must be obtained from a cloud directory.
At "importing time" that is unknown yet.

So the one who is working with this application has to look for himself for the right http:// address.
Andâ€¦. This should be a unique value aswell.

How to solve this â€œproblemâ€ is something else.
For now I can live with it and can explain to the user, just one

, what to do.
Ron

AppGini Community Forums

Import csv

Import csv

Re: Import csv

Re: Import csv

Re: Import csv

Re: Import csv

Re: Import csv

Re: Import csv

Re: Import csv

Re: Import csv

Re: Import csv

Re: Import csv

Re: Import csv

Re: Import csv