Joomla 5 Notice

We are pleased to announce that as of January 29, 2024, all of our Joomla extensions are compatible with Joomla 5.

For all who are still updateing from Joomla 3 to Joomla 4: Joomla 4 Migration instructions are available here:

There is now a separate Documentation for Visforms for Joomla 4 and for Visforms for Joomla 5!

Forum

Visforms Subscription user can ask questions in our forum. Please log in with the relevant user first.
Everybody can access the forum for reading.

Please only ask 1 question per topic.

Important information for almost every question:
V1: Which Visforms version is running?
V2: Which Joomla version is running?
V3: Which PHP version is running?

Import data into forms

More
1 month 1 day ago #10731 by rusasadmins
Import data into forms was created by rusasadmins
I need to import 1,000 records into a form that I have created.

I have a csv file
I have created the form

I manually entered a few rows into the form, so I could see how the data sits.

Here is an example of the data via sql:
Code:
(2, 1, '2024-09-04 14:50:28', 160, NULL, NULL, '165.230.225.214', '0000-00-00 00:00:00', 0, 0, '01:013:111', 'The Bible in Aramaic', '3', 'HST\0, AHp', 'AMESALL: African, Middles Eastern, and South Asian Languages and Literature', 'Yes', NULL)
For the field with 'HST\0, AHp', since \0 isn't a printable character, how can I format my data so I can import into the field that has a multiple select values?

I'm using ROCSVI to run the import, which it can do easily my only issue is the field with multiple values.

Can you tell me how I can do this? I really don't want to do it manually.

thanks,
Laura

Code:
 

More
1 month 4 hours ago - 1 month 4 hours ago #10735 by Administrator IV
Replied by Administrator IV on topic Import data into forms
Hello Laura,

SQL statements are a possible and very good way to add any external data to a form as form data.
That is obviously your intention.

In order to be able to display non-printable characters in the SQL statement, MySQL has so-called:
'Special Character Escape Sequences'.
And that is exactly what you can see in your SQL example.

String Literals:
dev.mysql.com/doc/refman/8.4/en/string-literals.html

Re: since \0 isn't a printable character
No, \0 is a printable string literal, which you can read here.

\0 stands for the 'binary zero', and the 'binary zero' is the non-printable character.
\0 stands for a non-printable character.

An example:

For the listbox with multiple selection with field ID=636, the multiple selection of 'value2' and 'value3' is set using an SQL statement.
The following SQL statement is correct and is the only way it is possible.
In the database field, there is then a 'binary zero' directly after 'value2'.
A 'binary zero' in a database field is a normal case that occurs quite frequently.
Code:
update jx_visforms_60 set F636 = "value2\0, value3" where id = 1;

As I said, \0 is readable/writable and a 'binary zero', on the other hand, is the non-printable character.
If you work with readable, editable SQL statements (e.g. in a file), then there is no other option.

Working with a tool:

If your tool cannot handle non-printable characters (export and import with the same tool), you should
- Ask the tool provider.
- Use a different tool.

Other options 1:

All other options for developers lead more or less to the same thing.
A file is created from the existing data, in whatever way, which consists of a series of individual SQL statements.
Each individual SQL statement adds a record with the form data to the data table.
In the SQL statement, all non-printable characters are in a 'special character escape sequence'.
This file can then be read in and processed using common database editors or on the command line (see below).

Creating the readable file with the SQL statements, based on any external data table, is then an individual development task.

Other options 2:

Assuming the optimal case that the table structure of the source table and the form data table match, you can do the following.

- Export the table with mysqldump.exe
General:
mysqldump.exe [database-name] --result-file=[path-to-file]\[file-name].sql [source-tablename]
My example:
mysqldump.exe developj5 --result-file=C:\tmp\dump.sql jx_visforms_6

- Delete all lines in dump.sql that do not start with 'INSERT INTO'.

- Import the file with mysql.exe
General:
mysql -u [database-user] -p[password] [database-name] < I:\SandBox-IV\Java\mis\mis\sql\mis.sql
My example:
mysql -u devuser -p236598452XX developj5 < C:\tmp\dump.sql

The contents of dump.sql:
Code:
INSERT INTO `jx_visforms_60` VALUES (1,1,'2024-09-06 06:39:03',0,NULL,NULL,'172.20.0.1','0000-00-00 00:00:00',0,0,'value2\0, value3',NULL,NULL,'value-1\0, value-2 '); INSERT INTO `jx_visforms_60` VALUES (2,1,'2024-09-06 06:43:09',0,NULL,NULL,'172.20.0.1','0000-00-00 00:00:00',0,0,'value1\0, value3',NULL,NULL,'value-1\0, value-3');

The two new additional form data records are then read in correctly.
The multiple selection of the two list boxes is retained.


Best regards, Ingmar

:idea: I recommend you the new and up-to-date documentation for Joomla 4:
docs.joomla-5.visforms.vi-solutions.de/en/docs/
Most of this also applies retrospectively to Joomla 3.
Please only ask 1 question per topic :-).

:idea: Ich empfehle Dir die neue und aktuelle Dokumentation für Joomla 4:
docs.joomla-5.visforms.vi-solutions.de/docs/
Das meiste gilt rückwirkend auch für Joomla 3.
Bitte immer nur 1 Frage pro Thema stellen :-).
Last edit: 1 month 4 hours ago by Administrator IV.

More
1 month 4 hours ago #10736 by rusasadmins
Replied by rusasadmins on topic Import data into forms
Thank you very much, I will give this a try!

More
4 weeks 1 day ago #10737 by Administrator IV
Replied by Administrator IV on topic Import data into forms
Hello Laura,

you are very welcome.

Addendum:
The above example with the command line tools for MySQL ran on my local computer.
Depending on the provider package and what options it contains, the same thing works just as easily remotely.
Provided your provider supports it and the MySQL server is configured accordingly.

Connecting to the MySQL Server Using Command Options:
dev.mysql.com/doc/refman/8.4/en/connecting.html

How to connect to MySQL from the command line:
stackoverflow.com/questions/5131931/how-...rom-the-command-line

There are just 3 command line parameters added:
Code:
mysql -h [remote.example.com] -P [port-number] -u [user-name]
or in alternative notation:
Code:
mysql --host=[remote.example.com] --port=[port-number] --user=[user-name]

Best regards, Ingmar

:idea: I recommend you the new and up-to-date documentation for Joomla 4:
docs.joomla-5.visforms.vi-solutions.de/en/docs/
Most of this also applies retrospectively to Joomla 3.
Please only ask 1 question per topic :-).

:idea: Ich empfehle Dir die neue und aktuelle Dokumentation für Joomla 4:
docs.joomla-5.visforms.vi-solutions.de/docs/
Das meiste gilt rückwirkend auch für Joomla 3.
Bitte immer nur 1 Frage pro Thema stellen :-).

Moderators: Administrator AVAdministrator IV
Powered by Kunena Forum