September 16, 2014 at 7:28 am
Hi Guys, I am trying to export data using the BCP.
My syntax is as follows:
bcp dbName.schema.Table "My Query" queryout "Filepath" -S Servername\InstanceName -T -c
Can someone please tell me why it's not working.
The error message I get is:
Copy direction must be 'in', 'out' or 'format'.
September 17, 2014 at 3:38 am
If you are bcp'ing data out using a query then the syntax is as follows (assuming you want to bcp out all the data in a table):
bcp "SELECT * FROM database.schema.table" queryout "Filepath" -SServerName -T -c
I've used BCP a lot lately and I can never remember the syntax so I find this page very useful.
Regards
Lempster
February 4, 2015 at 6:39 am
I don't want to export from SQL, I want to import to SQL from the excel file. I have now created a format file but it still say starting copy, 0 rows copied
February 5, 2015 at 2:15 am
crazy_new (2/4/2015)
I don't want to export from SQL, I want to import to SQL from the excel file. I have now created a format file but it still say starting copy, 0 rows copied
Well, that's not what you stated in your original post. You don't need to use bcp to import from Excel, in fact I'm not sure that it is possible to do so, but you can use OPENROWSET or OPENQUERY like this:
INSERT INTO <your table>
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=<Excel file path and name>', [Sheet1$])
INSERT INTO dbo.ImportTest
SELECT *
FROM OPENQUERY(ImportData, 'SELECT * FROM [Sheet1$]')
For OPENQUERY you'll need to set up a Linked Server (in the example above, the Linked Server is called 'ImportData).
If the Excel file you are importing from is 2007 or above, instead of the Jet.OLEDB.4.0 driver you'll need the ACE.OLEDB.12.0 driver.
Have a look at the following links:
http://www.mssqltips.com/sqlservertip/1207/different-options-for-importing-data-into-sql-server/
Regards
Lempster
February 5, 2015 at 2:26 am
I used the openrowset noe, and the error message says:
The OLE DB provider "ACE.OLEDB.12.0" has not been registered yet.
So where and how do I register it?
February 5, 2015 at 4:21 am
crazy_new (2/5/2015)
I used the openrowset noe, and the error message says:The OLE DB provider "ACE.OLEDB.12.0" has not been registered yet.
So where and how do I register it?
You need to download it from here (making sure you select the correct version - 32 or 64 bit) and install it.
February 5, 2015 at 4:37 am
The drivers I have on here at the moment for Access and Excel are the following:
MS Access dBASE Driver - ACEODBC.DLL
MS Access Driver - ACEODBC.DLL
MS Access Text Driver - ACEODBC.DLL
MS Excel Driver - ACEODBC.DLL
Should I still install that other one?
February 5, 2015 at 8:55 am
Yes. The ones you have are ODBC drivers not OLEDB.
February 6, 2015 at 4:35 am
Oh ok thanks man.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply