Importing To SQL Server......

[amal1973]

Hello .....
I am recently facing a problem I haven't thought about it earlier .
I have a very big file in an Microsoft Excel Work Book ,Spread on 15 sheets (Customers accounts and Payments ), What I am trying to do is import all that Excel file in one Table On the SQL Server .
Also I am facing the problem of converting Data,One of the columns I have contain Numbers with a zero in front (I don't want to make any calculation on it ). I have converted it to text, but also I find difference between Unicode and normal text ??
I would really appreciate the help..
amal

 

[schu]

I came across a similar problem, but I had about 8 sheets. My method may not be best but it worked for me.

First the leading zero problem. I assume your problem is that the zero is missing because excel cleverly takes it away, even when you convert it to text. I wrote a vB macro to add the zero back in. It should be something like

StrNum = Left("0000000", 6 - Len(Trim(StrNum))) + Trim(StrNum)

This adds leadig zeros to make a string a fixed length of 6.

As for convertion of your work sheets, I assume you already have a design for the result table and know how to link the rows in the worksheets.

Firstly I matched some of the worksheets by hand. Simple match which can be performed using cut and paste, which reduced the number of worksheets to handle.

I just created the main table, and one temp table for each excel worksheet which is the destination for the DTS. The used
Insert into .... (select from ....)

and
update .... set ... = (select ...) etc

statements to create the main table
Obvoiusly the method depends alot on your data and how it is linked. It's still a lot of work, but then you have a lot of data to import.

 

[amal1973]

Well.. I am sure that there is a way to import all the sheets in one table without the use of insert into or using temp tables ... please advice..
Also .how could i insert 60,000 rows from excel in one table in sql server..
thanks for the tip