Integer zero, "0' will be ignored when upload to SQL Server.

Question

i have a page that allow user to upload an excel file and insert the data in excel file to the SQL Server. Now i have a small issue that, there is a column in excel file with values, such as "001", "029", "236". When it's insert to the SQL Server, the zero in front will be ignored in SQL, so the data would become "1", "29", "239". The data type for the column in SQL is varchar(10). How to solve this?

Answer 1

Something must be converting the data in the excel cell from a string to an integer. How are you performing the insert?

Answer 2

Maybe setting up the datatype "Text" for an Excel cell will help.

Answer 3

Excel seems to automatically convert cell values to numbers. Try prefixing the cell contents with a single quote in the Excel sheet prior to processing. Eg '001. If you can't trust the users to do that, use a string formatting routine to left pad the numbers with zeroes.

Answer 4

If a user enters 001 into Excel, it will be converted to the number 1.

If the user enters '001 into Excel, it will be saved in the cell as text.

If the cell is pre-formatted with the number format "@", then when the user enters 001 into the cell it will be entered as the text "001". The "@" number format tells Excel that the cell is a text cell and any entry (whether it looks like a number, date, time, fraction, etc...) should simply be placed in the cell as is - as a text cell.

Can you tell your users to pre-format this column with "@"? This is generally the most reliable way to handle this since the user does not have to remember to enter '001.

Answer 5

Excel is probably the culprit here. Try converting your file to CSV and see how it comes out. If the leading zeros are gone in the new CSV file, Excel is the problem.

Answer 6

Excel always does this, and its a nuissance. There are three workarounds I know of:

1. BEFORE entering the data in any cell in Excel format the cell as text (you can do a whole column if needed.) This only works if you control the spreadsheets and users, which is basically never :-).

2. Assume you'll get a mix of numbers and/or text in the Excel data, and fix it in Excel before import: add a column to the spreadsheet and use the TEXT() function to convert the number to text, as in =TEXT(A2, "000"); fill down. Also assumes you can edit the worksheet.

3. Assume you have to fix the numbers upon insert in your code. Depending on how you are loading the data, that could happen in T-SQL or in your other code. In TSQL this expression works to pad with zeros to a width of 3 characters: right( '000' + cast ( 2 as varchar(3) ), 3 )