Importing data into SQL from Excel

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

Importing data into SQL from Excel

Author

Topic

mapidea
Posting Yak Master

124 Posts

Posted - 2011-12-14 : 12:58:27

I am using Import data task to import excel data which has dates.

It gives me null for dates with day more than 12.

I have the machines local date and time set to English India.

Sample data

12/09/2011 15:59 989.00 13.00

13/12/2011 09:41:29 962.3 975

THe 13/12/2011 gives null.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-14 : 13:04:42

how are you doing import? using OPENROWSET or export/import wizard?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

mapidea
Posting Yak Master

124 Posts

Posted - 2011-12-14 : 20:14:04

export import wizard

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-14 : 23:54:36

try using openrowset and explicitly change dates to iso format using convert

http://visakhm.blogspot.com/2011/12/why-iso-format-is-recommended-while.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

mapidea
Posting Yak Master

124 Posts

Posted - 2011-12-15 : 00:51:43

Visakh,

Please give the SQL how I can do that using ISO format and openrowset. Please correct me if I am wrong.

INSERT INTO Axis (date, price)
SELECT *
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\dataSource.xls;HDR=YES',
'SELECT * FROM [Foglio1$]')

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-15 : 03:06:14

[code]
INSERT INTO Axis (date, price)
SELECT CONVERT(datetime,[datefield],121),pricefield
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\dataSource.xls;HDR=YES',
'SELECT * FROM [Foglio1$]')
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

mapidea
Posting Yak Master

124 Posts

Posted - 2011-12-16 : 04:21:17

Thanks a lot Visakh.

I did it through import export wizard itself. Though my goal is achieved

I want to ask you how can we specify a field (datefield, pricefield) in excel. It was giving me a "Insert into " error executing the code.

What should be the format of the excel? Do we need to name the fields in specific way.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-16 : 06:24:17

quote:
Originally posted by mapidea

Thanks a lot Visakh.

I did it through import export wizard itself. Though my goal is achieved

I want to ask you how can we specify a field (datefield, pricefield) in excel. It was giving me a "Insert into " error executing the code.

What should be the format of the excel? Do we need to name the fields in specific way.

can you post exact error message?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources