Hi,
I need to transfer comma separated data from about 1000 text files to a database
I’ve looked into BCP, DTS and also tried to create a .bat file
DTS works fine, but to sit down and run through this process for each and every text file is going to be a mission
BCP- I don’t have format files, only the data files, and therefore cannot create the appropriate tables and fields
Ideally I would like a .bat file that will call dtsrun.exe which will read the text files, get the number of fields and types of fields required and create a table and populate it with the data. The table name will be that of the corresponding text file

Once this is complete, it will loop to the next text file, until the loop is complete
Has anyone come across this before, and is there a simpler approach to this problem, that I missed out

Your assistance is much appreciate

This is not as easy as it sounds.

In a text file everything is a string. How do you then know what datatype
is what?

DTS has no inbuilt way to do this but you could use some VBScript and a
touch of ingenuity and accomplish it.





--
--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org


"TG" <anonymous (AT) discussions (DOT) microsoft.com> wrote

create a table and populate it with the data. The table name will be that of
the corresponding text file.

Hi guys

One thing you could do is to loop through the file names using Perl, build the string on the fly (since Perl doesn't care about string lengths, etc.), and then run each command after building. You can get it at ActiveState.com and write the script in very short order. If you aren't Perlish, let me know and I'd be happy to help

Regards
John Miceli, MCDBA

John

I do not necessarily think that the parsing is a problem here. Like you I
believe Perl would be better suited to parsing than VBScript but in this
instance the amount needed is minimal. The effort comes in the fact that
everything in a text file is a string so to make a proper CREATE TABLE
script containing more than just

attr1 varchar(255)

you will need to do a lot of testing of the values in the attribute. Look
at what DTS does when you define a new table in SQL Server from a text file
source.


--
--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org


"John Miceli" <jmiceli (AT) wrberkley (DOT) com> wrote

and then run each command after building. You can get it at ActiveState.com
and write the script in very short order. If you aren't Perlish, let me
know and I'd be happy to help.

Hi Guys,
Thanks for your time
I've been asking around, and was told that I can get some software eg. awk that will read the text file and determine the amount of commas up until the first carriage return, thus providing me with the amount of columns in the first line
With regards to the data type, I’m planning on setting all the columns to varchar 255
Hope it works!
John – I’m not to good with Perlish, didn’t use it before

Hi Guys,
Thanks for your time
I've been asking around, and was told that I can get some software eg. awk that will read the text file and determine the amount of commas up until the first carriage return, thus providing me with the amount of columns in the first line
With regards to the data type, I’m planning on setting all the columns to varchar 255
Hope it works!
John – I’m not to good with Perlish, didn’t use it before

Almost any scripting language will do that for you as well

--
--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org


"TG" <anonymous (AT) discussions (DOT) microsoft.com> wrote

first carriage return, thus providing me with the amount of columns in the
first line.