x

Is there a way to auto-assign data types to my table?

Hey guys, quick question...

I just imported a large .csv file, that has about 60 columns, and about 300,000 rows... In order to get the import to work properly I just set all columns to data type string and field size of 500. When I tried to do it the other ways, I just got errors every couple hundred rows about some sort of anomaly...

So now I have this table, is there anyway that I can either copy this table and auto-detect data types or update these data types automatically?

Thanks
more ▼

asked Jul 04, 2012 at 09:07 PM in Default

TelepathicSheep2 gravatar image

TelepathicSheep2
140 14 15 17

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

Usually the setting of the datatypes is done at the time of import. SSIS and other tools, such as Log Parser, can be set to review the first N rows of the sourcefile in order to attribute a datatype to the new (destination) table.

It would appear from what you say that the source data does have anomalies in it if you cannot import the data successfully with more restrictive data type settings.

Is this a characteristic of the data or is it's integrity fundamentally out of whack?

My next step from your position would be to analyse the data, column by column, making data fixes if appropriate or identifying issues as they are encountered. Once you have worked on all the columns you can use TSQL to move the data from your current table to one with a better schema.

You may well be using ISDATE (http://msdn.microsoft.com/en-us/library/ms187347.aspx), ISNUMERIC (http://msdn.microsoft.com/en-us/library/ms186272.aspx) and so on to identify records that fit, or fail to fit, your expectations.

Once you have the datatypes that you want to have applied then create a new table accordingly and then you can use a simple INSERT statement to move the data. You can convert data during this phase or simply refine the data quality in the current table prior to the transfer.

Sadly there is no way to automatically assess the data for the appropriate datatypes where the data is in some way inconsistent as you delve deeper into the recordset.
more ▼

answered Jul 04, 2012 at 09:30 PM

Fatherjack gravatar image

Fatherjack ♦♦
42.4k 75 78 108

One trick I used to use was to import the file into Access first as it is much more forgiving with data types. Then I would import the data from Access into SQL Server which never complained about the Access data even when it would choke on the original file.

I think with SSIS you can redirect the error rows (1 out of 500-ish). This gets the good rows into the table and the "bad" rows into a location where you can look at just them
Jul 05, 2012 at 12:59 AM KenJ
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1834
x7

asked: Jul 04, 2012 at 09:07 PM

Seen: 877 times

Last Updated: Jul 05, 2012 at 12:59 AM