Fetching Data from Excel File

[sparkme]

I have a Excel file Called Customerdata with data in one sheet called data value(There is another sheet in the same Excel file) This Excel sheet is in my Local drive C:

Can anyone tell me the syntax to fetch the record from this excel file (FYI) the following code is not working:


SELECT * FROM OPENROWSET ('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;DATABASE= C:\Customerdata.xls;User ID=sa;Password=',
'SELECT * FROM [data value$]')

Thanks

 

[ESquared]

I was never able to get connecting to an Excel spreadsheet to work, either.

People might be more inclined to spend the effort to help you if you provide more detail... what you've tried, exact error messages, what resources you've found on the web.

Did you realize that you posted the same question twice? You might like to know that some people could lose interest in answering your post when they see this.

I solved my own issue by using DTS to get my data into SQL Server and work with it entirely there.

 

[TRACEYMARYLAND]

There is an import utility in SQL think under tools where you can import a file which is csv and put that into a table......

 

[chipCHE]

I agree with Esquared. DTS is probably your best bet. With it you can import from a csv on a scheduled basis, on a one time basis, or you can even call the DTS package you create from a stored procedure.

I have found DTS very useful.
In one case I use DTS to FTP to a Telecenter site (They collect data from people calling in requesting product info)
1) DTS downloads the CSV file to my server and pulls the data into a temp table.
2) DTS then runs an update Query and cleans up the data.
3) DTS pulls the cleaned up data from the Temp table into the main database tables.
4) DTS sends an e-mail notifying me of the results.


DTS is extremely simple to use. It may take a little for you to figure out the ins and outs but it is well worth it.

If I can figure it out it - anyone can

Regards,
Chip

 

[sparkme]

Hi Thanks to all your replies!

I have totally 60 Excel files which loads data
into 150 tables, If i use DTS i have to create 60 packages,
Instead if i write one program(script) which can be something like what i mentioned earlier FYI

SELECT * FROM OPENROWSET ('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;DATABASE= C:\Customerdata.xls;User ID=sa;Password=',
'SELECT * FROM [data value$]')

Will be more useful.

I'am getting error mesage that file can not be created because of OLEDB error.

....Thanks