Importing string list from SQL dump

[[RTS]BN+VS*]

Hey,

(not sure this is the right section)

I want to import a list of string from an SQL dump of one table.

So if I have a db with a table products, and a field product_name in it, I want to end up with a list/array containing all the product names. After opening the SQL dump with my app the user should be able to choose the relevant table and field, and then the data should be imported.

I have already done this with a simple txt file with on each line a product name, but obviously getting the data from an SQL dump is a little harder. Can anyone give me a hand with this or refer me to a helpful article on this?

Cheers
BN

 

[cjard]

Can you show or upload a fragment opf the data? Post it in a code block so that the forum line breaks don't affect the layout

 

[[RTS]BN+VS*]

Hey,

Sure, here you have some example data:

-- phpMyAdmin SQL Dump
-- version 2.10.2
-- http://www.phpmyadmin.net
-- 
-- Host: localhost
-- Generation Time: Mar 20, 2009 at 01:02 PM
-- Server version: 4.1.10
-- PHP Version: 5.2.8

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";

-- 
-- Database: `spamz`
-- 

-- --------------------------------------------------------

-- 
-- Table structure for table `foo`
-- 

CREATE TABLE `foo` (
  `id` int(11) NOT NULL auto_increment,
  `field1` varchar(250) NOT NULL default '',
  `field2` varchar(250) NOT NULL default '',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=105 ;

-- 
-- Dumping data for table `foo`
-- 

INSERT INTO `mailssent` VALUES (1, 'textz1', 'textz4');
INSERT INTO `mailssent` VALUES (2, 'textz2', 'textz5');
INSERT INTO `mailssent` VALUES (3, 'textz3', 'textz6');
...

So if the user specifies table foo, and field field1, then I should end up having an array containing textz1, textz2 and textz3.

Cheers
BN

 

[cjard]

Er. Your dump says "dumping data for 'foo' but then talks about 'mailssent' ?

Anyways; Read the entire file into a string and split it by the delimiter "INSERT INTO"
That gets your data into neat blocks.

Forget using an array or list, it'll be too confusing because you'll need a Dictionary(Of String, List(of List(of Object)))
Use a dataset instead. If you really want it in an arrya, you can ask a row for its ItemArray, and you get other nice things like Xml storage etc when you use datasets
DataSet -> Named DataTables e.g. mailssent -> Rows -> Values


Parse each block. If the block contains a CREATE TABLE statement maybe you'll wanna get clever with it and use it to make a datatable of that type configuration in your dataset, or maybe discard it

If the block contains VALUES then it a values block. Split on VALUES and you have the name of the table in array(0) and the values in array(1)
Trim the spaces ' and ` chars off the name and use it to index the dataset - add a table if it doesnt exist
Parse the values block, obeying standard string rules or you can cheat and modify the code of Jouni Heikniemi's CSV reader (it works pretty well and is light weight enough, though it's C# - if you cant read/compile C# convert it to vb) because basically the VALUES block is a CSV line once you strip off the brackets and colons JHLib - Jouni Heikniemi's .NET tool library


If the lines in the file contain line breaks, you may be better off replacing all newlines with nothing, and replacing all semicolons with newlines before you begin - depends if youre going to use something e.g. JH's csv reader, that attaches a meaning to newlines

Good luck. Have fun

 

[[RTS]BN+VS*]

Hey,

I just took a table from some random db and made it example suited, but apparently forgot to replace the table name with foo there

Ty a lot for your reply, I think this will help me out!

Cheers
BN