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 new Data since last IMPORT - DTS/Stored Procs?

Hello:

I am not sure how to implement the following, but I believe it entails using DTS, and hopefully it is fine that I post it here b/c ultimately I will need this backend data for my frontend .aspx pages:

On a weekly basis, I need to IMPORT some data located on a remote Oracle DB into SQL Server 2k. Since there is so much data to transfer, I would only like to transfer the data that is new to the table since the last IMPORT, i.e. a week ago and leave behin the OLD data.

Is DTS the correct way to go or do I have more control via DTS with STORED PROCEDURES? Does anyone have any good references for me?

On a similar note, once this Oracle data is IMPORTED into a certain table, I would like to EXPORT some of these NEWLY acquired rows matching certain criteria into another table for auditing purposes. For this scenario, should I implement a TRIGGER UPDATE event here on the first table?

Any advice will be greatly appreciated!I wouldn't use a trigger.

You can use DTS to call stored procedures, so you can just do it all in one DTS package.

'a week ago and leave behin the OLD data' You need to filter the data coming in. You can pass variables to DTS jobs to alter the way they execute. I think you could pass in a variable to alter the where clause within the query you specify for DTS. I haven't used variables for that reason, so I 'm not sure. Anyway, schedule that with a job and pass in the appropriate date for your filter. That would get the import into SQL. Then run whatever procedures you need to clean up the data, check for validity, etc. Then call a step to export records based on whatever criteria. I'd do it all in one DTS package.

Here's a place to start
http://www.swynk.com/friends/green/DTSHowTo3.asp|||What DTS book do you recommend I acquire to learn more about passing variables to DTS and other related topics?

I also referenced the URL you gave, but I didn't believe it was too helpful in my case.

Thanks.|||I'm a google junkie; I don't have any books on DTS.

http://www.databasejournal.com/features/mssql/article.php/1461501

This link is on the page of the first link and gets a little bit more there. You have to use the same concept as in these examples.

What I would do is this:

1) create a DTS package that uses a query to pull from the oracle database. Just a canned one that has your desired date range specified so you can see what your where clause will look like.

2) save the package to a Visual Basic File. Open that file in Notepad and find the query you specified and you can see what all the object names/details are that you'll need to reference

3) Follow the examples in these two links to create an activex task in your package that will alter the query with variable that you pass into it. Actually, you don't need to pass in variables if you don't want. You could just put the logic into the activex task that determines, based on the current date, what the date range should be. It's just VBScript, so you have all the date/time functions available.

I know this is a little sketchy, but I used these examples to figure it out the first time so they do help. Swynk has some good stuff so you could search their site or join their forum also.

Import data from another table

Hello

I have created a table in mssql.2000 which holds details of names etc. I have also included categories of interest. However the table is growing very big and unmanageable as the list of interest expand.

Instead I would like to create seperate tables for each category of interest within the same database and populate the table with names taken from the Names_Table I could then indicated yes or no if any name is interested in this category.

for example: Art_category.

However, I am unsure how I can import the column of names from the Names_Table to polulate the NameID column in the Art_category table.

I would appreciate advice and possibly a link to step by step tutorial.

Thanks.

Lynn

Not sure whehter this is what you want, but try:

INSERT INTO Art_category (NameID) SELECT names FROM Names_Table

|||

Thank you fou your reply.

Will this automatically update when a new name is added?

|||You can put the insertion in a trigger.