Hi All,
I need to import data from a large XML file.
I do not want to use XML Bulk upload or use ADO components.
I am looking for a way to use sql server scripts to open
this file through a ODBC driver , OpenRowset function.
Are there any ODBC or OLEDB drivers exist that can be used
in OpenRowset function ?
Not that I know of.
You may want to look at using a stored proc and OpenXML (although XML
Bulkload is more efficient).
Sorry.
Michael
"vaga" <anonymous@.discussions.microsoft.com> wrote in message
news:00a101c49b3b$9b4c2150$a301280a@.phx.gbl...
> Hi All,
> I need to import data from a large XML file.
> I do not want to use XML Bulk upload or use ADO components.
> I am looking for a way to use sql server scripts to open
> this file through a ODBC driver , OpenRowset function.
> Are there any ODBC or OLEDB drivers exist that can be used
> in OpenRowset function ?
>
Showing posts with label components. Show all posts
Showing posts with label components. Show all posts
Friday, March 30, 2012
Friday, March 23, 2012
Importing old data into asp_tables...?
I have created an application that uses the login, create, etc login components in .net. How hard is it to convert all my old users, passwords, usertypes into the new tables. It almost looks like I have to do them by hand and created a new guid(userid), along with the same guid in the aspnet_usersinroles and aspnet_Membership. Is there a script to do this programatically?
Hi,
You can use t-sql cursor to go record by record thru your old table and inserting to asp.net tables. The difficult part is passwords. I think it would be too difficult to decode and encode password in t-sql.
So, probably first import data and then use asp.net to re-encode passwords. As far as I know there are no utilities to do it automatically.
Wednesday, March 7, 2012
Importing Data from Excel to update existing fields
I have an excel file that contains column A with names of components and products followed by column B which has each respective quantity on hand. I want to import that data to our website's SQL database that has a products table with a column, Pf_ID, that has only product names not component names and In_Stock which contains out-dated information that I want updated from column B of the excel file.
I think I've figured out how to use DTS and update the two fields, but I'm afraid that when everything runs new entries will be created with component information. Is it possible to specify that only rows where Pf_ID matches some row in column A that same row's column B will be used to update the data in In_Stock. I may have just made things too confusing than they need to be, but I don't have much experience with EM or Excel.
I'm also considering trying to write a macro that will match Pf_IDs in an exported excel file of the products table and take rows out of the excel file with current quantity information putting them in a new excel file to import into the website's database.
Please help, this is getting really confusing.I would bcp the data into a staging table, then use sql to do whatever you have to do.
But that's just me
MOO|||I'm not sure what bcp stands for ... I've only seen dts. I think your idea of using a staging table to query data from sounds good, but I kind of want this to be as simple a process as possible. Something that relatively anyone with instructions could click buttons and have done routinely.|||use dts to bring the excel data into a staging table
(a table created just to store data in the middle of a coomplex import)
then use an update statement against the staged data to update the destination data
(use the transact sql task)
For a more straightforward (but more advanced) method you might consider using the data driven query task and you could eliminate the staging table.
once you get this running you can set it up as a batch file or script or even call it from a sp. so anyone can run it.|||I've called tech support and checked permissions on the product table, but niether of us can seem to get this query to work when importing data from an excel workbook, Peach$. Is there something wrong with my query?
Here's what the support said when he tried:
It doesnt appear to be working running as SA either. I get a Error Line 3 near ) and then the db is read-only.
I cleared active connections to the DB and even stopped access, but it still appears to be locked by something, or perhaps the table is protected somehow?
--Raj
Here is the actual query:
update ASI.dbo.Product
set ASI.dbo.Product.In_Stock = (select `Peach$`.`Quantity On Hand`
from `Peach$`
where ASI.dbo.Product.Pf_ID = `Peach$`.`Item ID`)
where exists
(select `Peach$`.`Quantity On Hand`
from `Peach$`
where ASI.dbo.Product.Pf_ID = `Peach$`.`Item ID`);
Sorry for the lag time with a reply. I hope this gets read now.
I think I've figured out how to use DTS and update the two fields, but I'm afraid that when everything runs new entries will be created with component information. Is it possible to specify that only rows where Pf_ID matches some row in column A that same row's column B will be used to update the data in In_Stock. I may have just made things too confusing than they need to be, but I don't have much experience with EM or Excel.
I'm also considering trying to write a macro that will match Pf_IDs in an exported excel file of the products table and take rows out of the excel file with current quantity information putting them in a new excel file to import into the website's database.
Please help, this is getting really confusing.I would bcp the data into a staging table, then use sql to do whatever you have to do.
But that's just me
MOO|||I'm not sure what bcp stands for ... I've only seen dts. I think your idea of using a staging table to query data from sounds good, but I kind of want this to be as simple a process as possible. Something that relatively anyone with instructions could click buttons and have done routinely.|||use dts to bring the excel data into a staging table
(a table created just to store data in the middle of a coomplex import)
then use an update statement against the staged data to update the destination data
(use the transact sql task)
For a more straightforward (but more advanced) method you might consider using the data driven query task and you could eliminate the staging table.
once you get this running you can set it up as a batch file or script or even call it from a sp. so anyone can run it.|||I've called tech support and checked permissions on the product table, but niether of us can seem to get this query to work when importing data from an excel workbook, Peach$. Is there something wrong with my query?
Here's what the support said when he tried:
It doesnt appear to be working running as SA either. I get a Error Line 3 near ) and then the db is read-only.
I cleared active connections to the DB and even stopped access, but it still appears to be locked by something, or perhaps the table is protected somehow?
--Raj
Here is the actual query:
update ASI.dbo.Product
set ASI.dbo.Product.In_Stock = (select `Peach$`.`Quantity On Hand`
from `Peach$`
where ASI.dbo.Product.Pf_ID = `Peach$`.`Item ID`)
where exists
(select `Peach$`.`Quantity On Hand`
from `Peach$`
where ASI.dbo.Product.Pf_ID = `Peach$`.`Item ID`);
Sorry for the lag time with a reply. I hope this gets read now.
Subscribe to:
Posts (Atom)