Solved

Import csv to SQL 2008, time column problem

Posted on 2013-01-14
4
1,470 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 500 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

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
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

758 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now