Create Csv File with Header in Mysql

Question

I want to create a CSV outputfile with headers using the following code.

Select 'Erheüößbungsstelle', 'Bevorzugtes PLZ Gebiet','alternatives PLZ 
Gebiet','Vorname','Nachname','Geburtsdatum'
Union All  
Select  mitarbeite.region 
        , mitarbeite.preferred_regions_primary
        , mitarbeite.preferred_regions_secondary
        , mitarbeite.firstname
        , mitarbeite.lastname
        , mitarbeite.birthday
from mitarbeite
where mitarbeite.region = 'Kiel' 
Into Outfile'd:/TransferMysql/Kiel20211115.csv' 
FIELDS TERMINATED BY ';' 
LINES TERMINATED BY '
';

The problem is that the output format is UTF8 and Excel does not display the file with special characters, because Excel expects Windows(Ansi). So I have recreated the table.

CREATE TABLE `erhebungsbeauftragtemain` (
  `uid` int NOT NULL AUTO_INCREMENT,
  `region` varchar(200) CHARACTER SET cp1250 COLLATE cp1250_general_ci NOT NULL DEFAULT '',
  `preferred_regions_primary` varchar(200) CHARACTER SET cp1250 COLLATE cp1250_general_ci ,
  `preferred_regions_secondary` varchar(200) CHARACTER SET cp1250 COLLATE cp1250_general_ci '',
  `firstname` varchar(250) CHARACTER SET cp1250 COLLATE cp1250_general_ci ,
  `lastname` varchar(250) CHARACTER SET cp1250 COLLATE cp1250_general_ci ,
  `birthday` varchar(20) COLLATE cp1250_general_ci DEFAULT '0',
 PRIMARY KEY (`uid`)
) ENGINE=InnoDB AUTO_INCREMENT=41 DEFAULT CHARSET=cp1250 COLLATE=cp1250_general_ci;

When I run the select under the UNION, Excel displays all special characters correctly. When I run the Complete Select, I get the following error message:

    Error Code: 1267. Illegal mix of collations (utf8mb4_0900_ai_ci,COERCIBLE)

 and (cp1250_general_ci,IMPLICIT) for operation 'UNION' 0.000 sec

I already tried with:

Select Convert('Erheüößbungsstelle' USING cp1250), .....

The CSV file is created, but the header in Excel does not display special characters.

Create Csv File with Header in Mysql

0 Answers