CODEWITHSUNDEEP
sql-servercsvformattingdecimal-point
Vance Palacio
Vance Palacio

Reputation: 1450

Adding Decimal to data during ms-sql server bulk insert

so here's the scenario I'm working with:

Im working with data supplied to me from an external source. Basically whats happening is that the database definition has a column of type numeric(9,3)

A csv file has been provided, and for the column specified above, all of the data comes in the form of:

000100000
000001000
000002000
001000000

And so essentially what has happened is that its up to me to insert the decimal point my self in order to convert these values to things like 

000100.000
000001.000
000002.000
001000.000

I've been adding all the decimals to the values myself in c# using basic text file manipulation. Is there a way to tell sql server to insert a decimal point by default at the third position during the bulk insert operation?

Upvotes: 0

Views: 2344

Answers (2)

bdway
bdway

Reputation: 95

I know this is an older post, but I came across it trying to solve the exact same problem. I figured out a way to do this in one step and avoid the staging table so I thought I'd share.

Rather than using the BULK INSERT command you can use OPENROWSET and provide column aliases that you can then use in a select.

First you need a Format File to define your columns:

<?xml version="1.0"?>
<BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    <RECORD>
        ...
        <FIELD ID="2" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="12"/>
        ...
    </RECORD>
    <ROW>
        ...
        <COLUMN SOURCE="2" NAME="my_numeric_field" xsi:type="SQLINT"/>
        ...
    </ROW>
</BCPFORMAT>

Notice that we define our field as an INT. Then you can use the OPENROWSET to read the contents of your file:

INSERT INTO dbo.my_table
SELECT 
    ...,
    **my_numeric_field/1000**,
    ...
FROM OPENROWSET(
    BULK 'C:\my_file.txt',
    FORMATFILE = 'C:\FormatFiles\my_format_file.fmt'
) AS e (...,**my_numeric_field**,...)

Notice that we assign column aliases to each column. Now in the select that will be inserting into our table with a numeric or decimal column you can use your alias and apply additional logic to it, such as dividing by 1000 to get the desired result.

Upvotes: 1

user170442
user170442

Reputation:

In such case you would want to use staging table, load data into table in its raw form, then move it to target table and divide each row's value by 1000. Of course if you have different format, like 4 digits, then you divide by 10000 and so on.

Upvotes: 1

Related Questions