日期转换为从Excel导入到SQL Server的号码 [英] Date converting to number on import from Excel to SQL Server
问题描述
我正在尝试从Excel导入一个表到SQL Server，有一个字段有时包含一个日期（ mm / dd / yyyy ），有时包含一个日期和文本（ mm / dd / yyyy notes ）。
I am trying to import a table from Excel into SQL Server and there is one field that sometimes contains a date (mm/dd/yyyy) and sometimes contains a date and text (mm/dd/yyyy notes).
当我使用导入向导时，只有日期的行将转换为五位数字（ ##### ）。具有日期和文本的行正常显示。还有另一个字段只是一个日期，并且正常导入为datetime。
When I use the import wizard the rows that just have a date are converted to a five digit number (#####). The rows that have the date and text appear normally. There is another field that is only a date and that imports normally as datetime.
我也试图用excel写入import语句：
I also tried to write a import statement in excel like this:
="Import into MyTable values('"&A2&"','"&B2&"', [...] )"
但是，也将日期格式化为五位数字。
But that also formatted the date as a five digit number.
有没有人有建议导入此字段并保留日期格式？如果这是不可能的，那么我可以从五位数字转换回日期吗？
Does anyone have a suggestion to import this field and preserve the date format? If that is not possible then can I convert from the five digit number back to a date?
推荐答案
你可以尝试这样： / p>
You could try this:
-
首先使用另存为导出excel文件作为文本与制表符分隔符：
First export the excel file as Text with Tab delimiter using Save As:
然后打开SQL Server，并在导入向导中选择平面文件（即SQL用于原始文本文件，CSV等）的名称：
Then open up SQL Server and in the import wizard, pick Flat File (that is the name SQL uses for raw text files, CSVs, etc):
之后，请确保您设置了正确的设置：
After that, just make sure that you have the right things set:
我突出显示在导入过程中通常很重要的部分。
I highlighted the parts that are usually important during the import.
这篇关于日期转换为从Excel导入到SQL Server的号码的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
