OPENROWSET OR BULK - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

OPENROWSET OR BULK

Author

Topic

santana
Yak Posting Veteran

72 Posts

Posted - 2010-04-20 : 05:52:47

Hi,

Could you help me, please?!
I need to every month to import data from Excel file to Sql Server table.
Both, Excel and SQL table there are the same name/type of columns.

Do you know that can I use openrowset or bulk or any other function?
And How can I use this?

Thanks a lot!

Regards, Santana

santana
Yak Posting Veteran

72 Posts

Posted - 2010-04-20 : 06:32:29

I tried to things:

1) INSERT INTO dbo.fin_ldg_test([year], [month], month_name, product, category,customer,actual_volume, budget_volume)
SELECT * FROM OPENROWSET(
BULK 'c:\test_bulk.xlsm',
SINGLE_BLOB
) AS x

For that I received the msg: Msg 120, Level 15, State 1, Line 1
The select list for the INSERT statement contains fewer items than the insert list. The number of SELECT values must match the number of INSERT columns.

--> But I count 8 columns in the table and in the excel file

2) BULK INSERT dbo.fin_ldg_test
FROM 'c:\test_bulk.xlsm'
WITH (CODEPAGE = 850);

For that I received the messager:
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 1 (Year).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 2, column 1 (Year).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 3, column 1 (Year).

The first line in my excel i have : 2007 1 Jan 757 LDG Active NULL 0.5

I don't know what I did wrong. I would like to import data from excel file.

Regards,

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-04-20 : 06:49:03

Have you tried this?

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49926

Madhivanan

Failing to plan is Planning to fail

santana
Yak Posting Veteran

72 Posts

Posted - 2010-04-20 : 08:20:09

Yes:

Insert into dbo.fin_ldg_test Select * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 2007;Database=C:\test_bulk.xls;HDR=YES',
'SELECT * FROM [Sheet1$]')

But I received this error:

OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" returned message "Could not find installable ISAM.".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".

???

Any idea?

Thanks a lot!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-04-20 : 08:39:47

http://support.microsoft.com/kb/209805

Madhivanan

Failing to plan is Planning to fail

santana
Yak Posting Veteran

72 Posts

Posted - 2010-04-20 : 09:09:15

I changed my command, but it still didnt work:

INSERT INTO finance.dbo.fin_ldg_test([year])
SELECT *
FROM
OPENROWSET('MSDASQL',
'Driver={Microsoft Text Driver (*.txt; *.csv)};
DEFAULTDIR=C:Data;Extensions=CSV;',
'SELECT * FROM test_bulk.csv')

error msg:

OLE DB provider "MSDASQL" for linked server "(null)" returned message "[Microsoft][ODBC Text Driver] '(unknown)' is not a valid path. Make sure that the path name is spelled correctly and that you are connected to the server on which the file resides.".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "(null)".

Thanks a lot!
Regards,

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-04-20 : 09:15:27

Make sure the file exists in server's directory

Madhivanan

Failing to plan is Planning to fail

santana
Yak Posting Veteran

72 Posts

Posted - 2010-04-20 : 09:18:30

Yes. It is.
The file test_bulk.csv is in the directory c:

???

santana
Yak Posting Veteran

72 Posts

Posted - 2010-04-20 : 09:47:21

I don't understand what happen.
Because I create one excel file with only one column - year.
And it worked.

But when I changed the file, and included one more column - month.
I had problem. I included the month column into the table too

INSERT INTO finance.dbo.fin_ldg_test ([year], [month])
SELECT * FROM OPENROWSET(
BULK 'C:\test.csv',
SINGLE_BLOB

)AS x

Msg 120, Level 15, State 1, Line 1
The select list for the INSERT statement contains fewer items than the insert list. The number of SELECT values must match the number of INSERT columns.

Please!!! Help me!!!

Regards,

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-04-20 : 10:05:50

See what this returns

SELECT *
FROM
OPENROWSET('MSDASQL',
'Driver={Microsoft Text Driver (*.txt; *.csv)};
DEFAULTDIR=C:\Data\;Extensions=CSV;',
'SELECT * FROM test_bulk.csv')

Madhivanan

Failing to plan is Planning to fail

santana
Yak Posting Veteran

72 Posts

Posted - 2010-04-20 : 10:23:59

The same problem.
I looked again. I have year and month, both int, into the excel file and table.

OLE DB provider "MSDASQL" for linked server "(null)" returned message "[Microsoft][ODBC Text Driver] The Microsoft Jet database engine could not find the object 'test_bulk.csv'. Make sure the object exists and that you spell its name and the path name correctly.".
Msg 7350, Level 16, State 2, Line 1
Cannot get the column information from OLE DB provider "MSDASQL" for linked server "(null)".

santana
Yak Posting Veteran

72 Posts

Posted - 2010-04-20 : 10:40:21

this worked:

BULK
INSERT finance.dbo.fin_ldg_test
FROM 'c:\test.csv'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)
GO

I need to fixed two problems:

1 - How I take off the header?
2 - I have problem with columns with decimal values like 0.25 (Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 2, column 8
(Budget_volume))

I appreciate any help.Please!

thanks for all!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-04-21 : 02:45:34

quote:
Originally posted by santana

this worked:

BULK
INSERT finance.dbo.fin_ldg_test
FROM 'c:\test.csv'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)
GO

I need to fixed two problems:

1 - How I take off the header?
2 - I have problem with columns with decimal values like 0.25 (Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 2, column 8
(Budget_volume))

I appreciate any help.Please!

thanks for all!

1 Use Firstrow=2, before FIELDTERMINATOR = ',',
2 Make sure that the datatype of the tables matches with that of the data available in the file

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources