Multiple inputs - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

Multiple inputs

Author

Topic

elwoos
Master Smack Fu Yak Hacker

2052 Posts

Posted - 2005-01-05 : 11:17:03

I have some (related) problems which I would prefer to solve using DTS as I simply have never got to grips with BCP even though I know I should do and it may be the best solution in this case (in which case it's time to start learning.

Problem 1
I need to import from several files (with different file names) that are all in one directory into a single table. Any suggestions on the best way to do this so that it can be automated e.g. it tries to input from all the *.txt files in C:\MyData

Problem 2
The text files above contain varying numbers of columns

so for example

File1 has

Sample Ref ... Num M1
s1 r1 ... 1 abc
s2 r1 ... 1 bcd

File 2 has

Sample Ref ... Num M1 M2 M3
s3 r2 ... 2 def ghi <NULL>
s3 r1 ... 1 <NULL> <NULL> zxc

i.e. the number of M columns varies

I'm thinking about importing them into a table of the following form (using File 2 above)

Table M

FK_1 FK_2 M
s3 r2 def
s3 r2 ghi
s3 r1 zxc

Finally
I suspect users may want to see the data from Table M above in the form shown in file 2 so any suggestions on how to do this would be appreciated (though I think this may have been covered elsewhere)

Many thanks in advance, if any of this is not clear or there appears to be a better way of doing it I would appreciate any constructive comments.

steve

And how is education supposed to make me feel smarter? Besides, every time I learn something new, it pushes some old stuff out of my brain.

nr
SQLTeam MVY

12543 Posts

Posted - 2005-01-05 : 11:50:53

1. See
http://www.mindsdoor.net/SQLTsql/ImportTextFiles.html
http://www.mindsdoor.net/SQLTsql/s_ProcessAllFilesInDir.html

That bcp'c in all files in a directory. It moves them to an archive directory so you know they have been processed.

2. Probbaly easiest to import into a single column table then parse them from there - I think one of the links above does that.

You could use the above (probably the second link) to call a dts package for each file in a directory - passing the file name as a global variable and use a dynamic properties task or activex script to set the filename before running the import.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

elwoos
Master Smack Fu Yak Hacker

2052 Posts

Posted - 2005-01-06 : 06:49:41

That's great, many thanks

steve

And how is education supposed to make me feel smarter? Besides, every time I learn something new, it pushes some old stuff out of my brain.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources