Import split columns

xlsgen > overview > Import split columns

Sometimes the data being imported has more than just one piece in each cell. xlsgen has a mechanism for making it often possible to process it correctly.

Let's take an example of a data table including a person's name. This name often differentiates the first and last name in different columns, ie :

Data with first and last names in separate columns

But sometimes both are in the same column, separated by a special character, here a comma :

Data with both first and last names in the same column

In the latter case, xlsgen can import the data in separate columns. The mechanism is available for all import file formats, ie CSV, XML, JSON and HTML.

VB code


' import a CSV file, split Name column in two First name and Last name columns

Dim wbk As xlsgen.IXlsWorkbook
Set wbk = engine.New("ResultingFile.xlsx")

Dim wksht As xlsgen.IXlsWorksheet
Set wksht = wbk.AddWorksheet("Sheet1")

Dim cs As xlsgen.IXlsImportColumnSplitter
Set cs = wksht.Import.CSV.Options.ColumnSplitByName("Name")

cs.SplitBySeparator = ","
cs.AddColumn("First name")
cs.AddColumn("Last name")

wksht.Import.CSV.ImportFile ("datasource.csv")

Other than splitting based on a special character, the split can be based on a string length. For instance, if you know the data is made of 3 pieces of 10 characters each, you can set SplitByLength to 10.

Each split column can have its own style and number format. This is specified in the IXlsImportColumnSplit interface.