Database Entry Problem (MSSQL-2005) | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Database Entry Problem (MSSQL-2005)

Hi,
I am preparing a database in MSSQL for all cities list of the world. I have a list of all cities in MS-Excel file.
is there any way to enter these values automatically into database from Excel file or I have to enter the values manually? I am afraid these all values are 8000+ values. Please let me know.
Thanking You
Anshu
You can use DTS for importing those data or else from ado you can import data with VB —————————————-
http://spaces.msn.com/members/dineshasanka

Try OpenRowset as suggested here
http://sql-server-performance.com/forum/topic.asp?TOPIC_ID=8440 Madhivanan Failing to plan is Planning to fail
If you have Access installed too, then import the Excel sheet into Access. Next, add a linked ODBC table. File > Get External Data > Link Tables This brings up the Link dialog box. For "Files of Type" select the last option "ODBC databases", and a wizard will pop up that guides you through the steps. After the link to table on SQL Server is established, you run an INSERT action query. There may be ways of doing this directly from Excel, but I’m never comfortable with true data in Excel.
If this is a one time import just to fill the SQL Server table, go with the DTS Import/Export wizard. You can imvoke it in Enterprise Manager with a right mouse click.
Sounds like a straight forward import. Sorry, just read that it’s SQL Server 2005. No idea where and how to invoke the wizard here. [:I]

Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Ich unterstütze PASS Deutschland e.V. http://www.sqlpass.de)

DTS is still valid in SQL 2005, before that if you’re handling this data for a production environment then I suggest to wait until end of November as SQL 2K5 RTM will be released and may have few advantages more as the current one is in beta. SQL 2005 introduces SSIS –http://www.databasejournal.com/features/mssql/article.php/3525886 for more information. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
Hi,
I am sorry to say, I couldnot find DTS wizard in SQL Server Express edition. Also I couldn’t find DTS Wizard in Visual Studio 2005 evaluation version.
Please let me know.
Thanking You
Anshu
Have you tried the Link I suggested? Madhivanan Failing to plan is Planning to fail
Yes, I am trying, I will just let you know in couple of moments.
Thanking You
Anshu
As an aside … if there are just 8,000 cities in your world then what planet are you on?[<img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ />]
may be top 8000 cities [<img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ />]<br /><br />Madhivanan<br /><br />Failing to plan is Planning to fail
Hi Guys,<br />Only the selected cities I am cosidering…<img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ />)<br /><br />Anshu
SQL Express 2005 is like MSDE you will not get any GUI tools with it and you need to install SQL Management studio for GUI tools. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
… or add ODBC table links to your Office application and upload the data that way.
Hi Madhivanan, I just tried by the way you suggested. But I am sorry to say that it is showing an error that AD-Hoc Qyery should be on.
I tried to switch it on by SQL Server Express Edition. But I am sorry to say that in SQL Server Express Evaluation Version it is not showing the option for AD-Hoc Queries.
Please let me know how to switch it on so that I can run the query. Thanking You.
Anshu
See if this helps you
http://support.microsoft.com/default.aspx?scid=kb;en-us;327489 Madhivanan Failing to plan is Planning to fail
Hi Madhivanan, Thanking you for providing your suggestions. But I am sorry to say that I am unable to find to run any method for Ad-Hoc queries. I think there is no way to switch it on in SQL server express 2005. If you have any idea then please let me know.
Thanking You,
Anshu
As a first doubt can you confirm whether you’re providing/managing this data for production purpose or for your training. One of the MSDN article refers …some features are removed from SQL Server Express compared to MSDE. These features include DTS, replication publishing, and SQL Agent.
Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
Hi MAdhivanan,
Thanking you, SQL Express is running AD-Hoc Queries. I also have one more novice question. In the query you provided, where to put the name of the column inthis query.
Also should I provide the SheetNumber of Excel Sheet. Please help me.
Anshu
You should use Sheet Name and not Sheet No Use Insert into tableName Select * from OpenRowset(….. Select City from [sheetName$]) Madhivanan Failing to plan is Planning to fail
Hi Madhivanan, I am just running this query INSERT INTO city (cityName) // Where cityName is the name of the column in the city table//
SELECT *
FROM
OPENROWSET(
‘Microsoft.Jet.OLEDB.4.0’,
‘Excel 8.0;Database=c:city.xls; HDR=YES’,
‘SELECT A FROM [city]’
) AS derivedtbl_1 but it is showing error that can not get the column information from the OLE database provider.
Please help me if you see any mistake in this query. Thanking You
Anshu
If the sheet City has the column(heading) named A it will work correctly Madhivanan Failing to plan is Planning to fail
Hi Madhivanan, Thanking you for your reply. I am executing query like this
INSERT INTO city (cityName)
SELECT *
FROM OPENROWSET(
‘Microsoft.Jet.OLEDB.4.0’,
‘Excel 8.0;Database=c:city.xls; HDR=YES’,
‘SELECT * FROM [city]’) AS derivedtbl_1 so I Think now it should execute because we are selecting *. But I am sorry to say it is again showing error that can not have column information of OLE data provider. Please let me know where to look for that. thanking you very very much
Anshu
Can you run the SELECT part only, and see how many columns are returned?
]]>