2. PHPRunner General questions
  3. [SOLVED]  CSV-File without quotation marks
This topic is locked
[SOLVED]

 CSV-File without quotation marks

 		3/19/2021 7:53:27 AM
PHPRunner General questions
T
 taumic author

Hello,

I am using PHPR Enterprise V 10.5 / 36834.
I want to create a CSV file in a certain format.

Among other things without writing the texts or fields in quotation marks. But this is exactly what PHPR does.

I have read that you can change this in a PHPR file, but I don't want to do that, because otherwise you always have to remember to follow this (update/reinstall). At this point I would like a switch to be able to configure this in PHPR.
To get around this problem I would like to create my "own" CSV file.

When I execute the SQL statement in e.g. Maria-DB, I get my desired CSV file.

If I put it in the server part of a button, I get neither an error code in the syntax check nor a CSV file created. The command is not executed, no matter if with DB::Exec($SQL) or with DB::Query($SQL).
Please give me a nudge in the right direction, I guess I can't see the forest for the trees?
Here is the SQL command in the server-part of a button to create the CSV file:



$sql = "

SELECT 'B_EBBuchung','B_Egkonto','Beleg','Bereich','Betrag','BetragN','BetragS','BuchText','BuErfDatum','Datum','ExterneOPNummer','FADatum','GKonto','InterneOPID','KAKENN','KBKENN','Konto','Programm','REDatum','SKBETRAG','SKCODE','SKPROZ','START','STCODE','STPROZ','USTIDNR','WaWiBeleg'

UNION

SELECT

B_EBBuchung,

B_Egkonto,

Beleg,

Bereich,

FORMAT(Betrag, 2 ,'de_DE') AS Betrag,

FORMAT(BetragN, 2 ,'de_DE') AS BetragN,

FORMAT(BetragS, 2 ,'de_DE') AS BetragS,

BuchText,

BuErfDatum,

DATE_FORMAT(Datum, '%d.%m.%Y') AS Datum,

ExterneOPNummer,

DATE_FORMAT(FADatum, '%d.%m.%Y') AS FADatum,

GKonto,

InterneOPID,

KAKENN,

KBKENN,

Konto,

Programm,

DATE_FORMAT(REDatum, '%d.%m.%Y') AS REDatum,

SKBETRAG,

SKCODE,

SKPROZ,

START,

STCODE,

STPROZ,

USTIDNR,

WaWiBeleg

INTO OUTFILE 'fibu.csv'

FIELDS TERMINATED BY ';'

LINES TERMINATED BY '\n'

FROM crm_fibu" ;
DB::Exec($sql) ;


Thanks for your help
Michael
D
 		DealerModulesDevClub member 3/19/2021



Hello,

I am using PHPR Enterprise V 10.5 / 36834.
I want to create a CSV file in a certain format.

Among other things without writing the texts or fields in quotation marks. But this is exactly what PHPR does.

I have read that you can change this in a PHPR file, but I don't want to do that, because otherwise you always have to remember to follow this (update/reinstall). At this point I would like a switch to be able to configure this in PHPR.
To get around this problem I would like to create my "own" CSV file.

When I execute the SQL statement in e.g. Maria-DB, I get my desired CSV file.

If I put it in the server part of a button, I get neither an error code in the syntax check nor a CSV file created. The command is not executed, no matter if with DB::Exec($SQL) or with DB::Query($SQL).
Please give me a nudge in the right direction, I guess I can't see the forest for the trees?
Here is the SQL command in the server-part of a button to create the CSV file:



$sql = "

SELECT 'B_EBBuchung','B_Egkonto','Beleg','Bereich','Betrag','BetragN','BetragS','BuchText','BuErfDatum','Datum','ExterneOPNummer','FADatum','GKonto','InterneOPID','KAKENN','KBKENN','Konto','Programm','REDatum','SKBETRAG','SKCODE','SKPROZ','START','STCODE','STPROZ','USTIDNR','WaWiBeleg'

UNION

SELECT

B_EBBuchung,

B_Egkonto,

Beleg,

Bereich,

FORMAT(Betrag, 2 ,'de_DE') AS Betrag,

FORMAT(BetragN, 2 ,'de_DE') AS BetragN,

FORMAT(BetragS, 2 ,'de_DE') AS BetragS,

BuchText,

BuErfDatum,

DATE_FORMAT(Datum, '%d.%m.%Y') AS Datum,

ExterneOPNummer,

DATE_FORMAT(FADatum, '%d.%m.%Y') AS FADatum,

GKonto,

InterneOPID,

KAKENN,

KBKENN,

Konto,

Programm,

DATE_FORMAT(REDatum, '%d.%m.%Y') AS REDatum,

SKBETRAG,

SKCODE,

SKPROZ,

START,

STCODE,

STPROZ,

USTIDNR,

WaWiBeleg

INTO OUTFILE 'fibu.csv'

FIELDS TERMINATED BY ';'

LINES TERMINATED BY '\n'

FROM crm_fibu" ;
DB::Exec($sql) ;


Thanks for your help
Michael


Hi Michael,
I am not sure if this will help but I know to change the view you would use:
$value = str_replace(","," ",$value);
This would change all commas into spaces.
Also...
I see in the manual creating an export:
To access the Export page settings, proceed to the Choose pages screen and click the Settings button next to the Export page checkbox. To enable the Import page, click the Import page checkbox on the same screen.


These are the Export page settings:
(maybe changing the "Field seperator in CSV format: from , to a space)


The Export page is opened in a popup by default. This is how it looks like in the generated application:


The supported file formats are:
•Excel 2007 (.xlsx);
•Word (.doc);
•CSV (comma separated values).
The field labels are used as the headers for the Excel/Word files. The field names are used as the headers for the CSV files so that the exported file can be imported into other software.

Paul
T
 		taumic author 3/19/2021

Hello Paul,
thank you very much for your time.

But the quotation marks ( " ) are not part of the data in my opinion and thus cannot be replaced by $value, or am I wrong?

You could use a text editor at the end of the process to replace all " in blanks - but that is not a solution in this case.

And in the settings you can't deselect the quotation marks ( " ) either.

The quotation marks ( " ) are set BEFORE and AFTER each field content of PHPR when exporting as CSV.
Best regards
Michael
A
 		acpan 3/20/2021

As a first step, I would check if MySQL user that PHPRunner uses to connect to MySQL DB has global File permissions granted in MySQL.
The key step is to be able to view the SQL error in your Server event, you can store it in as a var and view in Client After event:
Client Before Event:



return true;


Server Event:



//Note: the file MUST be removed first before export, else export will fail.
$result["sql"] = " your select statement ..." ;

DB::Exec($result["sql"]);

if ( DB::LastError() ) {

$result["sql_err"]=DB::LastError().", SQL=".$result["sql"];

}


Client After Event:



var sql_err = result["sql_err"] ;

alert(sql_err);


The SELECT to outfile method using the above works fine, but handling the exported file permission is tricky, as it is protected by MySQL and may be more troublesome in the end to manage the download and delete the old files.
Hope it helps.
A
 		acpan 3/20/2021

Another way, better way IMO, save all trouble and just do a small change for project_folder/classes/exportpage.php:
For the header:



// write header

$headerParts = array();

foreach( $this->selectedFields as $field )

{



// !! Add this !!

if ($_SESSION["my_csv"] == 1 ) {

$headerParts[] = str_replace( '"', '""', $field);

} else {

$headerParts[] = '"'.str_replace( '"', '""', $field).'"';

}

...



}


For the data:



foreach( $this->selectedFields as $field ) {



// Add this codes

if ($_SESSION["my_csv"] == 1 ) {

$dataRowParts[] = str_replace( '"', '""', $values[ $field ] );

}

else {

$dataRowParts[] = '"'.str_replace( '"', '""', $values[ $field ] ).'"';

}
.....


You can now switch between your custom csv format and normal csv format, to use the custom csv format, set $_SESSION["my_csv"] to 1 before export, add the codes as follows:
in After App Initialized event: $_SESSION["my_csv"] = 0;

Export Page, Before Export Process event: $_SESSION["my_csv"] = 1;
T
 		taumic author 3/22/2021

Hello all,
thank you very much for your efforts.

The places in the file "exportpage.php" are known to me. The idea with the control SESSION is very good, but even with that I have to keep an eye on the file "exportpage.php" at every update, which I want to avoid.
I think I will create the export file externally.
Thanks a lot and stay healthy!
Michael