Shawn this is very helpful indeed and greatly appreciated, I now have a better understanding of what is needed I now have alot of reading to do :)
It funny I was thinking on the way home that logically it would make sense to pull the records in the format of the MySQL insert but for the life of me couldn't work out how this would work with the 3 tables and the auto_increment and also what was stumping my thought processing was the future additions to the products and then updates and then I lost track... so I guess I had better plan this step by step and not expect to get it completed by the end of the week :) Thank you Andrew >-----Original Message----- >From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] >Sent: 15 June 2004 20:46 >To: [EMAIL PROTECTED] >Cc: MySQL-Lista >Subject: RE: odbc reverse > > > >OK, now we are closer to a solution. Typically it is much easier to convert >data on the way OUT of a database than it is on the way INTO a database. > > I would recommend exporting the results of 3 specific queries from your >existing data (I can't tell you exactly how to do this as it differs from >product to product.) Each query would look like one of the three tables >that you want to put the data into in MySQL. If you are more comfortable >filling Excel spreadsheets with data, then make three new sheets. Each new >sheet would match the layout of one of your MySQL tables. Then, export >those sheets to CSV format. > >The trick here is to create a compatible data structure on disk so that we >can use the LOAD DATA INFILE utility of MySQL. The only interesting thing >is to keep your ID fields coordinated (to maintain your relationships). If >you export your existing(!) ID's then we can disable the auto_increment on >the ID columns, perform the import, then restore the auto_increment flag >(adjusting its value to the new max +1). No matter which method you choose, >you will have several steps and stages to this bulk import. > >Other solutions could be to use a commercially available data conversion >utility. Some of them can be quite pricey and may not be the right >investment for this one-time operation. > >Cross-platform or cross-product data conversion is one of those things that >we all have to do from time to time. Actually, I am currently writing some >VB Scripts to move records out of an Access database into a MySQL database >(also changing the data from a flat-record format to a more normalized data >structure). My design choice was based on platform (Win32 / MySQL 4.0.18) >and the fact that this process will have to be repeated many times per day. >I chose to use a script so that I can embed this process into the source >Access database when I am done and the provider of my source data (a >laboratory) will be able to perform several automated steps (built into the >script) with little or no Administrative interaction (leaving me to deal >with other issues). > >What will work for you depends on what you have available in terms of time >and options and maintainability. > >Shawn Green >Database Administrator >Unimin Corporation - Spruce Pine > > > > > > > "Business A2Z" > > <[EMAIL PROTECTED] To: ><[EMAIL PROTECTED]>, <[EMAIL PROTECTED]> > r.co.uk> cc: ><[EMAIL PROTECTED]>, "MySQL-Lista" > ><[EMAIL PROTECTED]> > 06/15/2004 03:07 Fax to: > > PM Subject: RE: odbc >reverse > Please respond to > > info > > > > > > > > > >Thanks Shawn you ahve been very hyelpful indeed and have helped clarify my >thoughts on finding a solution for this problem of 2 structurally different >DB's. > >The first DB is SQL and holds all compnay data the second is MySQLand holds >all >website products which are snippets from the SQL what I need to do is pull >the >main fields from the SQL and insert them inot the MySQL. > >However, and there is always a however they are structurally very >different, so >my theory was set a structure for the MySQL pull records from the SQL and >rename >the fields and then insert into the MySQL... but the insert would have to >be >inot 3 tables as there are some joins with products_id and a category_id so >its >not that straightforward.... the alternative is to print off the records >from >the SQL and typoe them all in by hand into the MySQL which doesn't sound >like >the 21st century! > >I guess I just need a insert that will read a CSV file is that possible? 3 >tables on one line? > >this is the structure of the 3 tables > ># ># Table structure for table `products` ># > >CREATE TABLE `products` ( > `products_id` int(11) NOT NULL auto_increment, > `products_quantity` int(4) NOT NULL default '0', > `products_model` varchar(12) default NULL, > `products_image` varchar(64) default NULL, > `products_image_med` varchar(64) default NULL, > `products_image_lrg` varchar(64) default NULL, > `products_image_sm_1` varchar(64) default NULL, > `products_image_xl_1` varchar(64) default NULL, > `products_image_sm_2` varchar(64) default NULL, > `products_image_xl_2` varchar(64) default NULL, > `products_image_sm_3` varchar(64) default NULL, > `products_image_xl_3` varchar(64) default NULL, > `products_image_sm_4` varchar(64) default NULL, > `products_image_xl_4` varchar(64) default NULL, > `products_image_sm_5` varchar(64) default NULL, > `products_image_xl_5` varchar(64) default NULL, > `products_image_sm_6` varchar(64) default NULL, > `products_image_xl_6` varchar(64) default NULL, > `products_bimage` varchar(64) default NULL, > `products_price` decimal(15,4) NOT NULL default '0.0000', > `products_moq` varchar(32) NOT NULL default '', > `products_pppack` varchar(16) NOT NULL default '', > `products_date_added` datetime default NULL, > `products_last_modified` datetime default NULL, > `products_date_available` datetime default NULL, > `products_weight` decimal(5,2) NOT NULL default '0.00', > `products_status` tinyint(1) NOT NULL default '0', > `products_tax_class_id` int(11) NOT NULL default '0', > `manufacturers_id` int(11) default NULL, > `products_ordered` int(11) NOT NULL default '0', > PRIMARY KEY (`products_id`), > KEY `idx_products_date_added` (`products_date_added`) >) TYPE=MyISAM AUTO_INCREMENT=2 ; > ># ># Dumping data for table `products` ># > >INSERT INTO `products` VALUES (1, 5000, 'sytlenumber0', NULL, NULL, NULL, >NULL, >NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, >'2.0000', '30', '5', '2004-06-15 15:49:37', NULL, NULL, '4.00', 1, 0, 0, >0); > ># -------------------------------------------------------- > ># ># Table structure for table `products_description` ># > >CREATE TABLE `products_description` ( > `products_id` int(11) NOT NULL auto_increment, > `language_id` int(11) NOT NULL default '1', > `products_name` varchar(64) NOT NULL default '', > `products_description` text, > `products_url` varchar(255) default NULL, > `products_viewed` int(5) default '0', > PRIMARY KEY (`products_id`,`language_id`), > KEY `products_name` (`products_name`) >) TYPE=MyISAM AUTO_INCREMENT=2 ; > ># ># Dumping data for table `products_description` ># > >INSERT INTO `products_description` VALUES (1, 1, 'test name', 'html table >that >could be an image!', '', 0); >INSERT INTO `products_description` VALUES (1, 2, '', '', '', 0); >INSERT INTO `products_description` VALUES (1, 3, '', '', '', 0); > ># -------------------------------------------------------- > ># ># Table structure for table `products_to_categories` ># > >CREATE TABLE `products_to_categories` ( > `products_id` int(11) NOT NULL default '0', > `categories_id` int(11) NOT NULL default '0', > PRIMARY KEY (`products_id`,`categories_id`) >) TYPE=MyISAM; > ># ># Dumping data for table `products_to_categories` ># > >INSERT INTO `products_to_categories` VALUES (1, 28); > >Andrew > >>-----Original Message----- >>From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] >>Sent: 15 June 2004 13:38 >>To: [EMAIL PROTECTED] >>Cc: [EMAIL PROTECTED]; MySQL-Lista >>Subject: RE: odbc reverse >> >> >> >>To be honest, there are probably hundreds of workable solutions to the >>*very* generic problem of moving data from one database to another. Using >>an ODBC driver (or drivers) would definitely be an option. One of the >>simplest methods is to export your spreadsheet to a CSV file (comma >>separated, string-quoted, one-row per line) then use the bulk import >>facility built in to MySQL (LOAD DATA INFILE...). >> >>It all depends on what you need as far as connectivity. Which solutions >>meet your design requirement I cannot guess as you have told us very >little >>about your situation. I recommended you read up on the MS documentation of >>OLE DB, ADO, ODBC, VBA, and the product Excel as some combination of those >>will allow you to create and keep a "live" connection to your data. If you >>do not need interactive database connectivity, try the bulk export/bulk >>import method I described earlier. >> >>Sorry I wasn't more help. :-( , >>Shawn Green >>Database Administrator >>Unimin Corporation - Spruce Pine >> >> >> >> >> >> "Business A2Z" >> >> <[EMAIL PROTECTED] To: >><[EMAIL PROTECTED]>, <[EMAIL PROTECTED]> >> r.co.uk> cc: >><[EMAIL PROTECTED]>, "MySQL-Lista" >> >><[EMAIL PROTECTED]> >> 06/14/2004 05:58 Fax to: >> >> PM Subject: RE: odbc >>reverse >> Please respond to >> >> info >> >> >> >> >> >> >> >> >> >>Ok final question on this for today, is it possible to move data into >MySQL >>through odbc from excel or any other data source come to think of it? >> >>>-----Original Message----- >>>From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] >>>Sent: 14 June 2004 21:52 >>>To: [EMAIL PROTECTED] >>>Cc: [EMAIL PROTECTED]; MySQL-Lista >>>Subject: RE: odbc reverse >>> >>> >>> >>>It seems to me that you are trying to use EXCEL as some type of live data >>>entry form. That would require "binding" an excel sheet to your data >>>source (through ADO through ODBC to the MySQL server). There are many >>>examples of this type of binding. You didn't say, but I assume that your >>>EXCEL is running on a windows platform. >>> >>>May I politely refer you to the Microsoft Developers Network site. I am >>>sure you will find some specific examples of binding Excel to various >>>databases. It should be the matter of a change or two to modify one of >>>their examples so that you are using MySQL and not MSSQL or Access as >>your >>>data source. You may have to search on several different terms, though. >>>That site has a **LOT** of information but it's not always where you >>>logically think it should be. >>> >>>http://msdn.microsoft.com/ >>> >>>Sorry I can't be more helpful but I haven't used EXCEL+ADO+ODBC to bind >>to >>>a live dataset in a VERY long time and I am sure to say the wrong thing. >>> >>>Shawn >>> >>> >>> >>> >>> >>> "Business A2Z" >>> >>> <[EMAIL PROTECTED] To: >>><[EMAIL PROTECTED]>, <[EMAIL PROTECTED]> >>> r.co.uk> cc: "MySQL-Lista" >>><[EMAIL PROTECTED]> >>> Fax to: >>> >>> 06/14/2004 04:30 Subject: RE: odbc >>>reverse >>> PM >>> >>> Please respond to >>> >>> info >>> >>> >>> >>> >>> >>> >>> >>> >>> >>>Thanks you for repying, I was expecting a reply such as this and I am not >>>prpared fully, here goes. >>> >>>I assumed that the ease with which data can be pulled using Myodbc from >>>MySQL >>>and chnaged within excel could simple be used to 'UPDATE' the selected >>>recordset. Clearly this is not the case. >>> >>>I am working with oscommerce and as many people have no doubted fund the >>>frustrating aspect of updating and adding many records (records that >>>already >>>exist in another DB excel for instance) So, I want to pull the records >>>from my >>>orginal source (its a SQL server but the application is bespoke) inot >>excel >>>and >>>then use a similar conncetion as odbc to update MySQL? Is this >>impossible? >>> >>>I am going to guess that it is and that I am going to have to create a >>>bespoke >>>manual 'UPDATE' query for the product tables ... alas there are more >>tables >>>with >>>joins so this is where the fun will begin. >>> >>>If I have this completely incorrect please do let me know, as I was >>>thinking of >>>something else called data transfer method >>> >>>Andrew >>> >>>>-----Original Message----- >>>>From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] >>>>Sent: 14 June 2004 20:56 >>>>To: [EMAIL PROTECTED] >>>>Cc: MySQL-Lista >>>>Subject: Re: odbc reverse >>>> >>>> >>>> >>>>I don't have enough information to answer your question. What platform >>>are >>>>you on? What language/DB library are you using to call the ODBC driver? >>>The >>>>answers you seek may be in the documentation for the particular >>>>language-library combination you are using. >>>> >>>>Eventually it all boils down to 3 different SQL commands: SELECT, >INSERT, >>>>and UPDATE. >>>> >>>>Please give us more details so that we can understand what you are >trying >>>>to do. >>>> >>>>Yours, >>>>Shawn Green >>>>Database Administrator >>>>Unimin Corporation - Spruce Pine >>>> >>>> >>>> >>>> >>>> >>>> "Business A2Z" >>>> >>>> <[EMAIL PROTECTED] To: "MySQL-Lista" >>>><[EMAIL PROTECTED]> >>>> r.co.uk> cc: >>>> >>>> Fax to: >>>> >>>> 06/14/2004 03:45 Subject: odbc reverse >>>> >>>> PM >>>> >>>> Please respond to >>>> >>>> info >>>> >>>> >>>> >>>> >>>> >>>> >>>> >>>> >>>> >>>>Hi All >>>> >>>>I have a puzzle or more like a mental block that needs some sensible >>>>thought >>>>inputs please. >>>> >>>>If I odbc inot mysql and pull out records and make chnages how do I put >>>the >>>>changes back into mysql ? >>>> >>>>Hope there is a simple response to this >>>> >>>>Andrew >>>> >>>> >>>>-- >>>>MySQL General Mailing List >>>>For list archives: http://lists.mysql.com/mysql >>>>To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] >>>> >>>> >>>> >>>> >>>> >>>> >>>> >>>>--- >>>>Incoming mail is certified Virus Free. >>>>Checked by AVG anti-virus system (http://www.grisoft.com). >>>>Version: 6.0.701 / Virus Database: 458 - Release Date: 07/06/2004 >>>> >>> >>> >>> >>> >>> >>> >>> >>>-- >>>MySQL General Mailing List >>>For list archives: http://lists.mysql.com/mysql >>>To unsubscribe: >>http://lists.mysql.com/[EMAIL PROTECTED] >>> >>> >>>--- >>>Incoming mail is certified Virus Free. >>>Checked by AVG anti-virus system (http://www.grisoft.com). >>>Version: 6.0.701 / Virus Database: 458 - Release Date: 07/06/2004 >>> >> >> >> >> >> >> >> >>-- >>MySQL General Mailing List >>For list archives: http://lists.mysql.com/mysql >>To unsubscribe: >http://lists.mysql.com/[EMAIL PROTECTED] >> >> >>--- >>Incoming mail is certified Virus Free. >>Checked by AVG anti-virus system (http://www.grisoft.com). >>Version: 6.0.701 / Virus Database: 458 - Release Date: 07/06/2004 >> > > > > > > > >--- >Incoming mail is certified Virus Free. >Checked by AVG anti-virus system (http://www.grisoft.com). >Version: 6.0.701 / Virus Database: 458 - Release Date: 07/06/2004 > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
