Importing multiple Access databases

Hi,
I have 30 remote sites that each day send an access
database of sales figures for that day, how can I automate
the process of importing these into an SQL database. I run
SQL server 2000.
As simple a solution as possible please as I am new to SQL
server.
Thanks Dave
Data Transform Services (DTS) would typically be used to import Access data in to SQL server tables, and packages can be scheduled. There is coverage of DTS in BOL and there are several DTS books (Professional SQL Server 2000 DTS is good).
A way to get started is to use the DTS Import/Export Wizard (see tools/Data Transformation Services) with one of the Access databases as the source and the SQL Server database as the destination. Check the Save Package box (select SQL Server for storage
) to save your package to SQL Server (you might want to uncheck the execute imediate box) and use it as a template.

> Hi,
> I have 30 remote sites that each day send an access
> database of sales figures for that day, how can I automate
> the process of importing these into an SQL database. I run
> SQL server 2000.
> As simple a solution as possible please as I am new to SQL
> server.
> Thanks Dave
>
|||Assuming the access db's always have the same names, and are located in the
same way, you should be able to simply import the data into your SQL
database, by right clicking the database, selecting import, and then going
through the wizard which is reasonably self explanatory.
At the end of the wizard you will be asked if you want to run the import
now, save it, or schedule it. If you choose the schedule option you can then
select a time for that data to be imported, and how often it should be done,
and from there it should go through without a hitch. To test it, go to
Management, SQL Server Agent, Jobs, and you should see your DTS job in the
right hand side. Right click on it and tell it to start, and it will go
through and perform the import. Once finished you can see the status of the
job, and if necessary view the job history and details of what happened if
it fails for some reason.
Hope that helps
Keith
"Dave Tordoff" <david.tordoff@.companioncare.co.uk> wrote in message
news:167d01c46f3f$5c33fbf0$a601280a@.phx.gbl...
> Hi,
> I have 30 remote sites that each day send an access
> database of sales figures for that day, how can I automate
> the process of importing these into an SQL database. I run
> SQL server 2000.
> As simple a solution as possible please as I am new to SQL
> server.
> Thanks Dave

Importing multiple Access databases

Hi,
I have 30 remote sites that each day send an access
database of sales figures for that day, how can I automate
the process of importing these into an SQL database. I run
SQL server 2000.
As simple a solution as possible please as I am new to SQL
server.
Thanks DaveAssuming the access db's always have the same names, and are located in the
same way, you should be able to simply import the data into your SQL
database, by right clicking the database, selecting import, and then going
through the wizard which is reasonably self explanatory.
At the end of the wizard you will be asked if you want to run the import
now, save it, or schedule it. If you choose the schedule option you can then
select a time for that data to be imported, and how often it should be done,
and from there it should go through without a hitch. To test it, go to
Management, SQL Server Agent, Jobs, and you should see your DTS job in the
right hand side. Right click on it and tell it to start, and it will go
through and perform the import. Once finished you can see the status of the
job, and if necessary view the job history and details of what happened if
it fails for some reason.
Hope that helps
Keith
"Dave Tordoff" <david.tordoff@.companioncare.co.uk> wrote in message
news:167d01c46f3f$5c33fbf0$a601280a@.phx.gbl...
> Hi,
> I have 30 remote sites that each day send an access
> database of sales figures for that day, how can I automate
> the process of importing these into an SQL database. I run
> SQL server 2000.
> As simple a solution as possible please as I am new to SQL
> server.
> Thanks Dave

Importing multiple Access databases

Hi,
I have 30 remote sites that each day send an access
database of sales figures for that day, how can I automate
the process of importing these into an SQL database. I run
SQL server 2000.
As simple a solution as possible please as I am new to SQL
server.
Thanks DaveData Transform Services (DTS) would typically be used to import Access data
in to SQL server tables, and packages can be scheduled. There is coverage o
f DTS in BOL and there are several DTS books (Professional SQL Server 2000
DTS is good).
A way to get started is to use the DTS Import/Export Wizard (see tools/Data
Transformation Services) with one of the Access databases as the source and
the SQL Server database as the destination. Check the Save Package box (se
lect SQL Server for storage
) to save your package to SQL Server (you might want to uncheck the execute
imediate box) and use it as a template.

> Hi,
> I have 30 remote sites that each day send an access
> database of sales figures for that day, how can I automate
> the process of importing these into an SQL database. I run
> SQL server 2000.
> As simple a solution as possible please as I am new to SQL
> server.
> Thanks Dave
>|||Assuming the access db's always have the same names, and are located in the
same way, you should be able to simply import the data into your SQL
database, by right clicking the database, selecting import, and then going
through the wizard which is reasonably self explanatory.
At the end of the wizard you will be asked if you want to run the import
now, save it, or schedule it. If you choose the schedule option you can then
select a time for that data to be imported, and how often it should be done,
and from there it should go through without a hitch. To test it, go to
Management, SQL Server Agent, Jobs, and you should see your DTS job in the
right hand side. Right click on it and tell it to start, and it will go
through and perform the import. Once finished you can see the status of the
job, and if necessary view the job history and details of what happened if
it fails for some reason.
Hope that helps
Keith
"Dave Tordoff" <david.tordoff@.companioncare.co.uk> wrote in message
news:167d01c46f3f$5c33fbf0$a601280a@.phx.gbl...
> Hi,
> I have 30 remote sites that each day send an access
> database of sales figures for that day, how can I automate
> the process of importing these into an SQL database. I run
> SQL server 2000.
> As simple a solution as possible please as I am new to SQL
> server.
> Thanks Davesql

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 Data??

I have a process that calls a proc that BCP's a delimited file into a table. Well the SOX police say a header and footer must be added to the file. Needless to say this screws my BCP process.

Does anyone know how to strip a header and footer record from a text file using transact sql or have any other suggestions to strip the records?Well, -F will allow you to bypass the headers... If you can magically devine the number of rows, then -L will allow you to bypass the footers.

If that doesn't suffice, you can always use brute force! Either specify a format file and a batch size of 1 row (really ugly performance), or BCP into a staging table, then only copy the rows of interest from the staging table to the production table.

The other option that will get the job done, but might infuriate the SOX-meisters would be to use the "Flintstone" method... Write one utility to apply the requisite header and footer, and a separate utility to remove the header and footer before using BCP to import the data!

-PatP|||Yeah, well...I'm sure SOX is gonna ask you to interogate thos headers and trailers as well...

bcp the whole damn thing in to a single column table varchar(8000)

Sounds like a mainframe file, so do you have record identifiers?

Like 'H', 'D', and 'T'?|||Yeah, well...I'm sure SOX is gonna ask you to interogate thos headers and trailers as well...Oh! You're no fun!
bcp the whole damn thing in to a single column table varchar(8000)Nothing quite like brute force! 'Tain't pretty, but it does get the job done.

-PatP|||What's funny is they don't care if the header and footer are interogated. I'm lobbying to remove the footer since I can eliminate the header with -F.

My record/row delimiter is {CR}{LF}. I've decided my worse case senario is to add an extra column to the file and BCP the file into a staging table that populates the target table with all records where the new column is null. You know, populate the new column with the header and footer data and leave them blank for all other records. Still too much damn work for such a small problem.|||Let me ask you...what other tool could you possibly need in your toolbox, when GOD made this wonderful thing called a sledge hammer?

Peter,

The Header and trailer thing has always been a pain...

Add an IDENTITY column to get the last and first row...

(OK Pat, release the hounds...)

Do your intergoation and save the stats...

The use a simple bcp, with your first row = 2 and your last row = COUNT(*)

Then perform the audits...

I also like to do an INSERT with parsing to the final destination...but I like what I sketched out above better...

OK, now the discussion about how the data may not get loaded to the table in the same manner that it's in the file...

Take it away Pat...

(did I piss him off too?)|||Nah, it takes LOTS more than that to irrigate me!

I'm actually fine with that idea... The staging table was actually in my first posting, and an identity column makes processing easy. The only thing that might make it a booger to process would be variable length columns with delimiters, although SQL Server can handle that too (at some performance penalty relative to the way that BCP would handle it).

I'm for whatever works, and the less effort needed to get there, the better I like it! I've only got time for so much schtuff, and I really don't want to do any more than I have to!

-PatP

Importing Access DB

I am in the process of upgrading from Access to SQL Server. When I
import the tables from an access file, everything looks normal in
Enterprise Manager. All the tables are listed properly.

However, when I login through Query Analyzer, the tables names have a
prefix (my username). Why is that? How can I get rid of it?

I'd hate to have to modify all my souce code to reflect the "new" table
names.

BijoyDo you need to use the prefix in your queries? If so you could logon
as Admin and reimport the db, alternatively use an alias for each
table in you SQL code (which is what I'd do).

"bijoy" <b_naick@.yahoo.ca> wrote in message news:<1109271759.015779.5650@.l41g2000cwc.googlegroups.co m>...
> I am in the process of upgrading from Access to SQL Server. When I
> import the tables from an access file, everything looks normal in
> Enterprise Manager. All the tables are listed properly.
> However, when I login through Query Analyzer, the tables names have a
> prefix (my username). Why is that? How can I get rid of it?
> I'd hate to have to modify all my souce code to reflect the "new" table
> names.
> Bijoy|||bijoy a crit :
> I am in the process of upgrading from Access to SQL Server. When I
> import the tables from an access file, everything looks normal in
> Enterprise Manager. All the tables are listed properly.
> However, when I login through Query Analyzer, the tables names have a
> prefix (my username). Why is that? How can I get rid of it?
> I'd hate to have to modify all my souce code to reflect the "new" table
> names.
> Bijoy
under SQL server, the tables have the name of their owners as prefixes
eg : dbo.MyTable. But you still access them by using their name :
select * from MyTable.
So it shouldn't affect your code.
Polgornek

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
>