I am working on a peice of software which has expodentially grown over the last few years and the database needs to be regularly updated. Customers are providing us with data now on large spreadsheets which we format and will start importing into the database. I am using the Import and Export Data (32-bit) Wizard. One column in the database contains values like '220.127.116.11' etc and i am importing them in as a Varchar as that is the data type in the database. However, for values like '8.5', 'NULL' is getting imported insead. It only occurs when there is one decimal point.
Is this a formatting error with excel or is it the wrong datatype?
It is formatting in Excel. I have had so much trouble with the Import and Export Data Wizard and Excel that now I save the Excel file first as a csv file to remove formating and then import it. Doing this takes and extra few seconds but has saved me time later from having to deal with leading zeros being dropped, random NULL values, etc.
My first advice to you is to get your customers to send you text files or at the very least .csv files. The Excel conversions are horrible and you will spend many more hours to make things work properly than you will if your customers stop giving you Excel to begin with.
The problem is that the data type is determined by the earliest lines and thus if Excel thinks the data later on is an Int or a decimal as opposed to text, the import will think that is bad data and null it out.
Tuning rant feature on: I import data for a living and I refuse to accept Excel files becasue there are many, many more issues to deal with in trying to import them and with any other type of file. We had to send out directions to some of them as to how to save an Excel file as a .csv but it costs us a lot less money to create imports if you simply do not accept Excel as a data source. It is sad that the people who designed SSIS (which is what the wizard is using under the hood) seemed to think that Excel was something few people would need to import and thus didn't spend the time to create a good import path for it. It's their own company's product for crying out loud. One would think it would have some of the best support. Turning rant feature off.
I found that by running a script that put a ' in front of any numeric fields made them import into SQL fine.
I ran into this on one spreadsheet out of 12, wasn't sure why it became a problem.
A simple VBA script can make this easy if you have a lot of rows.