EXCEL 导入到 VARCHAR 数据类型时返回小数 NULL 的 SQL
我正在开发一款软件，该软件在过去几年中呈指数级增长，并且数据库需要定期更新。客户现在正在向我们提供大型电子表格中的数据，我们将其格式化并将开始导入到数据库中。我正在使用导入和导出数据（32 位）向导。数据库中的一列包含“1.1.1.2”等值，我将它们作为 Varchar 导入，因为这是数据库中的数据类型。但是，对于像“8.5”这样的值，“NULL”将被导入。仅当有一位小数点时才会发生。
这是 Excel 的格式错误还是数据类型错误？
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 '1.1.1.2' 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?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
它正在 Excel 中格式化。我在使用导入和导出数据向导和 Excel 时遇到了很多麻烦，现在我首先将 Excel 文件另存为 csv 文件以删除格式，然后导入它。这样做需要额外的几秒钟，但为我节省了以后处理删除前导零、随机 NULL 值等的时间。
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.
我给您的第一个建议是让您的客户向您发送文本文件或至少 .csv 文件。 Excel 转换非常糟糕，如果您的客户一开始就不再向您提供 Excel，您将花费更多的时间来使事情正常运行。
问题在于数据类型是由最早的行确定的，因此如果 Excel 稍后认为数据是 Int 或小数而不是文本，则导入将认为这是错误数据并将其置空。
调整咆哮功能：
我以导入数据为生，我拒绝接受 Excel 文件，因为在尝试导入它们以及任何其他类型的文件时，还有很多很多问题需要处理。我们必须向其中一些人发送有关如何将 Excel 文件另存为 .csv 的说明，但如果您根本不接受 Excel 作为数据源，那么创建导入的成本会少很多。令人遗憾的是，设计 SSIS（向导在幕后使用的东西）的人似乎认为 Excel 是很少有人需要导入的东西，因此没有花时间为其创建良好的导入路径。这是他们自己公司的产品，让人大呼过瘾。人们会认为它会得到一些最好的支持。
关闭咆哮功能。
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.
我发现通过运行一个在任何数字字段前面放置 ' 的脚本，可以将它们很好地导入到 SQL 中。
我在 12 个电子表格中的一个上遇到了这个问题，不知道为什么它会成为一个问题。
如果您有很多行，一个简单的 VBA 脚本可以使这一切变得容易。
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.