I have the source file in Excel as follows:
Item-------Reference---Description
-400020--20045-------Ruler 60cms
8900032--20012-------Eraser 45gr
Notes: Because I can add many spaces here, the dash"-" denotes a space on the original file The Item 400020 comes with a space before the 4 from the source.
The following file is the expected with the proper format to be exported to our DB
Item--------Reference---Description
0400020--0020045-----Ruler
60cms 8900032--0020012-----Eraser 45gr
It seems that you need to convert numbers in excel table to characters first, and adjust the data (spaces). Export to CSV, and then import into database. I suppose you have varchar datatype for Item and Reference columns in the table.
A history of using Excel has convinced me that Excel formatting should not be relied upon. And it is, in fact, difficult to influence on the fly.
Assuming Microsoft SQL Server, you could use a linked server query to read from the Excel spreadsheet or you could load the data into a staging table. Such as:
CREATE TABLE dbo.StagingExcel
(Item CHAR(7),
Reference CHAR(7),
Description VARCHAR(50));
Load the data into the StagingExcel table, then transfer the data, properly formatted, to the target table. E.g.
INSERT INTO dbo.YourTargetTable (Item, Reference, Description)
SELECT RIGHT(REPLICATE('0',7) + Item,7),
RIGHT(REPLICATE('0',7) + Reference,7),
Description
FROM dbo.StagingExcel -- (or the Excel linked server)
EDIT: My comment on Excel formatting is simply that you also have to control the Excel columns. If the Excel spreadsheet is all defined as text column, the results are predictable.
In the three columns below I typed the same strings in AutoFormat and Text columns, but see how differently they are displayed. Then I copied the AutoFormat values into AutoFormatToText column and got the AutoFormat values, but justified as Text.
**AutoFormat Text AutoFormatToText**
12/21/1934 12/21/34 12/21/1934
12345 12345 12345
123 000123 123
Your comment seems to imply that the first two columns combine into a single key value. Assuming the format to be two 0-justified 7-character columns becoming a 14-character column you would just concatenate the two columns. Snippet:
...
RIGHT(REPLICATE('0',7) + Item,7) +
RIGHT(REPLICATE('0',7) + Reference,7),
...
