Importing Excel data with Danish characters into Azure SQL database

Question

Importing Excel data with Danish characters into Azure SQL database

Peter Grandjean 30

I am having trouble importing Excel data with Danish characters like æ, ø, and å into Azure SQL database. I have tried saving the data as CSV and using the SQL Server Import extension, but the special characters get converted into strange signs. I have also attempted saving the Excel file as TXT and CSV, but to no avail.

I have read online to select the correct code page for the source data during import, specifically, code page for Western European (Windows) - Codepage 1252. However, I cannot find where to select the codepage during import. Can someone please help me out? Thank you.

SSingh-MSFT 16,371 Reputation points Moderator

2023-10-05T12:59:11.5966667+00:00

Hi
Peter Grandjean,

Welcome to Microsoft Q&A forum.

As I understand, you could not import excel data into Azure SQL Database.

Could you please share sample data present inside the excel so that we can reproduce and check at our end?

Also, please let us know the way with which you are importing data to Database? Is it through Azure Data Factory or through some other method.

Thanks
SSingh-MSFT 16,371 Reputation points Moderator

2023-10-09T04:12:15.0833333+00:00

Hi
Peter Grandjean,

Just checking if you got a chance to look at the replies here.

Thanks
Peter Grandjean 30 Reputation points

2023-10-09T05:50:49.68+00:00

Good morning ShaktiSingh and Alberto.
I have taken some steps forward in the process. Thank you for your assisance so far.
I mananged to save the Excel files in UTF-8 format. I installed the MSSS and can import that way. However, I still have some challenges. I will report back to you when I have solved (or not solved !) these challenges.
Med venlig hilsen
Peter
Peter Grandjean 30 Reputation points

2023-10-10T07:20:01.36+00:00

Good morning once again.
The next issue is Dates.
I have dates in my excel file. They are configured as dates in the local Danish format "23-12-2022". When importing into sql I use the format Date. The field in the table now shows "2022-12-23", thus I assume it is recoignized as a date.
When I create a pivottable in Excel, the date is now shown as "2022-12-23". But I am not able to convert the format to my Danish dd-mm-yyyy. And I am not able to group the dates. However, if i add 1 to the cell I have the date "24-12-2022".
Can you help me here ?
Med venlig hilsen
Peter
Alberto Morillo 34,671 Reputation points MVP Volunteer Moderator

2023-10-10T20:13:31.43+00:00

Would you consider to create a new column on the table named DanishDate of type Date then run the following UPDATE statement on SQL Server Management Studio or Azure Data Studio:

UPDATE YourTable

SET DanishDate = CAST(LocalDanishFormatColumn as DATE)

WHERE LocalDanishFormatColumn is not null

Accepted answer

1 additional answer

Your answer

SSingh-MSFT 16,371 Reputation points Moderator

2023-10-05T12:59:11.5966667+00:00

Hi
Peter Grandjean,

Welcome to Microsoft Q&A forum.

As I understand, you could not import excel data into Azure SQL Database.

Could you please share sample data present inside the excel so that we can reproduce and check at our end?

Also, please let us know the way with which you are importing data to Database? Is it through Azure Data Factory or through some other method.

Thanks
SSingh-MSFT 16,371 Reputation points Moderator

2023-10-09T04:12:15.0833333+00:00

Hi
Peter Grandjean,

Just checking if you got a chance to look at the replies here.

Thanks
Peter Grandjean 30 Reputation points

2023-10-09T05:50:49.68+00:00

Good morning ShaktiSingh and Alberto.
I have taken some steps forward in the process. Thank you for your assisance so far.
I mananged to save the Excel files in UTF-8 format. I installed the MSSS and can import that way. However, I still have some challenges. I will report back to you when I have solved (or not solved !) these challenges.
Med venlig hilsen
Peter
Peter Grandjean 30 Reputation points

2023-10-10T07:20:01.36+00:00

Good morning once again.
The next issue is Dates.
I have dates in my excel file. They are configured as dates in the local Danish format "23-12-2022". When importing into sql I use the format Date. The field in the table now shows "2022-12-23", thus I assume it is recoignized as a date.
When I create a pivottable in Excel, the date is now shown as "2022-12-23". But I am not able to convert the format to my Danish dd-mm-yyyy. And I am not able to group the dates. However, if i add 1 to the cell I have the date "24-12-2022".
Can you help me here ?
Med venlig hilsen
Peter
Alberto Morillo 34,671 Reputation points MVP Volunteer Moderator

2023-10-10T20:13:31.43+00:00

Would you consider to create a new column on the table named DanishDate of type Date then run the following UPDATE statement on SQL Server Management Studio or Azure Data Studio:

UPDATE YourTable

SET DanishDate = CAST(LocalDanishFormatColumn as DATE)

WHERE LocalDanishFormatColumn is not null

Answer 1

Alberto Morillo 34,671 MVP Volunteer Moderator

Please save the text file again as CSV then convert that CSV file to UTF-8 format using below steps.

Open the CSV file with Notepad++ by right-clicking on the file and choosing Edit with Notepad++.
In Notepad++, choose Encoding > Convert to UTF-8 without BOM. This will change the encoding of the file to UTF-8 without adding any extra bytes at the beginning of the file.
Save the file and close Notepad++.

You can try now to import the CSV file to Azure SQL again.

Answer 2

Peter Grandjean 30

I changed the format from Date to DateTime in the sql db.

I seems to have solved my problem.

Thank you for now

Peter

Alberto Morillo 34,671 Reputation points MVP Volunteer Moderator

2023-10-12T12:27:04.1+00:00

I am glad to hear that Peter. Have a great day!

Share via

Importing Excel data with Danish characters into Azure SQL database

1 additional answer

Your answer