Building tables from bulk inserting files

[PROPAR]

Hello,
I need to read files into "SQL Server 2000" tables.

What I have is a set of files, with a different number of fields in each line (but the same number for a given file), and I want for each file a table with fields files into rows and columns.
And I would like not to bother to describe file structure, just let (BULK INSERT + code) create a suited table for each file read.

For example :
File1.txt :
f1;f2;f3
f4;f5;f6
f7;f8;f9
...
and

File2.txt :
fa;fb
fc;fd
...

go to tables dbo..File1 and dbo..File2
dbo..File1
Col1 Col2 Col3
----- ----- -----
f1 f2 f3
f4 f5 f6
.......

dbo..File2
Col1 Col2
----- -----
fa fb
fc fd
.......
.......

Thanks for any clue.

 

[Catadmin]

The best way to do this is via DTS or SSIS and use the delimiters as your column indicators. Or you could use SELECT..INTO.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[PROPAR]

Thank you for the the lead !
DTS is great and I managed to do it for one file at a time.
The problem is I got 312 file with different number of columns.
Is there a way to do the trick without creating 312 different DTS packages ?
The core of the DTS run is when I'm asked to specify the "Transform Data Task" and columns translations. Without it, my package don't run.
DTS tool builds code (I can see it the VB file) then related to columns.
How can I tell the stuff that I want all columns transfered the simple way ? Is there a script mode to DTS building ?

 

[Catadmin]

A DTS package can contain any number of "groups" in which it does something. You don't actually have to use a separate package for each individual item.

Unfortunately, I don't know a whole lot about DTS packages myself. Most of my experience is in SSIS. Here's a forum where the DTS gurus hang out and can help you. forum961

I do know if you want to incorporate everything in one package, it'll help if your delimiters are the same. And scripts can be done in DTS via the ActiveX tasks. They use VBA (I believe) as the language.





Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"