Importing multiple Excel files.

Hello, I have a SQL Server 2000 database that I want to load with Excel
files. All the Excel files are in one folder and have the same column names.
What is the best way to get them into a table in the database? While
looping though the folder can you just do a bulk insert on the excel files?
"Don" <Don@.discussions.microsoft.com> wrote in message
news:510DB3BC-CAFD-42BC-8262-9D43B577A82D@.microsoft.com...
> Hello, I have a SQL Server 2000 database that I want to load with Excel
> files. All the Excel files are in one folder and have the same column
> names.
> What is the best way to get them into a table in the database? While
> looping though the folder can you just do a bulk insert on the excel
> files?
Use DTS. The following example uses a text file but it could equally be
Excel:
http://www.sqldts.com/default.aspx?246
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx

Importing multiple Excel files.

Hello, I have a SQL Server 2000 database that I want to load with Excel
files. All the Excel files are in one folder and have the same column names.
What is the best way to get them into a table in the database? While
looping though the folder can you just do a bulk insert on the excel files?"Don" <Don@.discussions.microsoft.com> wrote in message
news:510DB3BC-CAFD-42BC-8262-9D43B577A82D@.microsoft.com...
> Hello, I have a SQL Server 2000 database that I want to load with Excel
> files. All the Excel files are in one folder and have the same column
> names.
> What is the best way to get them into a table in the database? While
> looping though the folder can you just do a bulk insert on the excel
> files?
Use DTS. The following example uses a text file but it could equally be
Excel:
http://www.sqldts.com/default.aspx?246
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--sql

Importing multiple Excel files.

Hello, I have a SQL Server 2000 database that I want to load with Excel
files. All the Excel files are in one folder and have the same column names
.
What is the best way to get them into a table in the database? While
looping though the folder can you just do a bulk insert on the excel files?"Don" <Don@.discussions.microsoft.com> wrote in message
news:510DB3BC-CAFD-42BC-8262-9D43B577A82D@.microsoft.com...
> Hello, I have a SQL Server 2000 database that I want to load with Excel
> files. All the Excel files are in one folder and have the same column
> names.
> What is the best way to get them into a table in the database? While
> looping though the folder can you just do a bulk insert on the excel
> files?
Use DTS. The following example uses a text file but it could equally be
Excel:
http://www.sqldts.com/default.aspx?246
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

Importing from access with system Database

I am trying to import tables from my access database.

1st trying simple table, In ent mgr i right clicked the db folder
all task
import data
data source : Microsoft access
file name : c:\Data Management System\DMS.mdb
User name : mike (same used for lonin in access)
password : (same used for lonin in access)

advanced button:
jet Oledb: System database c:\Data Management System\Secured1.mdw

windows authentication
server (local)
database New -> dms

copy tables

now i see the items in my access database so i am guessing every step so far was correct

hey it worked !

ha,, hope u dont mind if i leave this here for anyone else, i ansd my own ?OK...but you know what's a pain...as soon as you join another group your DTS is HOSED...|||i dont get it what u mean join a group? dts hosage?

importing delimited files...

Hi,
I am importing a batch of comma delimited files. The process reads several
files and then moves them to an archive folder.
The process reads each line of each file into a temp db, but it does not
parse the line out onto fields. It just dumps the whole line into the table.
What I need is to be able to take that data and parse it out and insert it
into my new table, either AFTER the text has been imported or AS it is
imported.
Here is an example of the data that is being imported:
FLD1---
9876543.00, "Mr. John Doe, Jr.","John","Doe","1234","MyCity","MyState"
So I really need that to be into a table in individual fields
Any ideas on how to split this string up after or as it comes into SQL?
MartyUse Data Transformation Services.
AMB
"M.Smith" wrote:

> Hi,
> I am importing a batch of comma delimited files. The process reads several
> files and then moves them to an archive folder.
> The process reads each line of each file into a temp db, but it does not
> parse the line out onto fields. It just dumps the whole line into the tabl
e.
> What I need is to be able to take that data and parse it out and insert it
> into my new table, either AFTER the text has been imported or AS it is
> imported.
> Here is an example of the data that is being imported:
> FLD1---
> 9876543.00, "Mr. John Doe, Jr.","John","Doe","1234","MyCity","MyState"
> So I really need that to be into a table in individual fields
> Any ideas on how to split this string up after or as it comes into SQL?
> Marty
>
>|||Any more explanation on this response? Is there a reason you said use
DTS...you kind of left me hanging...
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:6F2C2699-F892-49DD-8CE9-6F90551299C2@.microsoft.com...
> Use Data Transformation Services.
>
> AMB
> "M.Smith" wrote:
>|||With DTS, you can specify a source file with certain attributes and a
destination and any transformations that you want. For instance, you can
make a Text File (Source) object and tell it that you have a comma-delimited
file. Then you can make a SQL Server object and point it at your database.
Than you can define a Data Pump that connects the two and performs any
transformations that you want on the data. You cound throw together a simpl
e
one in less that ten minutes.
Chris
"M.Smith" wrote:

> Any more explanation on this response? Is there a reason you said use
> DTS...you kind of left me hanging...
>
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in messag
e
> news:6F2C2699-F892-49DD-8CE9-6F90551299C2@.microsoft.com...
>
>

importing and archiving

I was wondering how i could use dts to import accessfiles and then archive
them to another folder. I've read some examples on sqldts.com but i still
can't figure it out.

Basically this is what i want:

- import an accessfile which has a name like this <companyname>_<today's
date>.mdb. The importfolder is called in my case d:\import

- for updating some fields, the dts should use the company's name from the
accessfile and check that with a lookuptable to translate the name into a
int value.

- after some processing, the dts should place the file to another folder. In
my case it is d:\archive

If someone can help me with this, i would be very greatful.

--

----------------
This mailbox protected from junk email by MailFrontier Desktop
from MailFrontier, Inc. http://info.mailfrontier.comI tend to avoid DTS. I prefer to use xp_cmdshell (to perform Command
line commands) and linked servers. In this case, use xp_cmdshell to
FTP the files down. Create a linked server to the Access MDB.
Process. Then use xp_cmdshell to move the file to the archive folder.

//FTP SP
CREATE PROCEDURE FTP
(
@.ftpserver varchar(50)='myserver' ,
@.ftpuser varchar(50)='myusername' ,
@.ftppwd varchar(50)='mypassword' ,
@.batchfile varchar(100)='\\myserver\path\ftpcmd.txt' ,
@.destfile varchar(100)='\\myserver\path\mydestfile.txt' ,
@.sourcefile varchar(50)='mysourcefile',
@.mode varchar(3)='get' -- get | put
)
as
DECLARE @.cmd varchar(1000)

SELECT @.cmd = 'echo ' + 'open ' + @.ftpserver + ' > ' + @.batchfile
EXEC master..xp_cmdshell @.cmd
SELECT @.cmd = 'echo ' + @.ftpuser + '>> ' + @.batchfile
EXEC master..xp_cmdshell @.cmd
SELECT @.cmd = 'echo ' + @.ftppwd + '>> ' + @.batchfile
EXEC master..xp_cmdshell @.cmd

SELECT @.cmd = 'echo ' + @.mode + ' ' + @.sourcefile + ' ' + @.destfile + '
>> ' + @.batchfile
EXEC master..xp_cmdshell @.cmd
SELECT @.cmd = 'echo ' + 'quit' + ' >> ' + @.batchfile
EXEC master..xp_cmdshell @.cmd
SELECT @.cmd = 'ftp -s:' + @.batchfile
EXEC master..xp_cmdshell @.cmd
SELECT @.cmd = 'del ' + @.batchfile
EXEC master..xp_cmdshell @.cmd|||"Ezekil" <ezekil@.lycos.com> wrote in message
news:41ffd97b$0$71093$cd19a363@.news.wanadoo.nl...
> I was wondering how i could use dts to import accessfiles and then archive
> them to another folder. I've read some examples on sqldts.com but i still
> can't figure it out.
> Basically this is what i want:
> - import an accessfile which has a name like this <companyname>_<today's
> date>.mdb. The importfolder is called in my case d:\import

Generate the current Access filename and assign it to a package global
variable in an ActiveX step, then use a Dynamic Properties task to set the
Access Connection properties.

> - for updating some fields, the dts should use the company's name from the
> accessfile and check that with a lookuptable to translate the name into a
> int value.

It depends if you're going to load the Access data into MSSQL or not - if
so, then an Execute SQL task could join the tables or call a stored proc.
Otherwise, ActiveX will work again.

> - after some processing, the dts should place the file to another folder.
> In my case it is d:\archive

Another ActiveX step, or just an Execute Process task to move the file.

Simon

Importing a zip folder

Hello all,
I will like to know how to create a process that unzips a folder and
import all its files into SQL Server 2000/2005.
Any suggestions will be greatly appreciated.
Thanks.You want to import it the files as binary objects into the SQL Server? I
haven't made a tool to do unzip and import all, but I have done a simple
binary file import utility for SQL 2000 enviornment that I can send over to
you if you want.
--
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
"SQL Pupil" wrote:
> Hello all,
> I will like to know how to create a process that unzips a folder and
> import all its files into SQL Server 2000/2005.
> Any suggestions will be greatly appreciated.
>
> Thanks.
>|||> I will like to know how to create a process that unzips a folder and
> import all its files into SQL Server 2000/2005.
> Any suggestions will be greatly appreciated.
There are a number of approaches. One method is to create a DTS package
that runs a command-line extract utility and then an ActiveX Script like the
one example below. You can similarly do this with SSIS and a .Net Script
task.
If you want to get fancy and forego the command-line extract, you extract
directly from .Net Script using ICSharpCode.SharpZipLib.dll
(http://www.icsharpcode.net/OpenSource/SharpZipLib/Download.aspx).
'DTS ActiveX example
Const adOpenKeyset = 1
Const adLockOptimistic = 3
Const adTypeBinary = 1
Function Main()
Set conn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")
Set fso = CreateObject("Scripting.FileSystemObject")
Set strm = CreateObject("ADODB.Stream")
ConnectionString = "Provider=SQLOLEDB" & _
";Data Source=MyServer" & _
";Initial Catalog=MyDatabase" & _
";Integrated Security=SSPI"
conn.Open ConnectionString
rs.Open "SELECT File_Name, File_Contents FROM dbo.MyTable", conn,
adOpenKeyset, adLockOptimistic
Set folder = fso.GetFolder("C:\MyExtractedFiles")
Set fileList = folder.Files
For Each file In fileList
InsertFile(file.Name)
Next
conn.Close
Main = DTSTaskExecResult_Success
End Function
Sub InsertFile(FileName)
strm.Type = adTypeBinary
strm.Open
strm.LoadFromFile FileName
rs.AddNew
rs.Fields("File_name") = FileName
rs.Fields("File_Contents") = strm.Read
rs.Update
strm.Close
End Sub
Hope this helps.
Dan Guzman
SQL Server MVP
"SQL Pupil" <guido_a_perez@.yahoo.com> wrote in message
news:1182447929.191367.191100@.w5g2000hsg.googlegroups.com...
> Hello all,
> I will like to know how to create a process that unzips a folder and
> import all its files into SQL Server 2000/2005.
> Any suggestions will be greatly appreciated.
>
> Thanks.
>|||Sure, any help that you can provide or any ideas, they all are going
to be welcome.
Thanks again|||I don't know .NET but my code is pretty similar to what Dan has offered here.
My code has some extra stuff, for pulling the table information and such on
the fly. It's still under fix up though, because I only use it for few
databases. So I can't say it will work all accross the board. But if you
want I can still send it ;-)
--
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
"Dan Guzman" wrote:
> > I will like to know how to create a process that unzips a folder and
> > import all its files into SQL Server 2000/2005.
> >
> > Any suggestions will be greatly appreciated.
> There are a number of approaches. One method is to create a DTS package
> that runs a command-line extract utility and then an ActiveX Script like the
> one example below. You can similarly do this with SSIS and a .Net Script
> task.
> If you want to get fancy and forego the command-line extract, you extract
> directly from .Net Script using ICSharpCode.SharpZipLib.dll
> (http://www.icsharpcode.net/OpenSource/SharpZipLib/Download.aspx).
>
> 'DTS ActiveX example
> Const adOpenKeyset = 1
> Const adLockOptimistic = 3
> Const adTypeBinary = 1
> Function Main()
> Set conn = CreateObject("ADODB.Connection")
> Set rs = CreateObject("ADODB.Recordset")
> Set fso = CreateObject("Scripting.FileSystemObject")
> Set strm = CreateObject("ADODB.Stream")
> ConnectionString = "Provider=SQLOLEDB" & _
> ";Data Source=MyServer" & _
> ";Initial Catalog=MyDatabase" & _
> ";Integrated Security=SSPI"
> conn.Open ConnectionString
> rs.Open "SELECT File_Name, File_Contents FROM dbo.MyTable", conn,
> adOpenKeyset, adLockOptimistic
> Set folder => fso.GetFolder("C:\MyExtractedFiles")
> Set fileList = folder.Files
> For Each file In fileList
> InsertFile(file.Name)
> Next
> conn.Close
> Main = DTSTaskExecResult_Success
> End Function
> Sub InsertFile(FileName)
> strm.Type = adTypeBinary
> strm.Open
> strm.LoadFromFile FileName
> rs.AddNew
> rs.Fields("File_name") = FileName
> rs.Fields("File_Contents") = strm.Read
> rs.Update
> strm.Close
> End Sub
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "SQL Pupil" <guido_a_perez@.yahoo.com> wrote in message
> news:1182447929.191367.191100@.w5g2000hsg.googlegroups.com...
> > Hello all,
> >
> > I will like to know how to create a process that unzips a folder and
> > import all its files into SQL Server 2000/2005.
> >
> > Any suggestions will be greatly appreciated.
> >
> >
> > Thanks.
> >
>|||Mohit, since I am in the process of gathering all the SQL info and
knowledge that I can get, anything that you can send me will be very
usefull.
Thanks again|||Emailed to your profile address ;-).
--
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
"SQL Pupil" wrote:
> Mohit, since I am in the process of gathering all the SQL info and
> knowledge that I can get, anything that you can send me will be very
> usefull.
> Thanks again
>

Importing a zip folder

Hello all,
I will like to know how to create a process that unzips a folder and
import all its files into SQL Server 2000/2005.
Any suggestions will be greatly appreciated.
Thanks.You want to import it the files as binary objects into the SQL Server? I
haven't made a tool to do unzip and import all, but I have done a simple
binary file import utility for SQL 2000 enviornment that I can send over to
you if you want.
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
"SQL Pupil" wrote:

> Hello all,
> I will like to know how to create a process that unzips a folder and
> import all its files into SQL Server 2000/2005.
> Any suggestions will be greatly appreciated.
>
> Thanks.
>|||> I will like to know how to create a process that unzips a folder and
> import all its files into SQL Server 2000/2005.
> Any suggestions will be greatly appreciated.
There are a number of approaches. One method is to create a DTS package
that runs a command-line extract utility and then an ActiveX Script like the
one example below. You can similarly do this with SSIS and a .Net Script
task.
If you want to get fancy and forego the command-line extract, you extract
directly from .Net Script using ICSharpCode.SharpZipLib.dll
(http://www.icsharpcode.net/OpenSour...b/Download.aspx).
'DTS ActiveX example
Const adOpenKeyset = 1
Const adLockOptimistic = 3
Const adTypeBinary = 1
Function Main()
Set conn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")
Set fso = CreateObject("Scripting.FileSystemObject")
Set strm = CreateObject("ADODB.Stream")
ConnectionString = "Provider=SQLOLEDB" & _
";Data Source=MyServer" & _
";Initial Catalog=MyDatabase" & _
";Integrated Security=SSPI"
conn.Open ConnectionString
rs.Open "SELECT File_Name, File_Contents FROM dbo.MyTable", conn,
adOpenKeyset, adLockOptimistic
Set folder =
fso.GetFolder("C:\MyExtractedFiles")
Set fileList = folder.Files
For Each file In fileList
InsertFile(file.Name)
Next
conn.Close
Main = DTSTaskExecResult_Success
End Function
Sub InsertFile(FileName)
strm.Type = adTypeBinary
strm.Open
strm.LoadFromFile FileName
rs.AddNew
rs.Fields("File_name") = FileName
rs.Fields("File_Contents") = strm.Read
rs.Update
strm.Close
End Sub
Hope this helps.
Dan Guzman
SQL Server MVP
"SQL Pupil" <guido_a_perez@.yahoo.com> wrote in message
news:1182447929.191367.191100@.w5g2000hsg.googlegroups.com...
> Hello all,
> I will like to know how to create a process that unzips a folder and
> import all its files into SQL Server 2000/2005.
> Any suggestions will be greatly appreciated.
>
> Thanks.
>|||Sure, any help that you can provide or any ideas, they all are going
to be welcome.
Thanks again|||I don't know .NET but my code is pretty similar to what Dan has offered here
.
My code has some extra stuff, for pulling the table information and such on
the fly. It's still under fix up though, because I only use it for few
databases. So I can't say it will work all accross the board. But if you
want I can still send it ;-)
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
"Dan Guzman" wrote:

> There are a number of approaches. One method is to create a DTS package
> that runs a command-line extract utility and then an ActiveX Script like t
he
> one example below. You can similarly do this with SSIS and a .Net Script
> task.
> If you want to get fancy and forego the command-line extract, you extract
> directly from .Net Script using ICSharpCode.SharpZipLib.dll
> (http://www.icsharpcode.net/OpenSour...b/Download.aspx).
>
> 'DTS ActiveX example
> Const adOpenKeyset = 1
> Const adLockOptimistic = 3
> Const adTypeBinary = 1
> Function Main()
> Set conn = CreateObject("ADODB.Connection")
> Set rs = CreateObject("ADODB.Recordset")
> Set fso = CreateObject("Scripting.FileSystemObject")
> Set strm = CreateObject("ADODB.Stream")
> ConnectionString = "Provider=SQLOLEDB" & _
> ";Data Source=MyServer" & _
> ";Initial Catalog=MyDatabase" & _
> ";Integrated Security=SSPI"
> conn.Open ConnectionString
> rs.Open "SELECT File_Name, File_Contents FROM dbo.MyTable", conn,
> adOpenKeyset, adLockOptimistic
> Set folder =
> fso.GetFolder("C:\MyExtractedFiles")
> Set fileList = folder.Files
> For Each file In fileList
> InsertFile(file.Name)
> Next
> conn.Close
> Main = DTSTaskExecResult_Success
> End Function
> Sub InsertFile(FileName)
> strm.Type = adTypeBinary
> strm.Open
> strm.LoadFromFile FileName
> rs.AddNew
> rs.Fields("File_name") = FileName
> rs.Fields("File_Contents") = strm.Read
> rs.Update
> strm.Close
> End Sub
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "SQL Pupil" <guido_a_perez@.yahoo.com> wrote in message
> news:1182447929.191367.191100@.w5g2000hsg.googlegroups.com...
>|||Mohit, since I am in the process of gathering all the SQL info and
knowledge that I can get, anything that you can send me will be very
usefull.
Thanks again|||Emailed to your profile address ;-).
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
"SQL Pupil" wrote:

> Mohit, since I am in the process of gathering all the SQL info and
> knowledge that I can get, anything that you can send me will be very
> usefull.
> Thanks again
>