?
Solved

Import csv to SQL 2008, time column problem

Posted on 2013-01-14
4
Medium Priority
?
1,939 Views
Last Modified: 2013-01-14
Am importing csv file to SQL 2008 using SQL Server Import and Export Wizard.
Only problem I seem to have is the time column, cannot use datetime.

SQL column is time(0)
CSV value is HH:MM:SS

Get error:
Found 2 unknown column type conversion(s)
The package will not be run.

Source Information]
Table: C:\Documents and Settings\jhinson.WAVERLYDOMAIN\Desktop\TermsCodes.csv
Column: Created_Time
Column Type: string [DT_STR]
SSIS Type: string [DT_STR]
Mapping file (to SSIS type):

[Destination Information]
Destination Location : (local)
Destination Provider : SQLNCLI10
Table: [dbo].[TermsCodes]
Column: Created_Time
Column Type: time
SSIS Type: database time with precision [DT_DBTIME2]
Mapping file (to SSIS type): c:\Program Files\Microsoft SQL Server\100\DTS\MappingFiles\MSSQLToSSIS10.XML

[Conversion Steps]
Conversion unknown ...
SSIS conversion file: c:\Program Files\Microsoft SQL Server\100\DTS\binn\DtwTypeConversion.xml

On preview everything looks fine.
0
Comment
Question by:jhinson
  • 3
4 Comments
 
LVL 8

Accepted Solution

by:
virtuadept earned 2000 total points
ID: 38776066
I tried it also and got the same error as you, with a time(0) column and HH:MM:SS formatted data in a CSV. Then I just converted the CSV to an Excel file and it imports fine, same data. Not sure how this helps you, it looks like it just is some kind of glitch in the importer.

EDIT: OK I figured out how to make it import CSV, On the screen for choose a data source there is an advanced tab on the left hand bar, click that and it opens a screen where you can define data type for each column in the CSV, and set the time column to "database time with precision".

Advanced tab
0
 
LVL 8

Expert Comment

by:virtuadept
ID: 38776157
Let me know if that works.
0
 

Author Closing Comment

by:jhinson
ID: 38776238
That worked for the time. Thanks.
It appears there will not be a straight forward solution for me.
Am extracting files from an AS400, dumping to Excel (only choice is Excel 97).
Excel treats some columns as numeric when they are text, but mostly numerals.
The only reliable solution seems to be to dump to a txt file, open Excel 2010 on desktop and save as xlsx file.

But your solution did work, thanks
0
 
LVL 8

Expert Comment

by:virtuadept
ID: 38776302
If you need an automated way to do it, you could try using a BULK INSERT statement in a stored procedure and dump the whole import file (raw from AS400) to a table that is all VARCHAR(255) columns with same number of columns, then have a stored procedure that does the converts from the "stage" table into the permanent table using the proper CONVERT for each column (and also will allow you to do some error checking and error handling).  And you can schedule this with SQL Server Agent.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

621 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question