Import from Excel question

Hello. This may be a silly question. I think I already know the answer because I tried it and didn't work, but just in case I'm missing something, can an Excel file be imported into SQL Server if the column names are on the left? in other words, all the column names instead of being on the top of the sheet, they run down on cell A.

Thank you!!Short answer: yes.

Long answer: not easily.

Best answer: use Excel's "Paste special" functionality to transform the data first and save yourself days of headache.

blindmansql

Import Export in SQL Server 2005

In case of Sql Server 2000, we have the option for import / Export data using DTS by creating objects.

Doing this we are getting the default value of colms of tables int the destination database.

But in case of SQL Server 2005 i am not getting this feature.

If i am using the Import /Export I am not getting the default values.

So first i am generating the script for table and create table in the destination database and then using import/export option to copy data.

Is there any other option to do this?

DEFAULT values are elements of the table schema. If you are not getting the default values, there are two possible explanitions.

1. Your script that creates the tables does not include the DEFAULT values.

2. Your data has empty strings instead of NULL values (DEFAULT values are ONLY used if the data is missing or NULL.)

|||

Yah what you are saying is right.

But what i am asking is , by default i am not getting default values while using import/ export to a new data base.

For that i am first generating script for tables and the table syntax include the default. and then using import/export data.

Is it possible, with out generating the script for tables from the existing database will get the default value in the new database.

|||Yes it is possible -but ONLY if the missing data is [NULL], NOT empty string [''].|||

You need to use the SQL Server specific version of import/export, look here for more info;

http://blogs.msdn.com/euanga/archive/2006/07/18/668916.aspx

sql

Import Export in SQL Server 2005

In case of Sql Server 2000, we have the option for import / Export data using DTS by creating objects.

Doing this we are getting the default value of colms of tables int the destination database.

But in case of SQL Server 2005 i am not getting this feature.

If i am using the Import /Export I am not getting the default values.

So first i am generating the script for table and create table in the destination database and then using import/export option to copy data.

Is there any other option to do this?

DEFAULT values are elements of the table schema. If you are not getting the default values, there are two possible explanitions.

1. Your script that creates the tables does not include the DEFAULT values.

2. Your data has empty strings instead of NULL values (DEFAULT values are ONLY used if the data is missing or NULL.)

|||

Yah what you are saying is right.

But what i am asking is , by default i am not getting default values while using import/ export to a new data base.

For that i am first generating script for tables and the table syntax include the default. and then using import/export data.

Is it possible, with out generating the script for tables from the existing database will get the default value in the new database.

|||Yes it is possible -but ONLY if the missing data is [NULL], NOT empty string [''].|||

You need to use the SQL Server specific version of import/export, look here for more info;

http://blogs.msdn.com/euanga/archive/2006/07/18/668916.aspx

import excel to sql using openquery

Hi!

I'm relatively new to sql.I want to import data from an excel file into a sql table.Could I use Openquery?In this case,what should I specify at the linked_server parameter?All I know is the SQL server name,\\tmav035a.

Thanks in advance!

http://www.sql-server-helper.com/tips/read-import-excel-file-p01.aspx

AMB