How can I import xls

Question

How can I import xls

anamoji

SSChasing Mays

Points: 601
More actions
May 17, 2002 at 11:48 am

#104850

Hi, I can not find something like this. Well I am going to start telling you what I need. I have an excel file, and I need to get only 12 columns of a worksheet (and it has more than 20) and stored the data into a table so I can use it. I don´t have any idea, I thing using bcp or something like that but I don´t know how to use it, can you help me?.
Thanks a lot for your help
Ana

Ana

Viewing 8 posts - 1 through 7 (of 7 total)

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

Antares686 SSC Guru Points: 125444 More actions · Answer 1

Use DTS. Open EM and drill thru Data Transformation Services to the package location. Create a new package, there you can define a connection to SQL, a Connection to XLS file. Then click on the XLS connection, hold down CTRL key and click SQL connection, right clisk and choose Transformation. There will now be a line pointing from the XLS connection to SQL. Double click it and in the properties you can set the destination and on the Transformation tab select and map the fields you want. Save and run. Now you have a quick way to do repeatedly or scheduled.

"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)

anamoji SSChasing Mays Points: 601 More actions · Answer 2

Thanks for the tip, but I have some questions:

1. I have a column on my xls thata has different type of data, and when I run the DTS the data thas has characters appear with null. how do I do it so the dts import every kind of data?

2. How can I run a dts from a query or a stored procedure?

Thanks a lot for your tips.

Ana

Antares686 SSC Guru Points: 125444 More actions · Answer 3

1) When you create the link it should tell you the value type is int or something else for that column. I have heard people make statements about this problem but I don't know what the solution has been. Try changing the columns definition to varchar (may also have to make sure the entire column is a text and that individual cells in the column are not formated differently).

2) You cannot do thru query and you can only execute the package in sp which will tie things up if multiple people try this at the same (most will get an error). If you need to get data live from this file then use a linked server or openrowset options to connection to the file live and access the data. See BOL for more details.

"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)

anamoji SSChasing Mays Points: 601 More actions · Answer 4

Thanks for your help, but I have some doubts, I am not an expertise on this, so how do I put it in a sp?

and I try to found the value type but it doesn´t appear, I put all the format of the xls to general and the value of the table to varchar.

What is BOL? where can I find it?

You tell me about an openrowset options, can you explain me more about it? or where can I get it?.

Thanks a lot again 😀

Ana

ralcure SSC Enthusiast Points: 168 More actions · Answer 5

Hello,

I believe this may help.

Just today I managed (with help from someone here in this forum) to execute a DTS package from within a procedure.

What I did was create a JOB (Management folder in EM), and a step in this JOB cmdShell like. In this step I execute dtsrun with its parameters.

It worked well, but the procedure exits before the import ends. It may cause problems and I now trying to find out if the JOB can wait untill the import finishes to return back to the procedure.

And in the procedure I called sp_job_start .

Antares686 SSC Guru Points: 125444 More actions · Answer 6

Sorry BOL is SQL Books Online. You just used to using the abrev version of things. Sorry it has been a while since using OPENROWSET but look in BOL for some examples of which here is one for querying and the Northwind sample Access DB Orders table.

SELECT a.*

FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',

'c:\MSOffice\Access\Samples\northwind.mdb';'admin';'mypwd', Orders)

AS a

GO

If you need an XLS example let me know and I will create one.

As for DTS you can use xp_cmdshell to execute DTSRUN and call the package and apparently you can use OPENROWSET to query a DTS package (which I have never tried but sounds interesting).

"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)

Edited by - antares686 on 05/17/2002 8:18:16 PM

neli Newbie Points: 3 More actions · Answer 7

Try to change the extantion of the file from xls to csv. After changing the extantion and run the DTS the data that has characters will not apper with null. Because csv file is a comma delimited file and can be used as Excel and as a text file. And don't forget when you create a new package you have to define one connection to SQL , and another to Text File not a connection to XSL FILE.