My SQL Import xls files
-
Hello everybody First sorry my english but i need som help. I have a website where i sal computer and other stof, every day i get i xls. file from one company with price list model number, description and other thing. Every product in my website has parnumber: thats unik. So i want to make a function in c# to update the price in mysql database because the price changes every day. How can i make a function like update table_name from c:\test.xls (take just price and partnumber from xls file ) where partnumber=(partnumber from xls) ? I hope really much you can help me.
-
Hello everybody First sorry my english but i need som help. I have a website where i sal computer and other stof, every day i get i xls. file from one company with price list model number, description and other thing. Every product in my website has parnumber: thats unik. So i want to make a function in c# to update the price in mysql database because the price changes every day. How can i make a function like update table_name from c:\test.xls (take just price and partnumber from xls file ) where partnumber=(partnumber from xls) ? I hope really much you can help me.
You will need to write code to parse the Excel file ( either export as csv, or use the Office interop stuff ), then write your SQL.
Christian Graus Driven to the arms of OSX by Vista.
-
You will need to write code to parse the Excel file ( either export as csv, or use the Office interop stuff ), then write your SQL.
Christian Graus Driven to the arms of OSX by Vista.
Thanks but do you have som code in c# i can use ?
-
Thanks but do you have som code in c# i can use ?
Not really, no. Connecting to a db is trivial, and widely documented. Parsing a CSV is trivial, use File.ReadAllLines and the Split method on the string class to break each line into discreet items. Controlling Excel is also well documented.
Christian Graus Driven to the arms of OSX by Vista.
-
Hello everybody First sorry my english but i need som help. I have a website where i sal computer and other stof, every day i get i xls. file from one company with price list model number, description and other thing. Every product in my website has parnumber: thats unik. So i want to make a function in c# to update the price in mysql database because the price changes every day. How can i make a function like update table_name from c:\test.xls (take just price and partnumber from xls file ) where partnumber=(partnumber from xls) ? I hope really much you can help me.
Burim, I had written an app to import excel files. That was some 3 years ago, so I no longer have the code. I did a google search for "C# import excel file into sql" and I found the same link I had used to write the code some 3 years ago. The link: http://davidhayden.com/blog/dave/archive/2006/05/31/2976.aspx[^] I recommend you use this link to do the following: 1) SQLBulkImport the Excel file into an import table (a table in the same database but that is only used for this purpose) 2) Call stored procedure that grabs the needed data from the import table and updates whatever table(s) you need to update. In my case, I had a Unique partNumber, too, so I would A) Mark records in my primary table as "inactive" (I use a tinyint with 1=inactive and 2=active) that were no longer in the import table B) Add new records from the import table into the primary table. C) Update records in the primary table with changed values in the import table (usually the description and price). Hope this helps.
ASP ~ Apple Simply Performs