?
Solved

Import csv to SQL 2008, time column problem

Posted on 2013-01-14
4
Medium Priority
?
1,745 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

764 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