Solved

Import csv to SQL 2008, time column problem

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
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.

785 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