Import of Excel or Txt File into Database

Question

Post reply

Import of Excel or Txt File into Database

jonathanm 4432

SSChasing Mays

Points: 619
More actions
September 11, 2015 at 8:48 am

#300364

I have been having some trouble now and need to see if there is a way to do the following:
I have an .xlsx file where I need to import the data into a table. If there is not a way to do this, is there a way to import either a tab del file or different type of .csv file into the database?
Any help would be nice. Do not want to use the SSIS or import feature from SQL2008 as I tried to save the steps and running it wont work either.

Viewing 10 posts - 1 through 9 (of 9 total)

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

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

You can use BULK INSERT to import from a csv or (any flat file).

You can also use OPENROWSET to query the Excel file as a table and insert the rows into the table.

The import/export wizard is the easiest option, but you don't want to use it.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

jonathanm 4432 SSChasing Mays Points: 619 More actions · Answer 2

I tried both of those options and it didn't work. So that is why I am asking if anyone has run into this issue.

Luis Cazares SSC Guru Points: 183706 More actions · Answer 3

All the options work if done properly, you might get better help if you share the errors you get or the problems you face.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

jonathanm 4432 SSChasing Mays Points: 619 More actions · Answer 4

Here is my code:

BULK INSERT [ThomasSci].[dbo].[DONNAWESNERINP]

FROM '\\111.17.10.23\global$\Stocking_Request\_macro\WESNERINPUT.txt';

GO

and here is the error message i get:

Msg 4861, Level 16, State 1, Line 1

Cannot bulk load because the file "\\111.17.10.23\global$\Stocking_Request\_macro\WESNERINPUT.txt" could not be opened. Operating system error code 5(Access is denied.).

jonathanm 4432 SSChasing Mays Points: 619 More actions · Answer 5

I also tried this code:

SELECT * INTO [ThomasSci].[dbo].[DONNAWESNERINP]

FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',

'Excel 12.0 Xml;HDR=YES;Database=\\111.17.10.23\global$\Stocking_Request\_macro\WESNERINPUT.xlsx',

'SELECT * FROM [Sheet1$]');

and got this error message:

Msg 7308, Level 16, State 1, Line 1

OLE DB provider 'Microsoft.ACE.OLEDB.12.0' cannot be used for distributed queries because the provider is configured to run in single-threaded apartment mode.

ZZartin SSC-Dedicated Points: 30921 More actions · Answer 6

jonathanm 4432 (9/11/2015)
Here is my code:
BULK INSERT [ThomasSci].[dbo].[DONNAWESNERINP]
FROM '\\111.17.10.23\global$\Stocking_Request\_macro\WESNERINPUT.txt';
GO
and here is the error message i get:
Msg 4861, Level 16, State 1, Line 1
Cannot bulk load because the file "\\111.17.10.23\global$\Stocking_Request\_macro\WESNERINPUT.txt" could not be opened. Operating system error code 5(Access is denied.).

Do you happen to have the file open another program like excel that puts a lock on it?

jonathanm 4432 SSChasing Mays Points: 619 More actions · Answer 7

Nothing else is open. I am not sure if it may be a compatibility issue because we are using 32-bit office but 64-bit SQL

Luis Cazares SSC Guru Points: 183706 More actions · Answer 8

Does the server have access to the path specified?

You might want to check this thread as well: http://www.sqlservercentral.com/Forums/Topic1300136-392-1.aspx

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

jonathanm 4432 SSChasing Mays Points: 619 More actions · Answer 9

Server does have access to that path and I have looked at that as well and I tried a lot of the mentioned solutions but still nothing. I am hoping it isn't the 32-bit vs 64-bit issue