June 28, 2015 at 11:41 pm
hi,
I want to import excel file in sqlserver , i was informed that we have to pay of drivers like .jet and .ace.
i have sqlserver and OS windows licences and do not want to pay just to import excel in sqlserver.
so please tel me folowing
q1) Is ssis requires .jet or .ace or any other componenet for xls import which is lincenced.
apart from sqlserver and OSwindows licence ,.
Q2)is it possible to convert xls into micorsoft word and then transport it.
Q3) do i have to pay any thing if i use bulk insert to import .txt or .csv file.
what other type of files can be imported by bulk insert.
Q4) how many type of files like .txt or .csv , can be imported using bcp?
yours sincelrey
yours sincelry.
June 29, 2015 at 5:44 am
rajemessage 14195 (6/28/2015)
hi,I want to import excel file in sqlserver , i was informed that we have to pay of drivers like .jet and .ace.
i have sqlserver and OS windows licences and do not want to pay just to import excel in sqlserver.
definitely incorrect. the ace drivers are free, and can be downloaded here:
http://www.microsoft.com/en-us/download/details.aspx?id=13255
Installing Full Office on the server, which costs at least a license, and installs these drivers and a lot more, is NOT free, so that's probably the misconception.
so please tel me folowing
q1) Is ssis requires .jet or .ace or any other componenet for xls import which is lincenced.
apart from sqlserver and OSwindows licence ,.
Q2)is it possible to convert xls into micorsoft word and then transport it.
why would you want to do that? excel is the most consumable format for data. converting to word? waste of time, but possible with excel automation, which is a pain to learn.
you would have to have a very exotic business reason to want to convert to word that i cannot even imagine, so i bet you are asking can you convert form xls to some other raw text format, so the answer is yes, but i'd do it in an SSIS package.
jet drivers are most likely already in place, but also have a free download, just google it
Q3) do i have to pay any thing if i use bulk insert to import .txt or .csv file.
what other type of files can be imported by bulk insert.
no, bulk insert and all those functionalities that are part of the SQL install and toolset are free after you've bought SQL server; they are part of the free client tools as well, which you can download (SSMS tools)
Q4) how many type of files like .txt or .csv , can be imported using bcp?
there is no limit to the quantity or size of the files you can import.
yours sincelrey
yours sincelry.
Lowell
June 30, 2015 at 2:04 am
thank u,
in question no 4)
i wanted to know, type of files like, can i import xls ,.xlsx ,.doc using bcp or bulk import command.
yours sinclerye.
June 30, 2015 at 5:15 am
sqlcmd, bcp and bulk insert can import flat text files only, but the delimiters a can be anything; comma seperated, tabl seperated or pipe character seperated are probably the most common, but you can import delimited files with other special characters for the row or column delimiters.
for xls/xmls, you'll need to use SSIS and an ODBC driver like the Jet or ACE drivers.
Lowell
June 30, 2015 at 6:19 am
Lowell (6/29/2015)jet drivers are most likely already in place, but also have a free download, just google it
Just to add - JET drivers distributed by default with Windows operating system are likely older versions and are 32-bit only. It's usually more ideal to have the drivers match the bit of the SQL Server instance (granted, you can run SSIS in 32-bit but there are gotchas). To use newer file formats or to run in 64-bit mode, you should download the ACE redistributable as was pointed out and add to your server.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply