Parse CSV Stored Proc? 1

[TamedTech]

Hello Guys,

I'm currently constructing a ColdFusion web application that recieves CSV data in a string passed in through a webservice every few minutes and I've been looking for the most efficient method to get it into the database.

Now DTS could be an option I guess but to be honest I'd rather avoid it if possible by passing the CSV string into a stored proc, does that sound like somthing i could achieve? The file csv content looks somthing like this:

GF:34:00:3F:FD, 6, 1, 2007-01-01 13:00:00, 1
GF:34:00:3F:FD, 6, 2, 2007-01-01 13:01:00, 1
GF:34:00:3F:FD, 6, 1, 2007-01-01 13:04:00, 1
GF:34:00:3F:FD, 6, 4, 2007-01-01 13:08:00, 1
GF:34:00:3F:FD, 6, 1, 2007-01-01 13:10:00, 1
GF:34:00:3F:FD, 6, 1, 2007-01-01 13:20:00, 1
GF:34:00:3F:FD, 6, 1, 2007-01-01 13:22:00, 1
GF:34:00:3F:FD, 6, 5, 2007-01-01 13:26:00. 1
GF:34:00:3F:FD, 6, 1, 2007-01-01 13:29:00, 1
GF:34:00:3F:FD, 6, 5, 2007-01-01 13:29:00, 1
GF:34:00:3F:FD, 6, 1, 2007-01-01 13:30:00, 1
GF:34:00:3F:FD, 6, 2, 2007-01-01 13:32:00, 1

Ideally this data would be placed into two tables, the first table would store all the MAC address’s that are listed in the first column of the CSV, the other elements of the log would then be stored in a second table which would reference the MAC address using a foreign key.

I’d be interested to hear your thoughts, these would most likely come in through the web service a few times an hour and be perhaps a hundred records long at tops.

Thanks,

Rob

 

[mflancour]

Is this a "file" or just a string of data? Two things are coming to mind depending on what your scenario is:

wget
or
bulk insert

 

[TamedTech]

mflancour,

Thanks for getting back to me on this, sorry I was slow to reply, I've been away on business getting some other things sorted.

This is just a string comming into the webservice, I -COULD- have my application write it to a file if thats going to work better, whatever is more efficient.

wget? Isnt that a linux app for hitting websites?

Thanks again,

Rob

 

[mrdenny]

If I was building this app I would like to see the coldfusion application write the data to a text file on disk that the SQL Server could access. Then use the BULK INSERT command from within a stored procedure to load the data into a temp table, then process it into the lookup table as needed (the one with the MAC Addresses) and the data table (the one with the rest of the data).

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[TamedTech]

Ok that sounds like a fair enough process Mr Denny, I'm quite happy to work with that arrangement.

I've not worked with bulk inserts or stored proceedures before, are you able to give me a hand in writing those? I wont have any issues firing them from coldfusion as I've done that in the past, its just writing the stored procs, temp tables and all that which I'm a little lost on as its new teritory for me.

Thanks again for the suggestions.

Rob

 

[mrdenny]

First you need to create a staging table in the same format as the text file you will be importing.

Then create a procedure which looks something like this.

create procedure usp_Something AS
BULK INSERT YourStagingTable
FROM 'c:\YourInputFile.txt'

INSERT INTO TableWithMacAddresses
SELECT MacAddress
FROM YourStagingTable
WHERE MaxAddress NOT IN (SELECT MacAddress FROM TableWithMacAddresses)

INSERT INTO TableWithLoggingData
SELECT {Your Columns}
FROM YourStagingTable

GO

Then execute the table after saving the file.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[TamedTech]

Thanks for that MrDeny, Excelent stuff and not too complicated which is good to see.

One quick question with regards to this, With that second insert statement, that creates all the logs, how does it know to referance the mac table with a foreign key for the mac?

The idea is that I'll have those two tables, with macs listed like.

MacId MAC
1 76:3dD:WE:00
2 12:RE:4R:44:OP

And then the second table like.

MacId Channel Blah Blah Blah
1 6 1 2007-01-01 13:00:00 1
2 6 1 2007-01-01 13:01:00 1
2 6 1 2007-01-01 13:05:00 1
1 6 1 2007-01-01 13:02:00 1

Does your code example work on that principle? Or will it require some changes to those queries?

Thanks again mate,

Rob

 

[mrdenny]

Sorry, I forgot about that part. The code

INSERT INTO TableWithLoggingData
SELECT {Your Columns}
FROM YourStagingTable

Will need to be altered to account for the join to the MacAddress table to get the MacID.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[TamedTech]

Ok thanks for that MrDenny,

A quick question about this staging table, should I be createing a perminant table for this? or some form of temporary table that is destroyed once the insert is done?

Also, sorry to be a bit of a pain but I've not done a JOINED insert before for creating that foreign key referance, any chance you can give us a hand with that?

Thanks bud, *Slide MrDenny a beer.

Rob

 

[mrdenny]

I would use a physical table for this. Just truncate it at the begining of the stored procedure.

INSERT INTO TableWithLoggingData
({Your Columns})
SELECT MacAddressTable.MacID, YourStagingTable.{Your Columns}
FROM YourStagingTable
JOIN MacAddressTable ON YourStagingTable.MacAddress.MacAddressTable.MacAddress

Does that make sense (best I can do without the actual table and column names.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[TamedTech]

Thanks Denny,

I've been working my way through this today and keep comming accross some issues, but I've got the BulkInsert now working a charm, but the second query I'm struggling with, so when running this.

INSERT INTO TableWithMacAddresses
SELECT MacAddress
FROM YourStagingTable
WHERE MaxAddress NOT IN (SELECT MacAddress FROM TableWithMacAddresses)

I get an error message that looks like this:

Msg 208, Level 16, State 1, Line 1
Invalid object name 'DeviceStaging'.

Which doesnt make much sense as i know for sure that the table 'DeviceStaging' does exist as I have the bulk insert parseing the text file into it.

Any ideas whats causing this?

As for my table layout, this is what i have.

MacAddress
----------
MacAddress_ID
MacAddress
Port

DeviceStaging
-------------
MacAddress
Port
DateTime
LogClass_ID

MacLog
------
MacLog_ID
MacAddress_ID
DateTime
LogClass_ID

The Mac and its Port need to be taken from the log file and put into the 'MacAddress' table and then all the coresponding mac logs into the Log table with the foreign key.

I think you're pretty close with your solution above but its not -quite- right. This is all a little above my SQL knowledge as I usualy only do simple CRUD statements.

Thanks for any further help.

Rob

 

[mrdenny]

If the table exists and you are still getting the error are you sure that you are running the command in the correct database and that everything is spelled correctly?

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[TamedTech]

Ok Bizzare, I've just logged in from home and given it another shot and it appears to work, well, in as much that it takes all the entries from the staging table and them places them in the MacAddress table.

I was looking to move the entries so there was only one entry of each mac address in the 'MacAddress' table, so they are all unique, does that make sense?

I'm sure this can be done using some form of UNIQUE statement, obviously you have the subquery there to check if the mac is already in the MacAddress table, but that doesnt take into account that the staging table may have multiple entries of the same mac address.

For instance, when i just ran the code, it moved 124 rows, but there was only actualy 2 unique mac address's.

Any ideas on how to modify that query?

Thanks again for all yor help mate, I'd love it if you've got any good SQL books that i could bury myself into at some point. Ones that cover design as much as the language itself would be a great help.

Thanks,

Rob

 

[mrdenny]

Yeah it does. You want to use the DISTINCT command.

INSERT INTO TableWithMacAddresses
SELECT DISTINCT MacAddress
FROM YourStagingTable
WHERE MaxAddress NOT IN (SELECT MacAddress FROM TableWithMacAddresses)

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[TamedTech]

Thank you for all your help on this Denny, It's now working PERFECTLY and its super speedy too.

Thanks again,

Rob

 

[mrdenny]

Excellent. I'm glad it's all working.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com