Insert Into 2

[Omnillas]

Got a slight issue and would like a little insight in how some of you have approached it.

I receive data via an uploaded CSV file and would like to select that data from that file and insert into a table of my complaints database. I currently have a DSNless connection to the CSV file that works like a charm and can see the data as well as the target MS SQL table that I would like to insert into.

For most query operations, a single database connection is allowed and no more. How do I handle doing the INSERT INTO from the CSV file into a SQL table? I've tossed around the idea of loading the data into an array but that seems like a real slow approach. I've searched around but haven't seen much discussion around this topic in Dreamweaver.

 

[lebisol]

hello Omnillas,
I am not sure of the whole operations flow and your need to 'browse & choose' records from csv file but if you are in need to get all the data from csv dump then try this:

http://www.aspfaq.com/show.asp?id=2438

or with some $

http://www.interaktonline.com/Products/Dreamweaver-Extensions/MXCSVImport-Export/Overview/

All the best!

:--------------------------------------------------------------------------:

All around in my home town,
They tryin' to track me down...

 

[Omnillas]

I'm currently evaluating the InterAKT product. Generating a basic upload/import page set is proving not to provide a solution for me. One of the developers has been working with me to resolve it so I have been looking elsewhere in between the developer feedback for alternate solutions. (Synopsis: sessions enabled, file uploads to tmp folder but does not populate the Temp folder. right after the file is uploaded the fild upload page is redisplayed. no solution or debugging yet.)

The MS SQL Bulk Insert is a very nice feature that I was not previously aware. I gave the example code a whirl and found out that it works best when the SQL server is on the same box as the IIS installation. For my environment, this is not the case. Using UNC paths in the command is proving to be a bit of a task as the IUSR_servername doesn't have rights to connect to the server share of the location of the CSV file. Based on current corporate policy, the server won't be added to the share for security reasons.

Thank you for the feedback and suggestions. A star at you for good information.

 

[SQLDenis]

Another way is to use OPENROWSET

INSERT INTO YourTable
SELECT *
FROM
OPENROWSET('MSDASQL',
'Driver={Microsoft Text Driver (*.txt; *.csv)};
DEFAULTDIR=C:\;Extensions=CSV;',
'SELECT * FROM CSVFile.csv')

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[lebisol]

Hi Omnillas,

"Using UNC paths in the command is proving to be a bit of a task as the IUSR_servername"

hm that would be tricky...but you should be able to create a share on your IIS (assuming that there is AD or some workgroup) and have access to that share for 'everyone' including your SQL_machine. I don't think you need to get IUSR_machine user involved.

Denis also made a nice example!
One thing I would consider is the size of your CSV file.
You said that you get 'updated' CSV file....one suggestion I would make is to try and not merge/update CSV file but rather keep them as 'fileS'. Depending on the size of csv you may hit some performance issues even when doing a bulk insert as well as using openrowset...
Anyhow, hope you have enough ideas to work something out.
All the best!

:--------------------------------------------------------------------------:

All around in my home town,
They tryin' to track me down...

 

[Omnillas]

Using OpenRowSet to pull the data in yields the following error:

Ad hoc access to OLE DB provider 'MSDASQL' has been denied. You must access this provider through a linked server.

All the ideas and suggestions have been really good ones but having the IIS installation on a different server than the SQL server is really a thorn in my development side.

Instead of placing the data in CSV format, what about an XML format and using a methodology to pull that in? Anyone have any suggestions about XML or more for my original approach?

 

[Omnillas]

Thank you very much lebisol for your ideas. I learned a couple new approaches if I can get my I.T. team to play ball with me. The policies they have placed on our development boxes are rather rigid and don't offer a whole lot in the way of work-arounds. :/

 

[lebisol]

Hello Omnillas,
my backgroud aways will lean towards networking admin etc. vs programming
MSSQL and IIS bundeled up in one box can be can of worms from admin side. Any time you are doing some upgrades you are 'downing' a whole system. All the vournabilities of IIS can open wholes to sql server and vice versa. Hardware issues = whole system is affected etc.
From programming side ..well, life would be easier if your admins were not so strict. I would approach your IT team with explanation that if they 'hide' your develpment box throuhg firewall and wan/lan access to anyone else there should not have any fear from it. In fact, thats why they are called 'development'machine...anythign goes ..it should be a programmers playground.

XML....I have very little experince doing anythign with it...from what I read and hear it can be very powerful...some sites are fully powered by xml and import export if more than posible.(same link as above to DW extension) On the other side, CSV is very portable...

But above all, if you can share, tell us more about the process of your project....these csv file are generated by someone of site? web users? or is this just 'admin side' where you are dong updates...can you schedule SQL server jobs to do imports wihtout IIS invovled....etc.

almost forgot ..your error:
and sorry for google link but it really opens a lot of resources that you can try

http://www.google.com/search?source...+to+OLE+DB+provider+'MSDASQL'+has+been+denied

All the best!

:--------------------------------------------------------------------------:

All around in my home town,
They tryin' to track me down...