473,747 Members | 2,927 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Question about data import in MS-SQL server

Dear All,

I am performing a data import on the SQL server. Due to fact
that I use the excel file as a source. Some of cells in excel are
actually empty, they become NULL fields after importing into the SQL
server. Actually I want these fields are empty string instead of NULL.
Does SQL server has any approach to make these fields to be empty
string instead of NULL when importing?? Or is there any store
procedure exist for converting the fields to empty string?

Thanks for your kind attention.

Benny
Jul 20 '05 #1
4 9396
Benny (cs******@hotma il.com) writes:
I am performing a data import on the SQL server. Due to fact
that I use the excel file as a source. Some of cells in excel are
actually empty, they become NULL fields after importing into the SQL
server. Actually I want these fields are empty string instead of NULL.
Does SQL server has any approach to make these fields to be empty
string instead of NULL when importing?? Or is there any store
procedure exist for converting the fields to empty string?


Since I don't know how you import the data, I can't really say what
you could do in that end.

Once the data is in SQL Server, you can say:

UPDATE tbl
SET col = ''
WHERE col IS NULL

--
Erland Sommarskog, SQL Server MVP, so****@algonet. se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #2
I use DTS import/export wizard to import the data from the Excel file.
So is there any ways to replace the NULL fields with empty string
beside using query to update the fields to empty string?

Erland Sommarskog <so****@algonet .se> wrote in message news:<Xn******* *************** @127.0.0.1>...
Benny (cs******@hotma il.com) writes:
I am performing a data import on the SQL server. Due to fact
that I use the excel file as a source. Some of cells in excel are
actually empty, they become NULL fields after importing into the SQL
server. Actually I want these fields are empty string instead of NULL.
Does SQL server has any approach to make these fields to be empty
string instead of NULL when importing?? Or is there any store
procedure exist for converting the fields to empty string?


Since I don't know how you import the data, I can't really say what
you could do in that end.

Once the data is in SQL Server, you can say:

UPDATE tbl
SET col = ''
WHERE col IS NULL

Jul 20 '05 #3
Benny (cs******@hotma il.com) writes:
I use DTS import/export wizard to import the data from the Excel file.
So is there any ways to replace the NULL fields with empty string
beside using query to update the fields to empty string?


Sorry, I don't use DTS so I don't know. But I want to point out the
necessity of providing people full information about what you are
doing.

If you don't get any replies, consider asking in
microsoft.publi c.sqlserver.dts . (Available on msnews.microsof t.com if your
ISP does not have it.)

Also check out the FAQ on http://www.sqldts.com.

--
Erland Sommarskog, SQL Server MVP, so****@algonet. se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #4
With DTS, you can transform the data before it gets imported. That's the
'T' in 'DTS'. When moving an Excel spreadsheet to SQL Server, there is a
place in DTS where you select the worksheet from the Excel file. At this
location, there is a Transform button. You can do simple
transformations , or you can use VB code to do more complex
transformations .

HTH,
Brain

Benny wrote:

I use DTS import/export wizard to import the data from the Excel file.
So is there any ways to replace the NULL fields with empty string
beside using query to update the fields to empty string?

Erland Sommarskog <so****@algonet .se> wrote in message news:<Xn******* *************** @127.0.0.1>...
Benny (cs******@hotma il.com) writes:
I am performing a data import on the SQL server. Due to fact
that I use the excel file as a source. Some of cells in excel are
actually empty, they become NULL fields after importing into the SQL
server. Actually I want these fields are empty string instead of NULL.
Does SQL server has any approach to make these fields to be empty
string instead of NULL when importing?? Or is there any store
procedure exist for converting the fields to empty string?


Since I don't know how you import the data, I can't really say what
you could do in that end.

Once the data is in SQL Server, you can say:

UPDATE tbl
SET col = ''
WHERE col IS NULL


--
=============== =============== =============== =============== =======

Brian Peasland
dba@remove_spam .peasland.com

Remove the "remove_spa m." from the email address to email me.
"I can give it to you cheap, quick, and good. Now pick two out of
the three"
Jul 20 '05 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
14931
by: Daniel | last post by:
I use an Access database to basically take data exports, import them, manipulate the data, and then turn them into exportable reports. I do this using numerous macros, and queries to get the data in the format that I need it in order to create the reports that we use. So far this has proven to be successful for the reports that I am doing and the data that I am pulling into it. I just have one challenge that may require a lot of work and I...
1
2800
by: D Mat | last post by:
Hi, I'm trying to get MS Access 2000 to automatically import a series of (~200) flat text, tab delimited, data files into a single Access table, with consistent fields and rows. The files have different, but somewhat logical naming structures (ex.Jun01.txt, Jun02.txt, Jul01.txt, etc...). Is there a relatively simple way to accomplish this, considering i'm new to Access. I was especially hoping to do this without having to manually...
1
6703
by: Dan | last post by:
Could someone please help me with auto importing a series of data files into an Access table. I tried to follow code given below in a previous messagebut i'm getting error messages. Here's my database stats: Path: C:\Database (contains the database and all the text files to be imported) Text files to import: (SampleData4.txt and SampleData3.txt as testing examples)
0
1291
by: Doug Beacher | last post by:
I need to import data from a spreadsheet that is created new every day into an MS Access database. The data is in a workbook consisting of multiple work sheets. I plan to import using the TransferSpreadsheet method. The name of the spreadsheet will change every day, but the position of the data to be imported will always be the same. Here is my problem: Having never used this method before, I am unsure as to what to put in the...
6
7342
by: Noozer | last post by:
We've created a new database and need to import the data from our old database. Both are MS Access databases and each have multiple tables. The table structure of each table is completely different from each other, but the actual fields of the data we need to move exist in both tables. From what I see in the MS Access help I can only find how to import a complete table from another Access database. How can I import the data from table...
14
1462
by: Frank Rizzo | last post by:
I realize that this maybe the wrong forum, but maybe someone has run into this situation before. I sell an app and I want to offer my customers a migration path from a competing product. However, my competitor stores all data in an Microsoft Access database, which is password protected. Now, I can easily break the password and export all the data necessary into my app. In fact, I did break the password by downloading a $10 app from the...
5
3530
by: Wired Hosting News | last post by:
I tried to be breif and give a scenario so as not to be overlooked because it was soooo long. Let me give you real world. I am a manufacturer of goods and produce 11 items that are distributed to 1800 stores of a national home improvement chain store. Every week I electronicaly receive an excel spreadsheet "inventory report" with 19,800 rows or records, which I import into my tblSalesData table. The table now has 10 weeks of data or...
5
1598
by: klfhall | last post by:
Hello. I work for a small company and we currently use an intricate system of spreadsheets to manage multiple contracts, people and billing rates. Our main goals are to project cash flow and Profit/ Loss. We keep running into problems - ie. adding new people, new projects, new bill rates, etc, and everytime we do, it can be a real headache to make sure the changes propgate throughout all of the sheets. In general wondering... we are...
1
1660
by: rwiethorn | last post by:
Hi all, It's been a while since I used MS Access. I remember a way of importing data (ie csv, or tab delimited) and formatting the data (ie specify data types, columns to skip, ect), then saving that Import Spec. Then later in code you can reference that Import Spec. I cannot seem to save/create an Import Spec in MS Access 2002. Can someone point me into right direction?
0
1462
by: tacofinger | last post by:
Hi, How to import MS Query result to Access Table? I have one MS query result from oracle ODBC and total record is 100ku records. I have tried to use link table from Access -import function but it takes a long time to retrieve or refresh data. (because the database is in US and i am working in Asia. Any suggestion would be highly appreciated. Thanks!
0
8979
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
8818
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9522
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
9354
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
9223
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
1
6790
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
1
3296
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
2771
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
2203
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.