Import multiple txt files

I have 8GB of text files which are basically log files from the past few years.
There is 24 text files per directory which are labeled for every day (so they are not all in 1 folder).
It would make reading them much easier if I could import them to SQL but I only seem to be able to import 1 at a time? (with the wizards :eek: )

Surely there is a way to mass import without all the costly applications that google searches give me?
cheers :PUse a DTS package implementing looping constructs with ActiveX scripts to loop through each folder and import the data contained in each file. There are some basic examples of how to do this on SQLDTS.com

Regards
Lempster|||If you are using SQL Server 2005 then you can use Integration service.
SSIS provides number of features to perform variety of tasks. You can use for loop container to import multiple text files at a time.|||I would use bcp and a cmd shell script for this.|||I would use bcp and a cmd shell script for this.

I would probably wrap a DTSRun in a cmd shell script (with a for/do) and pass in whatever needed input variables (date, basefilename, etc.), I just wish cmd had good "native" equivalents of grep and sed that exist in UNIX. I've used type | find, type | findstr but it gets a bit clumsy.|||Lost of ideas for me to look at here hehe, thanks a lot

import data from email attachment

This ones way beyond me, I don't even know where to start. basically we get an email every day with an attachment. The attachment is a csv file. I'd like to import that file into a table in sql. Anyone have any ideas?

Look into SSIS (SQL Server Integration Services)

That would give you everything you need to import the file.

BobP

|||Thanks... I started researching based on your post, and came accross xp_startmail and xp_readmail. These seem to be exactly what i was looking for....except;
xp_startmail gives me the following error;
Either there is no default mail client or the current mail client cannot fulfill the messaging request. Please run Microsoft Outlook and set it as the default mail client.
*** Start mail failed ***

I check outlook and it is the default. I'm using sql server 2005 outlook 2003. I have 2 mail accounts set up on the server, both accounts are bogus accounts that untill now I've used to send mail only (sp_senddbmail). I think I may have to create a real email account that has a corresponding windows account?

With that in mind I tried running xp_startmail using my windows login information and received the same error... ie
xp_startmail 'username', 'password'

|||

Another approach...pros and cons to everything....

Have outlook automatically save any file attachments to a specified folder (see http://www.vbaexpress.com/kb/getarticle.php?kb_id=522 )

Then, in a seperate process, use SSIS or some other mechanism to import the contents of the file.

In my opion, SQL is best when dealing with data rather than email.

Just my $0.02.

import data

I'm basically a database noob hoping to find a shortcut if at all possible.

I've created an ER diagram for a site I plan to build and have used that to
create the database in MS SQL 2000.

Now I want to import various data from (basically) excel spreadsheets (for
example, company names from a different sql dbase, addresses from excel and
phone numbers from a csv file) into the new database.

I have tried various methods outlined on web sites (including DTS) and have
learned I need to go back and create default values for essentially every
non-null field if I am to update literally any linked table.

My question is this: Is there any easy way/program via which I can import
the data to two different tables -- i.e. address into one table and
corresponding city into another table -- and have the relationship(s)
continue?

Or alternatively is there a "better/easy" way to do it inside sql once I
import the entire data into it's own single table?

Not to belabor the point (versus to more fully explain), but say I have two
data sets

[ Company Name | Address ]

and

[ Company Name | Phone ]

and I want to import them both into a database with separate three tables:

1. Company Name
2. Address
3. Phone

What is the least labour intensive way to effect this??

Thanks in advanceAlso, (though it is the same issue essentially) what if the first
dataset is Company Name, Address, City where each different city is
listed from 1 to ? number of times -- is there a way the dbase can
know that say New York only needs to be added once to a City table and
then the other tables just have a reference to that ID in the city
table?

"Mark S" <bob@.bob.comwrote in message
news:GVc_h.156992$aG1.70770@.pd7urf3no...

Quote:

Originally Posted by

I'm basically a database noob hoping to find a shortcut if at all
possible.
>
I've created an ER diagram for a site I plan to build and have used that
to create the database in MS SQL 2000.
>
Now I want to import various data from (basically) excel spreadsheets (for
example, company names from a different sql dbase, addresses from excel
and phone numbers from a csv file) into the new database.
>
I have tried various methods outlined on web sites (including DTS) and
have learned I need to go back and create default values for essentially
every non-null field if I am to update literally any linked table.
>
My question is this: Is there any easy way/program via which I can import
the data to two different tables -- i.e. address into one table and
corresponding city into another table -- and have the relationship(s)
continue?
>
Or alternatively is there a "better/easy" way to do it inside sql once I
import the entire data into it's own single table?
>
>
>
Not to belabor the point (versus to more fully explain), but say I have
two data sets
>
[ Company Name | Address ]
>
and
>
[ Company Name | Phone ]
>
and I want to import them both into a database with separate three tables:
>
1. Company Name
2. Address
3. Phone
>
What is the least labour intensive way to effect this??
>
Thanks in advance
>
>
>

|||Mark S wrote:

Quote:

Originally Posted by

Not to belabor the point (versus to more fully explain), but say I have two
data sets
>
[ Company Name | Address ]
>
and
>
[ Company Name | Phone ]
>
and I want to import them both into a database with separate three tables:
>
1. Company Name
2. Address
3. Phone
>
What is the least labour intensive way to effect this??

I would create a table with columns (name, address, phone), import
into it, then use queries to copy its data to the other tables.

Implicit conversions using UDTs

I've written a CLR UDT that basically wraps a COM FILETIME. While using and testing this UDT, I've come across some unusual behavior that I can't seem to track down. My question is this:

When I make the T-SQL statements

DECLARE @.ft dbo.MyFileTime
SET @.ft = 0x0123456789ABCDEF

What's happening within the SET statement?

My first surprise was that it succeeded. Then I thought, "The hex value, I understand, is implicitly a BINARY or a VARBINARY. Underneath, with those attributes, MyFileTime is also implicitly a Binary or a Varbinary. Maybe it's simply performing a direct assignment?"

MyFileTime is prefaced by the attributes:

[SqlUserDefinedTypeAttribute(Format.Native, IsByteOrdered = true, IsFixedLength = true]
[Serializable]
[StructLayout(LayoutKind.Sequential)]

One thing that definitely seems to be the case is that SET is not implicitly calling any code that I wrote. Also, the apparent binary representation of the stored value is not what I would expect it to be. If I manipulate @.ft via my properties, everything seems to work properly, but my use case doesn't allow me to do that in its context.

So, again, exactly what happens within the SET statement?

(The weird behavior has been exhibited on multiple versions of the beta, including the September CTP. The output methods I'm using to verify values (ToString(), e.g.) have been tested and verified with the precise values in question external to SqlServer. I'm reasonably confident that I'm not dealing with a simple logic error there.)

-Greg
I'm not entirely sure what problem you are facing, but I'll give it a shot. Let me know if this isn't what you were looking for.
You were right the first time when you thought that the Set command just performs a direct assignment - none of your UDT code is called for a Format.Native UDT. If your UDT was a Format.UserDefined UDT, then the BinaryReader code would have to run to instantiate it from the bytes.
I think the solution in your case is to define a UDT Validation Method. This validation method will be called whenever a UDT is defined directly by bytes (rather than going through the Parse method or something else) and you can use it to ensure that the bytes form a valid UDT.|||Hi, Steven, thanks for the reply. I apologize if my initial post was a bit obtuse. Reading back, it looks like I neglected to call out the precise issue I was having.
MyFileTime is wrapping two ints, dwHigh and dwLow, in that order. Potentially key to our discussion is that these are signed ints, not unsigned ints. When I attempt to assign a MyFileTime directly
SET @.ft = 0x0123456789ABCDEF
and then retrieve that value, the value I retrieve is:
0x8123456709ABCDEF
IE, the sign bits are toggled. That was the unexpected thing.
Right now I'm accessing the site from a different location, so I unfortunately can't provide the precise T-SQL statements that I was using to get these values.
Short version, then:
SET @.ft = 0x0123456789ABCDEF
-- Something like: PRINT cast(@.ft as binary(8))
0x8123456709ABCDEF
SET @.ft.DwHigh = 0x01234567
SET @.ft.DwLow = 0x89ABCDEF
-- I don't remember if this is req'd for type safety:
-- SET @.ft.DwLow = -1985229329 -- aka, 0x89ABCDEF
-- Something like: PRINT cast(@.ft as binary(8)), (IE, same output statement as above)
0x0123456789ABCDEF
The DwHigh and DwLow properties don't do anything exciting, it's a straight assignment to the underlying data.
Is it possible that the sign bits for those underlying ints are being toggled in the course of the direct assignment?
|||

Hi Greg, your concern makes a lot more sense now!

I understand your confusion and I believe I have an answer for you, but I'm actually seeing the reverse behavior from you. Can you check again to make sure? If your results are the same, can you attach your UDT source code?

Here is what I am seeing that hopefully explains things:

declare @.ft filetime
SET @.ft = 0x0123456789ABCDEF
select cast(@.ft as binary(8)), @.ft.ToString()
--The result matches exactly what was put in
--0x0123456789ABCDEF -2128394905,162254319
SET @.ft.dwHigh = 0x01234567
SET @.ft.dwLow = 0x89ABCDEF
select @.ft.dwHigh, cast(@.ft.dwHigh as binary(4))
--This also matches exactly what was put in
--19088743 0x01234567
select @.ft.dwLow, cast(@.ft.dwLow as binary(4))
--This also matches exactly what was put in
---1985229329 0x89ABCDEF
select cast(@.ft as binary(8)), @.ft.ToString()
--This output seems to be wrong, however the correct numbers are returned...
--0x8123456709ABCDEF 19088743,-1985229329

So, the question becomes why is setting the UDT binary to 0x0123456789ABCDEF not the same as setting the respective fields to 0x01234567 and 0x89ABCDEF?

The answer is Normalization. When SQL Server stores the UDT as a binary, it does so in such a way so that it can sort by the binary value (this is the IsByteOrdered=true attribute) rather than having to instantiate the UDT. It can't compare them in the regular Int binary format because as you can see with this data, 0x01234567 is less than 0x89ABCDEF when compared by binary values, but yet 19088743 is much greater than -1985229329.

Let me know if this compares with what you are seeing.

|||Hi, Steven!
My implementation of ToString() is a bit different, so instead of @.ft.ToString(), I tested this as follows:
DECLARE @.ft MyFiletime
SET @.ft = 0x0123456789ABCDEF
select cast(@.ft as binary(8)), @.ft.DwHigh, @.ft.DwLow
-- The binary result matches exactly what was put in
-- 0x0123456789ABCDEF
-- The decimal results appear to have their sign bits toggled
-- -2128394905,162254319
SET @.ft.DwHigh = 0x01234567
SET @.ft.DwLow = 0x89ABCDEF
select @.ft.DwHigh, cast(@.ft.DwHigh as binary(4))
-- This matches exactly what was put in
-- 19088743 0x01234567
select @.ft.DwLow, cast(@.ft.DwLow as binary(4))
-- This also matches exactly what was put in
-- -1985229329 0x89ABCDEF
select cast(@.ft as binary(8)), @.ft.DwHigh, @.ft.DwLow
--This output seems to be wrong, however the correct numbers are returned...
--0x8123456709ABCDEF 19088743,-1985229329
My results are the same as yours, so the normalization due to the IsByteOrdered property does seem to be the culprit.
I'd like to note that the first SELECT's decimal values, in both your and my samples, do have the sign bits toggled again, presumably because the normalization takes place between the MyFiletime CLR code and Sql Server's storage of the value. It seems, then, that the surprise is occurring because we're sort-of violating the MyFiletime interface by accessing the underlying bits. Of course, I don't need to say that in our particular use-case this is something of a necessity.
See, I find this behavior of the IsByteOrdered property to be unexpected. When I read the documentation for it while writing this UDT (which was, admittedly, some 6-7 months ago), I had the impression that "IsByteOrdered" would be a relatively "dumb" property. I didn't expect, and (apparently) didn't want, Sql Server and the CLR to interpret the semantics of my underlying types. Rather, I expected it to simply look at the byte representation and sort on it as it was. IE, I interpreted "IsByteOrdered" to be a "trust me, I know what I'm doing" property. With the set of attributes I used on this UDT, I didn't expect the internal byte representation of a MyFiletime to be any different between a byte array, a pair of ints, a pair of uints, a single long, and a single ulong.
Regarding solutions, then, my first thought is that converting those ints into uints ought to provide the desired behavior. It seems that the normalization is taking place because of the semantic meaning of a signed integral value.
If I correct the internal semantics of MyFiletime and make dwHigh and dwLow uints, would you also expect that to result in my desired behavior?
|||Hi Greg,
The serialization format does not actually depend on the IsByteOrdered=true attribute, I was just referencing it for the reason why SQL serializes the UDT in this way. It will be the same serialization whether IsByteOrdered=true or false.
The real reason you're not getting the results you expect is because you specified a Format.Native UDT, which is saying to SQL Server that you want SQL Server to serialize the UDT for you so that you don't have to deal with it.
If your use case depends on you dealing with the whole UDT on a byte level, then you may want to consider implementing it as a Format.UserDefined UDT, where you control the serialization completely. In this case, IsByteOrdered is a "trust me" attribute as SQL Server cannot verify that your serialization is in-fact Byte-Ordered.
Using Unsigned Integers internally in this case will also result in the serialization you expected because Unsigned Ints are naturally byte-ordered, but in general you should use a Format.UserDefined UDT if you require a specific serialization.
Did this clear things up?|||That clears it up nicely, Steven. Thanks again for the info!
As an aside, it may be useful to call out this normalization process in the BOL. I'm sure I'm not the only person who'll make this mistake.
-Greg
|||My co-worker Isaac just posted an entry to the SQL CLR blog about this issue which hopefully should help other people with similar problems.
Check out http://blogs.msdn.com/sqlclr/ for the entry and feel free to comment there with any feedback or suggestions for future posts.