When Excel Data is imported to SQL Server, null occurs.

After importing data to the customer today, we found a column containing many NULL data. When SQL Server is used for data import, the data type is float by default, and cannot be changed. As long as you turn to the omnipotent network

I found it on Google: http://hi.baidu.com/qwwwwqwwww/blog/item/7fa251088eea7ada63d98660.html

In Excel, we often encounter such a field (the most common is the phone number), that is, a pure number (such as a phone number without a zone number ), there is also a mix of numbers and other characters (such as "area code-phone number"), during the import of SQL Server, you will find that either the data of pure numbers is converted to null after being imported, it is either a mix of numbers and other characters that are imported and then converted to null.

Why does some data imported from pure numbers become null, while some data imported from mixed numbers and other characters become null?

In the process of sqlserver, sqlserver will make a judgment, whether the float or nvarchar type is used to accept data, the test shows (there is no scientific basis), which type of SQL server uses depends on the type to be imported

Which of the following types of data has a large proportion of records? For example, if there are 10 data records, there are 4 phone numbers without a zone number, and 6 are phone numbers with a zone number, then switch to sqlserver and select

In nvarchar mode, the result is that the four phone numbers without a zone number are all null after being exported, and vice versa. In any case, we finally want sqlserver to adopt nvarchar to accept

Data: The data we want to import contains data mixed with numbers and other characters. It is impossible to use the float type to accept the data, in this way, as long as we convert the pure numeric data to the numeric type and

Accept SQL Server.

The first thing I thought of was to set all the data in this field to the text format in Excel. I just mentioned that I wanted to convert the data into the character type when importing sqlserver, but the results were disappointing and didn't work.

Finally, the answer is: forced resolution of hybrid data type columns -- IMEX = 1
After the parameter is selected using IMEX = 1, all columns of the hybrid data type in the sample data are forcibly parsed to nvarchar/ntext text. Of course, IMEX = 1 does not affect the resolution of a single data type column.

IMEX is used to tell the driver to use the Excel file mode. Its values include 0, 1, and 2, which respectively represent the export, import, and hybrid modes. When we set IMEX = 1, the forced hybrid data is converted to text, but this setting is not reliable, IMEX = 1 only works when at least one of the first eight rows of data in a column is a text item, it only slightly changes the behavior of selecting the dominant data type in the first eight rows of data. For example, if the first eight rows of data in a column are all pure numbers, the Data Type of the column is still numeric, and the text data in the row is still null.
Another improvement measure is that IMEX = 1 is used with the registry value typeguessrows. The value of typeguessrows determines the Data Type of the isam driver from the previous data sampling. The default value is "8 ". You can change the number of sampled rows by modifying the registry value under "HKEY_LOCAL_MACHINE \ SOFTWARE \ Microsoft \ jet \ 4.0 \ engines \ excel. However, this improvement still does not fundamentally solve the problem. Even if we set IMEX to "1" and typeguessrows to be larger, for example, 1000, suppose the data table has 1001 rows, the first 1000 rows of a column are all pure numbers, and the first 1,001st rows of the column are a text. The isam-driven mechanism still makes the column data empty.

Select * into table08 from
OpenRowSet ('Microsoft. Jet. oledb.4.0'
, 'Excel 5.0; HDR = yes; database = E: \ 1.xls; IMEX = 1; ', [sheet1 $])

Note:
1. This statement is executed in the SQL Server Query analyzer and you must select a database. Otherwise, the data to be imported will be exported to another database.
2. table08 is the name of the table in sqlserver after the data is imported. It is a new table. Therefore, make sure that the table name is not in the database before the data is imported. Otherwise, the system prompts that the same table name already exists.
3. Data Source. Do not write data together. There is a space in the middle.
4. E: \ 1.xls, which is the absolute path and Database Name of Excel.
5. For Excel 5.0, write 5.0 or 8.0 or other words based on different Excel versions.
6. IMEX = 1 is the meaning of converting to text input. It is very important. If not, it will be the same as importing directly.
7. sheet1 is the table name. Do not add $ to the table name if $ is in the statement. Because $ is the name to be added by the statement.

If an error occurs:
SQL Server blocks access to the statement 'openrowset/OpenDataSource 'of the 'ad hoc distributed queries' component because this component has been disabled as part of the server's security configuration. The system administrator can enable 'ad hoc distributed queries 'by using sp_configure '. For more information about enabling 'ad hoc distributed querys', see "peripheral application configurator" in SQL Server books online ".
Query relevant information and find the solution:

Enable ad hoc distributed queries:
Exec sp_configure 'show advanced options', 1
Reconfigure
Exec sp_configure 'ad hoc distributed queries ', 1
Reconfigure
After use, disable ad hoc distributed queries:
Exec sp_configure 'ad hoc distributed queries ', 0
Reconfigure
Exec sp_configure 'show advanced options', 0
Reconfigure

Another troublesome problem is the scientific notation of Excel. I found a solution online,

When you enter long numbers such as mobile phone numbers and ID card numbers in the form, Excel intelligently displays them in scientific notation. What's more troublesome is that, I wanted to display the settings in text, but the settings remain unchanged. In addition, even if the text is copied, the E in the scientific count is also copied. To display the cell properly, you need to double-click the cell again, or press F2 and then press the Enter key to show the display. Really troublesome!

In Excel, how does one cancel the display of all scientific counts at a time?

1. A convenient temporary solution is to use the partition function.

Operate with excel2007:
1. Select the cell to be converted and set the attribute to text format (this step can be ignored, but it is recommended to perform this operation once)
2. Select a column of numbers in Excel (it seems that only one column can be selected), and select data-by-column-next-select text-to-finish.
After the operation, you have basically achieved the goal. However, if some numeric strings start with 0, they may be removed by Excel. In this case, you need to manually add them again, as for how to add more efficiently, you can use editplus and other powerful text editors.
2. You can set the cell attribute type to 0 faster. For example, if you have entered a number in unit A1: a100, follow these steps: select unit A1: a100, right-click, set unit format, and select Custom, enter 0 in the type to make it easy to do this. Of course, this method will also lose the number string starting with 0!

The above are all the relevant results I found, but I actually used the last method to remove the scientific notation. I found this is the simplest and most effective method, numbers are processed as strings. That is to say, you do not need to modify the registry at the top of the list, such as force resolution of hybrid data columns. But why is it ineffective to select and modify all of them to the text format ?? This is probably the design of Microsoft. It cannot be said to be a problem, but it is certainly inconvenient to operate. You can select data-sort-next-select text-to complete this step to get the desired result. After such processing, No matter what number is text, there will be no scientific and technical methods, and after processing, you can directly use SQL import, and do not need to write SQL statements.

 

I used the second method to solve the problem. Thank you!