import data in sql server 2005 express edition

Question

Post reply

import data in sql server 2005 express edition

sql_2005_fan

SSCommitted

Points: 1871
More actions
January 30, 2009 at 8:29 am

#204749

How can I import a text file into a sql server 2005 express edition?
Thanks.

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

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

Ramesh Saive SSC-Insane Points: 24275 More actions · Answer 1

There are many ways to do it...

1. Import Export Wizard

2. SSIS

3. Command Utilities such as BCP, SQLCMD etc.

4. Linked Servers

--Ramesh

sql_2005_fan SSCommitted Points: 1871 More actions · Answer 2

I can not see import/export wizard in the express edition by right clicking on tasks at the database level. Can you let me know where can I find import/export wizard in express edition.

Thanks.

Ramesh Saive SSC-Insane Points: 24275 More actions · Answer 3

You should see the options "Import Data..." OR "Export Data..." under Tasks option of Database..

--Ramesh

freens77 SSC-Addicted Points: 495 More actions · Answer 4

I don't see the options "Import Data" or "Export Data" under the expression version of Management Studio either.

Should we be seeing that in Express?

Lowell SSC Guru Points: 323518 More actions · Answer 5

import and export of any kind, where thru the Management Studio or SSIS, are only part of Standard,Enterprise and Develoepr editions; they are not included in the freebie versions.

your best bet is to add the folder cntaining the text file or files as a linked server.

you can then query the tables and pull them into tables in your server.

the filenames must only have ONE period in them (so filename like mystuff.config.txt is not valid)

[font="Courier New"]

--#################################################################################################

--Linked server Syntax for Folder Full Of Text Files

--#################################################################################################

--add a folder as a linked server to access all .txt and .csv files in the folder

DECLARE @server SYSNAME,

@srvproduct NVARCHAR(256),

@provider NVARCHAR(256),

@datasrc NVARCHAR(100),

@location NVARCHAR(100),

@provstr NVARCHAR(100),

@catalog SYSNAME,

@sql VARCHAR(1000)

SET @server = N'TxtSvr'

SET @srvproduct = N'Jet 4.0'

SET @provider = N'Microsoft.Jet.OLEDB.4.0'

SET @datasrc = N'C:\YourFolderContainingYourTXTFile'

SET @provstr = 'Text'

EXEC sp_addlinkedserver @server,@srvproduct,@provider,@datasrc,NULL,@provstr

--alternate, non param syntax

--===== Create a linked server to the drive and path you desire.

EXEC dbo.sp_AddLinkedServer TxtSvr,

'Jet 4.0',

'Microsoft.Jet.OLEDB.4.0',

'C:\YourFolderContainingYourTXTFile',

NULL,

'Text'

GO

--===== Set up login mappings.

EXEC dbo.sp_AddLinkedSrvLogin TxtSvr, FALSE, NULL, Admin, NULL

GO

--===== List the tables in the linked server which is really a list of

-- file names in the directory. Note that the "#" sign in the

-- Table_Name is where the period in the filename actually goes.

EXEC dbo.sp_Tables_Ex TxtSvr

GO

--===== Query one of the files by using a four-part name.

SELECT *

FROM TxtSvr...[xmlmap#txt]

--===== Drop the text server

EXEC dbo.sp_DropServer 'TxtSvr', 'DropLogins'

GO

[/font]

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

PedroSimao SSC Enthusiast Points: 199 More actions · Answer 6

Doesn't DTSWizzard ship with SQLEXPRESS 2008?

Should be in \Program Files\Microsoft SQL Server\100\DTS\Binn

Although it's a sql2008 app you should be able to import to sql2005

Ramesh Saive SSC-Insane Points: 24275 More actions · Answer 7

Actually, DTS Wizard is not supplied with the Express Edition of SQL 2005 (and its available in 2K8), though you can separately download & install the DTS Components and run the wizard from the location "..\Program Files\Microsoft SQL Server\100\DTS\Binn"

--Ramesh

Gift Peddie SSC Guru Points: 73570 More actions · Answer 8

It is in SQL Server 2005 Express Advanced edition the tool kit version which I think require the user to unistall the standard SQL Server Express edition. Check the links below for how to find it and how to download and install it.

http://mobiledeveloper.wordpress.com/2007/01/31/data-import-export-with-sql-server-express-using-dts-wizard/

http://www.microsoft.com/downloadS/details.aspx?familyid=D434DC36-A24D-44EE-937E-553C382557E3&displaylang=en

Kind regards,
Gift Peddie