Loading Data from Text file to SQL Server

Question

Loading Data from Text file to SQL Server

apat

SSCertifiable

Points: 5929
More actions
July 29, 2009 at 2:28 pm

#212751

Hi All,
I have a text file with one column where some of the data is blank where some of the records have some values like 0.9, -2.3,3.0,4 etc. I am loading that to one Table in SQL Server 2008. For those records where there is blank(empty) it gets loaded as 0 in my Sql server table.
What is the work around with this? I do not want that to be loaded as 0. I want that to be NULL or someother number etc.

Viewing 9 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

apat SSCertifiable Points: 5929 More actions · Answer 1

Also, my source text files are on my local machine and not on server. I cant copy that to actual server. So, Bulk Insert wont work.

Madhivanan-208264 SSCertifiable Points: 7516 More actions · Answer 2

Use Import/Export wizard

Madhivanan

Failing to plan is Planning to fail

apat SSCertifiable Points: 5929 More actions · Answer 3

I used Import Export wizard and that doesnt solve problem. SQL Server 2008 has this problem, it was working well if you use dtswiz with 2000/2005.

Jeff Moden SSC Guru Points: 1004615 More actions · Answer 4

Bulk Insert will work fine if the server can see a "share" on your local machine.

So far as the "0'/Null thing goes... load it into a staging table and change it. You should never load external data directly into a final table for a lot of reasons... mostly because you don't know what the condition of the external data is and on the fly validation is quite expensive performance wise. It's a real killer if a ROLLBACK happens to occur.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Rupashri Gulawani SSC Veteran Points: 233 More actions · Answer 5

Hi,

When you complete IMport Ecport Wizard, it asks you to save the SSIS package. Check that option.

Open this SSIS Package in Business Intelligence Studio. Right click on "SourceConnection"->Edit. Go to the "Advanced" option on left hand side. it will give you list of columns in text file. Check the column properties, change the data type to string.

I think it will work..

Thanks,

Rupashri

Rupashri Gulawani SSC Veteran Points: 233 More actions · Answer 6

One more thing you can try..

go to Source Data Flow tasks and open it.

It has property to Retain Null Values.

Thanks,

Rupashri

apat SSCertifiable Points: 5929 More actions · Answer 7

apat

SSCertifiable

Points: 5929

July 31, 2009 at 7:30 am

#1032917

Thanks for the help... it worked...

Jeff Moden SSC Guru Points: 1004615 More actions · Answer 8

apat (7/31/2009)
Thanks for the help... it worked...

It worked? Which of Rupashri's SSIS suggestions worked?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Loading Data from Text file to SQL Server

Cookies on SQLServerCentral