SSMS import wizard using CSV file with full date is not loading milliseconds to SQL Server table

Question

I have some large CSV files that I'm trying to import into a SQL Server table using ssms import wizard. One of the columns is a date column formatted as yyyy-mm-dd hh:MM:ss:xxx. I've tried every date format in the source field that is offered to me, but it, at best, gives me the entire date with the milliseconds as .000 rather than the value that is in the .txt CSV file.

The only thing I can think of is that the input date has the milliseconds after a colon (:) rather that a period (.). I could write a program that converts the colon to a period and then load it, but I did edit the file and changed the first six rows to period and that didn't seem to have any effect.

For the import I'm using "Flat File Source", under "Advanced", I select "DataType" for the column and have used every one of the following as the DataType.

database date [DT_DBDATE], database time [DT_DBTIME], database time with precision [DT_DBTIME2],

database timestamp [DT_DBTIMESTAMP], database timestamp with precision [DT_DBTIMESTAMP2],

DT_DBTIMESTAMPOFFSET] and even date [DT_DATE], decimal [DT_DECIMAL], file timestamp [DT_FILETIME]

The Destination is to SQL Server Native Client 11.0 to destination Type of datetime2 which is defined on the table as datetime2(7), not null.

With all these attempts I either get a "data would be truncated" error, or it would load with the milliseconds set to 000 rather than the value that is in the CSV file.

I would appreciate any help.

Thanks,

Bob

Answer 1

You will be much happier if you pull all fields from the CSV as strings, and use derived columns to convert the string to the target data type.

Answer 2

If I understand you, I should define the date as a string on both the source and destination in the import wizard, and before importing add a column to my table that is derived from the destination column. I've never done a derived column before, but there seems to be a ton of information that will guide me. "Happier" does sound like the results of this method. I'm importing close to 200 million rows so I'll see if the derivation process slows it down a lot. I'll follow up with results. Thanks for the help.

Answer 3

As I was thinking about creating the derived column it suddenly occurred to me that I don't need to convert the string date to a datetime column on my destination table after all. I may think about it if performance becomes an issue, but the date column down to the milliseconds can be used in a substring "where clause" for all situations that I can foresee at this time. Sometimes I find myself over-complicating simple tasks. Thank you for the help; that certainly triggered me to think it through a bit better.

Answer 4

I have a similar issue. I am new to creating tables and inserting data into them. I have a csv file with roughly 50,000 rows. One column is a date-time stamp, and it is important to preserve this column for use later, after import. How do I go about doing so? I am not making any progress with the flat file import wizard. @Tom Phillips I want to follow your suggestion, but I don't know how to pull fields from the CSV as strings, or use derived columns to convert the string to the target data type.