Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Solving a Problem Importing Integers Using Format Files

By Banyardi Schmardi,

The Problem

I had been importing all my TAB delimited text data as VARCHAR using the SQL_Latin1_General_CP1_CI_AS Column Collation into extract tables, and then implicitly casting the INT data types when inserting into base tables.  A business requirement made me revisit how to import the data into the extract tables with the INT data type. Using the BCP program to generate the FORMAT file produced unsatisfactory results for those fields identified in the Host file as SQLINT. The integers were not importing correctly to the extract tables in SQL Server.

Problem Analysis

I researched this issue online without finding a solution. It was a single sentence, and an unexplained example in Books-OnLine (BOL) that provided the key to understanding the real problem and producing the solution. 

In BOL, on the “File Storage Type” page, the different data types and their Host File Data Types are described. In the middle of the page is this sentence: “When noncharacter data types (for example, float, money, datetime, or int) are stored as their database types, the data is written to the data file in the SQL Server internal binary data format.” This indicated to me that integers being imported as SQLINT were being translated to a binary data format. 

In BOL, on the “Using Format Files” page, I found the following example:

I identified the field pub_id as an integer data type, so I used the first line as an example for my Format file. I noticed that the Column Collation was SQL_Latin1_General_Cp437_Bin.  Searching the MSDN library I found the explanation of this collation type.  The first part defines a string identifying the alphabet or language whose sorting rules are applied when dictionary sorting is specified, ie. Latin1_General or Polish.  The second part is a one- to four-digit number that identifies the code page used by the collation.  The suffix identifies Case sensitivity (CI, CS), Accent sensitivity (AI, AS), and the binary sort order to be used. 

The Solution

By changing the data type in the Format file to SQLCHAR 4 with a column collation of SQL_Latin1_General_Cp437_Bin, the integer data was translated correctly when importing using either BCP or BULKINSERT.

Total article views: 3824 | Views in the last 30 days: 1
 
Related Articles
FORUM

SSIS File Import Collation

Importing Unicode file to Latin_General collated database

FORUM

collate

using collate in query union with other query without collate

ARTICLE

Beware of Mixing Collations: Converting Collations

With SQL Server 2000 you are able to create databases or columns with a different collating setting ...

FORUM

Collation Setting Changes

Collation Setting Changes

Tags
bcp    
etl    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones