Custom delimiters in Data Transfer

Unfortunately, oledb connection completely ignores any custom delimiter. Our solution for this problem involves some manual setup. You will need to add a Schema.ini file, which needs to be in same folder as the file you are trying to import.

Example: [NAME_OF_YOUR_FILE.txt] Format=Delimited(|) ColNameHeader=True MaxScanRows=1000 Character=OEM

The first entry in Schema.ini is always the name of the text source file enclosed in square brackets. The following example illustrates the entry for the file Sample.txt: [Sample.txt] Format: The Format option in Schema.ini specifies the format of the text file.Format=Delimited(custom character) ColNameHeader: Defines if the file contains headers MaxScanRows: How many rows in the file are scanned. Character: You can select from two character sets: ANSI and OEM. The following example shows the Schema.ini entry that sets the character set to OEM: Character=OEM

If you have multiple files you will need to import, they will need to be in different folders. Click here to download template for Schema.ini

---

If the content of the column is being truncated. That means that the Jet database engine might not correctly determine the data type of each column, which could result in data truncation. In that case, you can use the Schema.ini file to specify the format of text data when you're using the Microsoft Jet database engine to import or link to the data. Especially pay attention to the MaxScanRows setting in the Schema.ini file. This setting scans rows to determine the data type and size of that column. If it is set to a low number, the Jet database engine might not correctly determine the data type of each column, which could result in data truncation. Here's an example of a Schema.ini file for a CSV file:

[YourFileName.csv] Format=CSVDelimited ColNameHeader=True MaxScanRows=0 CharacterSet=OEM Col1=ID Long Col2=Name Text Col3=Age Long Col4=Salary Double

In this example: • YourFileName.csv is the name of your CSV file. • Format=CSVDelimited specifies that the file is a CSV file. • ColNameHeader=True indicates that the first row of the file contains column names. • MaxScanRows=0 tells the driver to scan all rows (not just a subset) to guess the data type of each column. • CharacterSet=OEM specifies the character set. OEM is a common choice for English language Windows operating systems. • Col1=ID Long, Col2=Name Text, Col3=Age Long, Col4=Salary Double define the columns in the file. Each column is given a name and a data type (Long, Text, Double). Please replace YourFileName.csv and the column definitions with your actual file name and columns.