urso47 (12/23/2012)
Hi Jeff,First of all, I am impressed how helpfull you were creating this code, it is totally amazing! I am so happy to find people like you helping people like me that knows just a little bit.
I created the directory C:Temp/, copied the files into it, executed the 2 files GetFileType01 and The New DelimitedSplit8K Function but I can't even find the table to apply a select or find this SP 🙁
Maybe it is too much to ask but, could you tell me how to use the files? I read the instructions but I still not able to find out how to use it...
Thanks a lot again!
PS.:
1- I have only the weekend to study and practice but I will do my best to do it on weekdays either.
2- Once the table was created, I think would be easier just add the new files into it (use "select into" instead "create table", what do you think?).
The C:\Temp directory was just an example of a "data directory". My bad for not being a bit more clear.
1. You've already done this... From SSMS, execute the "The New DelimitedSplit8K Function.sql" file in the same database as where you intend all of this work to be done.
2. I think you've already done this, as well... From SSMS, execute the "GetFileType01.sql" file in the same database as where you intend all of this work to be done.
3. Copy the "RawData8000.fmt" file to C:\Temp on the server. We'll get back to this in a minute but let's get the test file running first. This is the file necessary to be able to do the imports and allow the rows to be numbered.
4. Copy the "urso47Test01.txt" file to C:\Temp on the server. This is just a test file with the data from your original post in it. It's just for test purposes.
5. We're all set now. Run the following command in SSMS.
EXEC dbo.GetFileType01 'C:\Temp\urso47Test01.txt';
You should see the following display which just shows that the original data was loaded and parsed correctly.
ListNameFormatVersionDate EquipmentIP UserNumberUserNameTotalB&W
User 1.2.5.0 2012-12-11 12:00:34.000L6867000214172.21.12.621600 technical76 76
User 1.2.5.0 2012-12-11 12:00:34.000L6867000214172.21.12.626611 Daise 559 559
User 1.2.5.0 2012-12-11 12:00:34.000L6867000214172.21.12.62900301 Richard 0 0
6. C:\Temp isn't a good place to keep anything permanently. What I recommend is that you copy the "RawData8000.fmt" to the same directory as where your real files are.
7. Now, find the following line in the dbo.GetFileType01 stored procedure...
FORMATFILE = ''C:\Temp\RawData8000.fmt'',
... and change the C:\Temp\ part of that to wherever you just copied the RawData8000.fmt format file to.
8. Pick one of your data files from that same directory and substitute either the full file path or full UNC between the quotes to load the file, parse it, and display it.
EXEC dbo.GetFileType01 'Put full file path or UNC here'
Once you have that working, we'll move onto the next steps.
--Jeff Moden
Change is inevitable... Change for the better is not.