urso47 (12/25/2012)
Hope you had a great X'mas Eve!
Thank you and Merry Christmas!
It worked perfectly, even when I don't get whole understanding right now, but I will learn when practicing. I didn't even know about this "CROSS APPLY " and " Item = REPLACE" function. Thank you for teach me step by step like a kid, because for me most part of the code still very hard to understand.
Press the {f1} key to bring up "Books Online". That and this website should become your best friends.
Now, I would like to ask you how could I execute this SP in dozens of files within the same directory (c:temp in this case). Is there a way to call all the files within this directory regardless the file name?
Yes... that's the next "lesson". What is the name of "that" directory?
I found the SP under Programmability, but not the table for the "GetFileType01", so I think we have to do an INSERT INTO SomeTable, am I right?
Correct. We first needed to show you how to parse the data. Next, we'll show you how to insert it into a table and how to do it for many files.
And finally, for some other files some fields will be bigger than 8K, specially one field that brings http adresses longer than 500 or 600 characters and I am having troubles even using the T-SQL or a SQL Server Import Export Tools, it comes with the error message:
I need the complete file layout in order to be able to help there. A sample file would also be very helpful.
When I use a T-SQL transaction to do it, I found out the this field gets splited and part of it goes to the next fields, even using VARCHAR(MAX) or TEXT configuration in this field. I don't have the "header problem" with this files, but now I faced this problem with a very big/long information to insert in this other table field.
Again, I'd need to know the layout for such a file including what the delimiters are (comma, tab, or something else). I'd also need to know if the large column has any column delimiters embedded in the data itself and if the column is encapsulated in any type of "text qualifier".
Always thank you, Jeff!
Andre
My pleasure. If you want to save some time, always include things like a record layout, the CREATE TABLE statement for the target table, and, if you can and without violating any private information or "company proprietary informatio", at least the first 10 lines from the files you'll be working with. Obviously, a file like those containing headers will need to have more lines included to cover the header and about 10 lines of data.
--Jeff Moden
Change is inevitable... Change for the better is not.