Converting Excel into shapefile (keeping encoding and data type)

Question

I am trying to create a "perfect" shapefile from the Excel data. "Perfect" means that it should match several factors: proper encoding and original data type.

Excel file basically contains information about point objects and includes five attributes with required types.

Attribute | Type | Description
--------------------------------------------------------------------------------
FFA_NR | integer | Object serial number
FFA_TXT | text | Object serial number but with leading zeros
Name | text | Object name
X | real | X-coordinate in Gauß-Krüger CS
Y | real | Y-coordinate in Gauß-Krüger CS

where an X,Y-tuple is based on DHDN / 3-degree Gauß-Krüger zone 5 EPSG:31469

Data sample looks as following (demonstrated in the image below).

Excel_Data

My workflow (similar to this Importing Spreadsheets or CSV files.) so far is

1. In Excel, Data > Save As...
2. I am trying with .xlsx, .txt(MS-DOS) or .csv(MS-DOS)
3. In QGIS everything visualized nicely in the sense that it is placed correctly. I have done it with several approaches, Layer > Add Layer > Add spreadsheet layer and Add Delimited Text Data
4. Afterwards, in QGIS, I am saving the opened point layer as a shapefile with Save vector layer as...
5. Then in the Attribute table, I do have several issues with data encoding and data type

---

1. Data Type

When I import Excel data into QGIS I am partially losing my initial data types. For instance, instead of the desired 035, I do have 35. Obviously, it is about the import data type.

I have seen similar questions on this topic.

• Changing CSV layer attribute value from text to number in QGIS?
• How to keep leading zeros in csv file when working with QGIS
• Keeping leading zeroes in QGIS in big files
• How to format labels with leading zeroes?
• Joining .csv file to a layer in QGIS [closed]
• How to Specify Data Types of CSV Columns for Use in QGIS

However, I can not understand how to create the file with .csvt extension. What is the workflow? How to create it and where it should be stored?

Of course, in Attribute table I can create a new column, appropriated with a text type, and then proceed with a Filed Calculator, using for instance lpad("FAA_Nr", 3, '0').

---

2. Encoding

I am working with German text, that contains letters like ä, ö, ü, ß. When in QGIS, I apply any of the encoding formats (System, UTF-8, ISO-8859-1, ISO-8859-15), none of my specific characters can be visualized.

Check the following graphics for UTF-8 and ISO-8859-15 accordingly.

UTF-8 ISO

I have found related topics,

• Exporting from Excel to csv and import in QGIS retaining encoding/special characters with workaround?
• Converting Hebrew Excel file into Shapefile in QGIS

However, I did not discover anything spectacular in the context of my issue. I am guessing that the problem is more with the Excel rather then QGIS, is not it?

Or maybe I apply the encode formatting on a wrong step. When should it be done?

---

Target: optimizing the efforts required for the data input in QGIS.

• user30184: When it comes to GDAL .csvt file, you can write it with any text editor that you like and save it into the same directory than the csv file gdal.org/drv_csv.html. GDAL can also create a template for you if your GDAL has support for .xls gdal.org/drv_xls.html or .xlsx gdal.org/drv_xlsx.html. ogr2ogr -f csv -lco CREATE_CSVT=YES output.csv input.xls(x)
• bugmenot123: Are you certain that your Excel exports in UTF-8? Did you choose UTF-8 in QGIS at the initial import step? Try importing the Excel sheet directly in QGIS too, it might just work. CSV is an incredibly stupid format.
• Taras: @user30184, Can I use an already created .csvt file with a new csv, of course keeping in mind that the attributes and their types are the same, only values are different. I doubt regarding the invisible relation, which exists between .csvt file and a csv. Or it only important for GDAL(QGIS), and nothing to do with OS? Are there any requirements, besides one that .csvt file and a csv should be stored in one folder?
• Taras: @bugmenot123, I will give a try one more time with UTF-8 in Excel. I just do not have that option to asign the encoding system for my txt file. Yeap, I aplied UTF-8 in QGIS at the initial import step.
• csk: Just a thought, does it help if you go into Options, override the system locale and change the language to Deustch? Perhaps that would change the import settings to accommodate German special characters.

Answer 1

You can re-use existing csvt files. There is no invisible relation, "filename.csv" is connected by the base filename with type file "filename.csvt".

Save these files and make a test:

test.csv

001,002

test.csvt

"String","Integer"

Now run ogrinfo

ogrinfo test.csv -al
INFO: Open of `test.csv'
 using driver `CSV' successful.
Layer name: test
Geometry: None
Feature Count: 1
Layer SRS WKT:
(unknown)
field_1: String (0.0)
field_2: Integer (0.0)
OGRFeature(test):1
 field_1 (String) = 001
 field_2 (Integer) = 2

If you have file "test2.csv" with the same structure just copy or rename test.csvt into test2.csvt.

Answer 2

A quick and dirty way to get the right datatype (like keeping 035 as text to prevent the loss of leading 0) is to create a dummy record as the first line in the excel where all data are of the right type and of the right length (so to hold 035 as text fill with AAA for exemple) this way when you import it the right type can be autoattributed. (just dont forget to remove the dummy line after importing)

Answer 3

edit: I just saw the QGIS 2.0 tag. I verified and the .csv import dialog in 2.18 doesn't seem to have the detect field type tickbox so this might not work outside of 3.x

There might be something slightly specific with your data, but I just tried a quick method and it worked for me:

• In Excel, select the entire column where you want leading zeros and set it to Text from the dropdown in the main tab
• Save the file as .csv (comma separated value, not MS-DOS).
• In QGIS, add Delimited Text data: specify System encoding and uncheck the Detect field types tickbox
• Now all fields should be set as string type so the leading zeros should be present in the needed field
• From the processing toolbox, find the Refactor Fields tool and set the correct field types for the other ones

After this you should have a new layer with the correct field types, the leading zeros, and special characters.

enter image description here

enter image description here

• Taras: Thank you so much for the response! Unfortunately, I am not deploying QGIS > 3 version that much.
• Taras: However, your hint about not MS-DOS does the job.

Answer 4

There is an extension called Spreadsheet Layers. This allows you to add Excel spreadsheets directly. And if required, you can save them via QGIS export function as SHP file.

QGIS Python Plugins Repository

QGIS-SpreadSheetLayers GitHub Repository