We have found no workaround except manually changing the imput data. To do this you need to convert your data into an Excel table.We have found that using the SSIS "Import and Export Wizard" using the "Microsoft Excel" data source that there appears to be a maximum column length of 255 characters for any row.Įven when defining the destination table columns as nvarchar(4000), the wizard fails with the errors shown below. You can also use a Power Pivot Table to show the first two characters of the Product Code. Extract Characters with a Power Pivot Calculated Column
It will result in the above M code formula.Ĭlick on Close and Load in the Close group on the Home tab of the ribbon, and a new worksheet will be added to your workbook with a table of the data in the new format. = Table.AddColumn(#"Inserted First Characters", "Last Characters", each Text.End(, 2), type text) If you need the last 2 characters, then click on Last Characters in the Extract drop-down. This will result in the above M code formula. = Table.AddColumn(#"Changed Type", "First Characters", each Text.Start(, 2), type text) Click on OK and a new column called First Characters will be added. Double-click on the new column header and rename it to Category.
Select First Characters in the drop-down.Ī pop-up window will be displayed.
You may find that product references consist of a product code, code reference, and product size all concatenated into one piece of text which appears in one cell within the worksheet. When using data in Excel that has been imported from another source, the text is often not how you wish to see it.