Read Excel, CSV and Tab delimited text files with an SQL query forCustomer and Item imports to RMS


B

Bill Wardlaw

Hi Convoluted,

I don't know how to have your update or insert query read an excel
spreadsheet, maybe someone more experienced with SQL will reply........
How to link sql server to an excel spreadsheet
1. Create a folder that SQL server has rights to access, EG: C:\Data
\Import\
2. Place your spreadsheet in the folder EG: ConvolutedSomething.xls
with a worksheet called MySheet
3. Create a linked server to tell sql server about your folder
3.a You can do this with a script but I prefer to do it interactively
3.b If you use a script the tricky bits are SQL server rights to
access the folder and setting the password
3.c Download, install and open (free) Microsoft sql server management
studio express
3.d Connect and go to server objects>linked servers and make a new
linked server
3.e Enter and OK the following
Name: MyLinkedXLFile
Server type: Other
Provider: Microsoft Jet 4.0 OLE DB Provider
Product name: Excel
Data source: C:\Data\Import\ConvolutedSomething.xls
Provider string: Excel 8.0
4. Run a query EG: select * from MyLinkedXLFile...MySheet$ (you might
need to restart sql server first)
5. You can do far more with linked servers
5.a Such as link to CSV and tab delimited files in that folder by
putting a schema.ini file in that folder which tells sql server how to
read your files that are not spreadsheets.
5.b To find out more, search on google for "microsoft schema.ini" and
look at the result for Microsoft MSDN
end
 
Ad

Advertisements

C

convoluted

Thanks Bill - will play with this when I have some downtime - always
appreciate an opportunity to learn something new (and there's so much to
learn ) - I appreciate your post.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top