Import RIS format to SQL Server?

Hello!

I have data exported from a Reference Manager 11, and need to import it
into and SQL database.

Each record has different number of fields. It is used to cite journal
articles.
(more about the format at
http://www.adeptscience.co.uk/kb/article/A626)

The format is very strange:

TY - RPRT
A1 - Esparza,J.
T1 - Report of a WHO workshop on the measurement and significance of
neutralizing antibody to HIV and SIV, London, 3-5 October 1988
Y1 - 1990
VL - 4
SP - 269
EP - 275
RP - Not In File
CY - San Francisco CA
PB - UC Berkeley
KW - HIV
KW - SIV
KW - AIDS
T3 - World Health Organisation Global Programme on AIDS
ER -

TY - CHAP
A1 - Franks,L.M.
T1 - Preface by an AIDS Victim
Y1 - 1991
VL - 10
SP - vii
EP - viii
RP - Not In File
T2 - Cancer, HIV and AIDS.
CY - Berkeley CA
PB - Berkeley Press
KW - HIV
KW - AIDS
M1 - 1
M2 - 1
SN - 0-679-40110-5
ER -

TY - CASE
A1 - Cary,A.
A1 - Friedenrich,W.
T1 - Redman v. State of California
Y1 - 1988/10/7
VL - 201
IS - 32
SP - 220
EP - 240
RP - Not In File
CY - ATLA Law Reporter
PB - San Diego County 45th Judicial District, California
KW - AIDS
KW - litigation
KW - AIDS litigation
KW - rape
U1 - ISSN 0456-8125
N1 - Raped inmate can press case against officials for contracting
AIDS
ER -

It looks like some of the columns are separted by CR but part of the
same colum. For instance 'KW' is seen multiple times per record but
should be one field called 'KW'.

Any idea how I would import this to SQL Server with the DTS?"Tmuld" <tmuldoon@.spliced.com> wrote in message
news:1113921439.658252.91360@.g14g2000cwa.googlegro ups.com...
> Hello!
> I have data exported from a Reference Manager 11, and need to import it
> into and SQL database.
> Each record has different number of fields. It is used to cite journal
> articles.
> (more about the format at
> http://www.adeptscience.co.uk/kb/article/A626)
> The format is very strange:

<snip
> It looks like some of the columns are separted by CR but part of the
> same colum. For instance 'KW' is seen multiple times per record but
> should be one field called 'KW'.
> Any idea how I would import this to SQL Server with the DTS?

The format looks rather awkward to handle in DTS transformations, so you'll
probably need some other solution. One would be to load the data into a
staging table, then use stored procedures to clean it up and INSERT it into
the destination tables. A better option is probably to parse the data
outside the database, using C#/Perl or whatever your preferred language is,
then load it. You might be able to transform it into XML, for example, then
use OPENXML or SQLXML Bulk Load to import it:

http://www.sqlxml.org/faqs.aspx?faq=13

Whatever solution you decide on, you could still use a DTS package to manage
all the steps, whether they are stored procedures or external programs.

Simon|||I think that your best bet would be to loop through the file in a VB
application which handles the logic. Usually, files that hold data in a
"record" format have the columns going across the file and rows going
down. They also usually have a set number of columns. While this format
isn't unheard of (I've worked with similar formats in the past) most of
the standard tools for importing files (BCP, DTS, BULK INSERT) are not
designed to deal with it. BCP might be able to get around it if you had
a fixed number of columns, but the variable number of columns would
present a problem there.

If you have no skill in a programming language like VB and you don't
have access to a resource who has those skills then you could BCP the
file into a staging table with an IDENTITY column. Make sure that the
ordering of the records in the file is preserved in the rows of the
table. From there you can loop through the rows sequentially with a
cursor. This is one of those VERY rare instances where I think a cursor
is called for. As I said though, I think you're better off using VB or
something similar if you can.

By looping through the records you can create a new row in your base
table every time that you hit a new record tag in the file then you can
create rows in children tables for each repeatable column type.

Good luck,
-Tom.

Import of XML data.

Dear all,
I have been asked to look into the following.
Data is delivered in XML format.
(Assume wel formed XML and the stylesheet is present).
This data has to be imported in the database.
What are the possibilities in 2005?
What are the possibilities in 2000?
Especially are there enough possibilities to import the XML data 2000. Or do
we need a special workaround voor 2000 ?
My experience with XML is very limited. Once the data is present in the
database I'll will be able to transform the data in such a way that it fits
in the target tables.
Thanks for your time and attention,
Ben Brugman"ben brugman" <ben@.niethier.nl> wrote in message
news:u00%23337oIHA.1952@.TK2MSFTNGP05.phx.gbl...
> Dear all,
> I have been asked to look into the following.
> Data is delivered in XML format.
> (Assume wel formed XML and the stylesheet is present).
> This data has to be imported in the database.
> What are the possibilities in 2005?
> What are the possibilities in 2000?
> Especially are there enough possibilities to import the XML data 2000. Or
> do we need a special workaround voor 2000 ?
> My experience with XML is very limited. Once the data is present in the
> database I'll will be able to transform the data in such a way that it
> fits in the target tables.
> Thanks for your time and attention,
> Ben Brugman
>
2005 has more XML related features, but you can import the data into 2000 as
well.
Take a look at the OPENXML command in the Books Online. You can use those
queries to pull the data from the XML document into whatever format you
wish.
Rick Sawtell|||Thank you, I hadn't thought about this possibility.
Does this say that I can not import directly from XML into 2000?
Because then I do not have to look further into that ally.
Thanks for your time and suggestion,
Ben Brugman
"Rick Sawtell" <r_sawtell@.nospam.hotmail.com> schreef in bericht
news:Op%232VF9oIHA.4912@.TK2MSFTNGP03.phx.gbl...
> "ben brugman" <ben@.niethier.nl> wrote in message
> news:u00%23337oIHA.1952@.TK2MSFTNGP05.phx.gbl...
>> Dear all,
>> I have been asked to look into the following.
>> Data is delivered in XML format.
>> (Assume wel formed XML and the stylesheet is present).
>> This data has to be imported in the database.
>> What are the possibilities in 2005?
>> What are the possibilities in 2000?
>> Especially are there enough possibilities to import the XML data 2000. Or
>> do we need a special workaround voor 2000 ?
>> My experience with XML is very limited. Once the data is present in the
>> database I'll will be able to transform the data in such a way that it
>> fits in the target tables.
>> Thanks for your time and attention,
>> Ben Brugman
> 2005 has more XML related features, but you can import the data into 2000
> as well.
> Take a look at the OPENXML command in the Books Online. You can use those
> queries to pull the data from the XML document into whatever format you
> wish.
>
> Rick Sawtell
>|||"ben brugman" <ben@.niethier.nl> wrote in message
news:7c959$480ce938$53557893$11072@.cache90.multikabel.net...
> Thank you, I hadn't thought about this possibility.
> Does this say that I can not import directly from XML into 2000?
> Because then I do not have to look further into that ally.
> Thanks for your time and suggestion,
> Ben Brugman
>
That depends on your needs. You could put an XML column into a text/ntext
or sufficiently large varchar/nvarchar field in SQL Server 2000. But then
you would be treating the XML as a single column in a table. This can also
be done in 2005, but as an XML data type rather than string datatypes listed
above. 2005 also has other advantages like binding an XSD to the XML data
type.
In order to map the columns in a database table(s) to specific nodes in the
xml object, you would need to use the OPENXML with some XPATH queries.
It is relatively straightforward.
As far as outputting XML, there are several avenues for you to pursue. This
includes the SELECT ... FOR XML scenario as well as some others.
HTH
Rick Sawtell

Import Large Record Fixed Length Text File

I am trying to import a large record (about 13k per record) fixed length file into SQL Server. I have built a format file and would like to now import the data. My understanding is that SQL Server limits record sizes to 1024, so I should only select the columns I really need in the format file. Can I perfrom the import without having to create the table ahead of time. I may change the columns that I include an I don't want to have to keep changing the table as well as the format file.

Will the bcp or bulk insert utlility create a table based on the format file or do I just have to tuff it out and make changes in both the format file and the table and be sure that they match?SQL server only supports bcp / bulk inserts into existing tables ...|||Thanks for the info|||What version of sql are you using|||The only reason I ask is that sql 2k supports 8K records.

BTW. You could always use DTS. Which will allow you to choose which columns you import and create a table ( please if you use this don't accept the default of varchar(255) Ugh!! )|||Please post the structure of the data you are importing - data type/size ... And maybe even attach a sample of the data. You have various options like text/ntext/image when importing into sql server to help with 8k boundary - but this is highly dependent on your data structure. You can play with dts and go to transformations to change the data types.sql

Import HTML and Tab delimited into SQL

Hi,
I need to import files from either HTML or Tab delimited format into SQL
Server. I imagine this would involve DTS and/or a DSN, but I'm not sure of
the details. I'm able to standardize the filename and directory using code,
so that's not a problem. Any ideas?
HTML? I don't think so. Tab delimited, you bet.
Yes, you would set up a DTS packages using the
Import / Export wizard.
If you want to script this and run it, check out this
VBScript code sample:
http://www.eggheadcafe.com/articles/20030923.asp
2005 Microsoft MVP C#
Robbe Morris
http://www.masterado.net
http://www.mastervb.net
"Eric" <Eric@.discussions.microsoft.com> wrote in message
news:C603F79A-3654-4785-B144-84909C69473E@.microsoft.com...
> Hi,
> I need to import files from either HTML or Tab delimited format into SQL
> Server. I imagine this would involve DTS and/or a DSN, but I'm not sure of
> the details. I'm able to standardize the filename and directory using
> code,
> so that's not a problem. Any ideas?

import from Access to SQL, not knowing the table format

I need to import few tables from MS Access to MS SQL but the table structure in Access is always different, as I would like the destination table in SQL to be.

Therefore I would like that a table would be created in SQL at runtime, according to the structure the Access table accessed has.

You can't do this using a data-flow because for these you need to know the metadata of the source and destination at design-time and according to your post, you don't know that!

I don't know much about Access. Is there a way of interrogating the metadata at design-time? If so you could get that metadata (hopefully using an Execute SQL Task) and use that to build your data-flow programatically at runtime. That's a difficult thing to do though. If you really want to go down this route then there's some stuff in BOL to help you.

-Jamie

|||OK, got the point.
Just to be clear, I wuold like to do something like

select * into <table destination> from <table source>

But I cannot because the source is Access, the destination is SQL Server 64 bit and there is no MS Jet driver for 64 bit.

Anybody has a good idea?
|||

The only way to do this with a SELECT...INTO... is to set the Access mdb up as a linked server.

-Jamie

|||

srem wrote:

But I cannot because the source is Access, the destination is SQL Server 64 bit and there is no MS Jet driver for 64 bit.

You can still run this on a 64 bit machine, just call it through the 32 bit dtexec, see the Program Files x86 folder.

I think your bigger issue is the lack of metadata up front, as Jamie points out.

|||i'm not sure about this, but i think you can use the script task to determine the access table schema. then, you could use this schema information to dynamically create the sql server table.

import from Access to SQL, not knowing the table format

I need to import few tables from MS Access to MS SQL but the table structure in Access is always different, as I would like the destination table in SQL to be.

Therefore I would like that a table would be created in SQL at runtime, according to the structure the Access table accessed has.

You can't do this using a data-flow because for these you need to know the metadata of the source and destination at design-time and according to your post, you don't know that!

I don't know much about Access. Is there a way of interrogating the metadata at design-time? If so you could get that metadata (hopefully using an Execute SQL Task) and use that to build your data-flow programatically at runtime. That's a difficult thing to do though. If you really want to go down this route then there's some stuff in BOL to help you.

-Jamie

|||OK, got the point.
Just to be clear, I wuold like to do something like

select * into <table destination> from <table source>

But I cannot because the source is Access, the destination is SQL Server 64 bit and there is no MS Jet driver for 64 bit.

Anybody has a good idea?|||

The only way to do this with a SELECT...INTO... is to set the Access mdb up as a linked server.

-Jamie

|||

srem wrote:

But I cannot because the source is Access, the destination is SQL Server 64 bit and there is no MS Jet driver for 64 bit.

You can still run this on a 64 bit machine, just call it through the 32 bit dtexec, see the Program Files x86 folder.

I think your bigger issue is the lack of metadata up front, as Jamie points out.

|||i'm not sure about this, but i think you can use the script task to determine the access table schema. then, you could use this schema information to dynamically create the sql server table.

import file question

suppose i would receive a file in csv format daily like this:

cvg_20070516.csv

cvg_20070517.csv

cvg_20070518.csv

.

.

.

so how can i import the data into the database as i can't specifcy a file to be the source file? (which means, for example, after i hv got a file cvg_20070518.csv, how can i set up an automation that to save another copy call 'cvg.csv' in another folder and so i can use this file as a source to import into database?)

thanks a lot, i appreciate your help!

Here you go...

Code Snippet

Create Table #Files

(

CSVFilevarchar(100)

);

Declare @.File as Varchar(100);

Declare @.cmd as varchar(1000);

Insert Into #Files

Exec master..xp_cmdshell 'dir /B C:\data\csv\*.csv'

Select @.File = 'C:\data\csv\' + Max(CSVFile) from #Files

Set @.cmd = 'Copy /Y ' + @.File + ' C:\data\csv\importable\cvg.csv'

Exec master..xp_cmdshell @.cmd

Drop table #Files;

|||

it returns the result like this:

Output

The system cannoot find the filespecified

null

can someone lese help...

|||

You have to give the proper path.

The example shows the sample path...

You can make select query against the temp table to verify all the files are listed ...

Import e-mails into SQL Server database

Hi,
I need to import/extract information from E-mails delivered to my SQL Server 2000 mail account.
The format of the e-mail is as below
ALERT for FPC \ fujipoc_FPC_01 \ R3Syslog \ CCMS at 20040916 074903 ( Time in UTC ) Yellow CCMS alert for monitored object CCMS Alert Text:The active profile was modified System:FPC
Segment:SAP_CCMS_fujipoc_FPC_01
MTE:FPC\fujipoc_FPC_01\R3Syslog\CCMS
Client:000
User:SAPSYS
The information I want to capture is underlined, the text file is not standard in width as there are many different monitors.
Is there an easy way to pull this information into the SQL table, I know that I can use format files with BCP but what about DTS packages?
TIA
Chris aka BoobBoo
Hi
You may want to look at xp_findnextmsg and xp_readmail to read the mailbox
directly. You can then use string functions to parse the message.
John
"Chris Kernaghan" wrote:

> Hi,
> I need to import/extract information from E-mails delivered to my SQL Server 2000 mail account.
> The format of the e-mail is as below
> ALERT for FPC \ fujipoc_FPC_01 \ R3Syslog \ CCMS at 20040916 074903 ( Time in UTC ) Yellow CCMS alert for monitored object CCMS Alert Text:The active profile was modified System:FPC
> Segment:SAP_CCMS_fujipoc_FPC_01
> MTE:FPC\fujipoc_FPC_01\R3Syslog\CCMS
> Client:000
> User:SAPSYS
>
> The information I want to capture is underlined, the text file is not standard in width as there are many different monitors.
> Is there an easy way to pull this information into the SQL table, I know that I can use format files with BCP but what about DTS packages?
>
> TIA
> Chris aka BoobBoo

Import e-mails into SQL Server database

Hi,
I need to import/extract information from E-mails delivered to my SQL Server
2000 mail account.
The format of the e-mail is as below
ALERT for FPC \ fujipoc_FPC_01 \ R3Syslog \ CCMS at 20040916 074903 ( Time i
n UTC ) Yellow CCMS alert for monitored object CCMS Alert Text:The active pr
ofile was modified System:FPC
Segment:SAP_CCMS_fujipoc_FPC_01
MTE:FPC\fujipoc_FPC_01\R3Syslog\CCMS
Client:000
User:SAPSYS
The information I want to capture is underlined, the text file is not standa
rd in width as there are many different monitors.
Is there an easy way to pull this information into the SQL table, I know tha
t I can use format files with BCP but what about DTS packages?
TIA
Chris aka BoobBooHi
You may want to look at xp_findnextmsg and xp_readmail to read the mailbox
directly. You can then use string functions to parse the message.
John
"Chris Kernaghan" wrote:

> Hi,
> I need to import/extract information from E-mails delivered to my SQL Serv
er 2000 mail account.
> The format of the e-mail is as below
> ALERT for FPC \ fujipoc_FPC_01 \ R3Syslog \ CCMS at 20040916 074903 ( Time
in UTC ) Yellow CCMS alert for monitored object CCMS Alert Text:The active
profile was modified System:FPC
> Segment:SAP_CCMS_fujipoc_FPC_01
> MTE:FPC\fujipoc_FPC_01\R3Syslog\CCMS
> Client:000
> User:SAPSYS
>
> The information I want to capture is underlined, the text file is not stan
dard in width as there are many different monitors.
> Is there an easy way to pull this information into the SQL table, I know t
hat I can use format files with BCP but what about DTS packages?
>
> TIA
> Chris aka BoobBoo

Import e-mails into SQL Server database

This is a multi-part message in MIME format.
--=_NextPart_000_000C_01C513AD.9261A570
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Hi,
I need to import/extract information from E-mails delivered to my SQL = Server 2000 mail account.
The format of the e-mail is as below
ALERT for FPC \ fujipoc_FPC_01 \ R3Syslog \ CCMS at 20040916 074903 ( = Time in UTC ) Yellow CCMS alert for monitored object CCMS Alert Text:The = active profile was modified System:FPC
Segment:SAP_CCMS_fujipoc_FPC_01
MTE:FPC\fujipoc_FPC_01\R3Syslog\CCMS
Client:000
User:SAPSYS
The information I want to capture is underlined, the text file is not = standard in width as there are many different monitors.
Is there an easy way to pull this information into the SQL table, I know = that I can use format files with BCP but what about DTS packages?
TIA
Chris aka BoobBoo
--=_NextPart_000_000C_01C513AD.9261A570
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Hi,I need to import/extract information from E-mails delivered to my SQL Server 2000 = mail account.The format of the e-mail is as belowALERT for = FPC \ fujipoc_FPC_01 \ R3Syslog \ CCMS at 20040916 074903 ( Time = in UTC ) Yellow CCMS alert for monitored object CCMS Alert Text:The = active profile was modified System:FPCSegment:SAP_CCMS_fujipoc_FPC_01MTE:FPC\fujipoc_F= PC_01\R3Syslog\CCMSClient:000User:SAPSYSThe information I want to capture is underlined, the text file is not = standard in width as there are many different monitors.Is there an easy way = to pull this information into the SQL table, I know that I can use format files = with BCP but what about DTS packages?TIA Chris aka BoobBoo
--=_NextPart_000_000C_01C513AD.9261A570--Hi
You may want to look at xp_findnextmsg and xp_readmail to read the mailbox
directly. You can then use string functions to parse the message.
John
"Chris Kernaghan" wrote:
> Hi,
> I need to import/extract information from E-mails delivered to my SQL Server 2000 mail account.
> The format of the e-mail is as below
> ALERT for FPC \ fujipoc_FPC_01 \ R3Syslog \ CCMS at 20040916 074903 ( Time in UTC ) Yellow CCMS alert for monitored object CCMS Alert Text:The active profile was modified System:FPC
> Segment:SAP_CCMS_fujipoc_FPC_01
> MTE:FPC\fujipoc_FPC_01\R3Syslog\CCMS
> Client:000
> User:SAPSYS
>
> The information I want to capture is underlined, the text file is not standard in width as there are many different monitors.
> Is there an easy way to pull this information into the SQL table, I know that I can use format files with BCP but what about DTS packages?
>
> TIA
> Chris aka BoobBoo

Import DBF

Hi There! I have a problem with a DBF file. The problem is that somebody gaves me a data base in DBF format and he uses in SQL Server 2000 and EMS SQL Manager. Well, I have to instal the MDE and SQL Enterprise Manager and when I use the DTS tool to import the data I've got this error:

'Error not Especified'

and I don't know what's happen and how to solve it. Please! Any ideas!

("Sorry about my english")i would like at the sql server error log but I am guessing you do not have the right database driver for the type of database you are trying to import.|||hi, is a dbase files? try test your odbc with the Excel query, maybe, your problem is the connection. test this and tell me.

import data question (reask)

suppose i would receive a file in csv format daily like this:

cvg_20070516.csv

cvg_20070517.csv

cvg_20070518.csv

.

.

.

so how can i import the data into the database as i can't specifcy a file to be the source file? (which means, for example, after i hv got a file cvg_20070518.csv, how can i set up an automation that to save another copy call 'cvg.csv' in another folder and so i can use this file as a source to import into database?)

some one replyed me with the following solution

Create Table #Files

(

CSVFile varchar(100)

);

Declare @.File as Varchar(100);

Declare @.cmd as varchar(1000);

Insert Into #Files

Exec master..xp_cmdshell 'dir /B C:\data\csv\*.csv'

Select @.File = 'C:\data\csv\' + Max(CSVFile) from #Files

Set @.cmd = 'Copy /Y ' + @.File + ' C:\data\csv\importable\cvg.csv'

Exec master..xp_cmdshell @.cmd

Drop table #Files;

but it doesn't work. can someone elaborate more on it and tell me how's it gonna work?

Having seen the error message in your other message, it sounds like the account under which your SQL Server instance's service is running doesn't have the appropriate permissions on the folder and/or CSV files.

Chris

import data from Text Files into SQL Server...?

Hi,
i wanna develop an web-database application with ASP.NET,C#, SQL server 2000.
i already have some data whichs been in text format(text file) and now, i want to import the same into my database.
the problem is, the text file has got many line breaks and also its not well formated to import it using DTS.
Can any one help me out in importing the same.
thanks in advance

Hello Kiran,
There is no way to transform the text file if it is not formatted and inconsistent.
Only thing you can do is to make it consistent at application end and then passes it to DTS package.
|||Try this link see if you can use BCP (bulk copy) with DTS. Hope this helps.
http://www.sqldts.com/default.aspx?237|||Hi,
Thanks for the response.
Even i think i need to format the text in the front end, before actually using the DTS.And thus formatted one can be easily imported.Well i gonna try that, but the probs is , even if i want to format it in the front end, the same cant be generalized to all, since each of them gonna have there own format.
any more ideas are always welcome

Import Data from Excel with Correct format

Hi,
I have one column of data which is 15.678 but in the excel, i format it to 15.68 ( two decimal place, so in excel i should see 15.68), when i am trying to import the data from excel to sql server by using odbc connection, it still getting 15.678, how can i get the data from 15.678 to 15.68 ( what i see is wat i get).
Thanks for help.

I am stuck at this problem too.

Anyone got a solution to this? Thank you!

|||I have had the same problem before. Some how Excel is funky with that sistuation. The solution I used is I let the SQL Database get the 5 decimal digit number. But if I ever had to use that number in calculation or displaying, I just format the decimal using coding.|||

Try the code in the thread below and use Decimal instead of Money as your data type so you can set precision and scale. Hope this helps.

http://forums.asp.net/1019602/ShowPost.aspx

|||You could also try saving the Excel file as pure CSV (Comma Separated Values) and then import that. As CSV is plain text you can see exactly what you will be getting.|||

Hi,

My main reason to use excel is because they can use excel to do some calculation and then just want to import the final result which the use can determine themselves at the excel and then juz import to the sql server database. So, user just want to export wat they see only and don't want the additional decimal points, have any better idea?

|||You could try putting a trigger on your SQL server table that rounds down the value of the column on insert.|||Sorry, if i set the trigger in the sql server, i need to default a rounding, but now the problem is i am not sure whether the user need to round to 2 decimal points or 3 decimals point or etc...i just want to import as what user already formatted in the excel..that's all...any other idea?

import csv files into MS SQL Server 2K

Hi There,

I have a requirement to import a large number of csv files to one table. The files are in the format NAME.date.csv. What is the best way to do this?

Cheers

Pete

There are several options, bcp, dts or bulk insert.

Depending on how much logic should be applied in the import you may want to use dts or bulk insert.

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

Import ASCII Data

I have data that comes from a legacy system. I can obtain the data in an
ASCII format. Currently I have created scripts in ACCESS to import the data
into tables.

What I would like to do is create an automated import function in SQL.

I am new to SQL, can anyone point me in the direction I should look to find
out how I could perform this task?

Using SQL 2005.

Thanks
Matt

--
Matt Campbell
mattc (at) saunatec [dot] com

Message posted via http://www.sqlmonster.commattc66 via SQLMonster.com (u16013@.uwe) writes:

Quote:

Originally Posted by

I have data that comes from a legacy system. I can obtain the data in an
ASCII format. Currently I have created scripts in ACCESS to import the
data into tables.
>
What I would like to do is create an automated import function in SQL.
>
I am new to SQL, can anyone point me in the direction I should look to
find out how I could perform this task?
>
Using SQL 2005.

There are a couple of alternatives. There is BCP (command-line tool)
and BULK INSERT (T-SQL statement) which work very similarily. Their
good as long as the files have one entry for each file in each record,
and there are no headers.

You can use the Import Wizard in SQL Server Management Studio. As with
all graphical tools, it's good for a one-off, but it's really a good
place if you need to do this on a regular basis. The Import Wizard requires
that SSIS (see below) is installed.

And then there is SQL Server Integration Service (SSIS), the member of the
SQL Server family that is all about importing and exporting data and
transforming it on the way. I have not used SSIS or its predecessor
myself, so I don't really know what it's so fantastic. (Being an old-
timer, I get by very well with BCP and BULK INSERT.)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||To add to Erland's response, you can use a SQL Agent job to schedule the
import process. In the case of a package created with SSMS or the BI dev
studio, there is a specialized SQL Server Integration Services step type
that allows you to specify the desired package and run time settings via a
GUI.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"mattc66 via SQLMonster.com" <u16013@.uwewrote in message
news:65826d5d02090@.uwe...

Quote:

Originally Posted by

>I have data that comes from a legacy system. I can obtain the data in an
ASCII format. Currently I have created scripts in ACCESS to import the
data
into tables.
>
What I would like to do is create an automated import function in SQL.
>
I am new to SQL, can anyone point me in the direction I should look to
find
out how I could perform this task?
>
Using SQL 2005.
>
Thanks
Matt
>
--
Matt Campbell
mattc (at) saunatec [dot] com
>
Message posted via http://www.sqlmonster.com
>
>

Implimenting additional export types

Is it possible to add additional export types to the list, link to sample
code would be nice.
Select a format (Export)
XML file with report data
CSV (somma delimited)
TIFF file
Acrobat (PDF) file
Web archive
Excel
-- MY NEW TYPE --
Regards,
JohnGuess not :(
"John J. Hughes II" <no@.invalid.com> wrote in message
news:eOwBwc1pGHA.4760@.TK2MSFTNGP05.phx.gbl...
> Is it possible to add additional export types to the list, link to sample
> code would be nice.
> Select a format (Export)
> XML file with report data
> CSV (somma delimited)
> TIFF file
> Acrobat (PDF) file
> Web archive
> Excel
> -- MY NEW TYPE --
> Regards,
> John
>

Implicit Keep Together For Export?

I have a report that has 3 nested lists. The report looks fine when I view it in the browser but when I print it or export it to another format it appears that keep together is on for each list because some pages are only partially filled. I do not have keep together turned on for any report control, and I don't have page break turned on for anything. This problem results in the report being many more pages than it needs to be. Can anyone help?Yes, list dataregions have an implicit KeepTogether. You might give tables a try instead. More control over KeepTogether is on the list for a future version.|||

Now nearly 5 months later is there _ANY_ hope to get this KeepTogether working properly? Searching the web I found some other people having that problem. Its working correct if you have few data and its working correct if you have much data, but if you have a certain amount in between this keeptogether bug appears.

|||now over a year later and a new version keeptogether is not avaliable on tables.. man this sucks!

Implicit Keep Together For Export?

I have a report that has 3 nested lists. The report looks fine when I view it in the browser but when I print it or export it to another format it appears that keep together is on for each list because some pages are only partially filled. I do not have keep together turned on for any report control, and I don't have page break turned on for anything. This problem results in the report being many more pages than it needs to be. Can anyone help?Yes, list dataregions have an implicit KeepTogether. You might give tables a try instead. More control over KeepTogether is on the list for a future version.|||

Now nearly 5 months later is there _ANY_ hope to get this KeepTogether working properly? Searching the web I found some other people having that problem. Its working correct if you have few data and its working correct if you have much data, but if you have a certain amount in between this keeptogether bug appears.

|||now over a year later and a new version keeptogether is not avaliable on tables.. man this sucks!

Implicit Keep Together For Export?

I have a report that has 3 nested lists. The report looks fine when I view it in the browser but when I print it or export it to another format it appears that keep together is on for each list because some pages are only partially filled. I do not have keep together turned on for any report control, and I don't have page break turned on for anything. This problem results in the report being many more pages than it needs to be. Can anyone help?Yes, list dataregions have an implicit KeepTogether. You might give tables a try instead. More control over KeepTogether is on the list for a future version.|||

Now nearly 5 months later is there _ANY_ hope to get this KeepTogether working properly? Searching the web I found some other people having that problem. Its working correct if you have few data and its working correct if you have much data, but if you have a certain amount in between this keeptogether bug appears.

|||now over a year later and a new version keeptogether is not avaliable on tables.. man this sucks!