Solved

Need to import data into Database that has strange architecture

Posted on 2011-03-12
19
197 Views
Last Modified: 2012-05-11
I have a set of a few thousand records that I need to import into my database. The data is structured so that each record in the database is comprised of a number of rows within the database (see attached image).

database-structure
So in the case of the attached image the 2nd record has only three fields entered and they include: company name (field #1), company website (field #2), and company logo (field #4).

If I have an Excel spreadsheet where each row includes the entire record (i.e. column 1 is company name, column 2 is contact name, column 3 is street address, etc.) what is the best way to import this into my database (using PHP) so that each field in my Excel Row (i.e. A1) is put in its own row in the db like in the image attached?

thanks.
0
Comment
Question by:befidled
  • 11
  • 7
19 Comments
 
LVL 82

Expert Comment

by:Dave Baldwin
ID: 35119297
I can see the pattern there but this may be difficult.  This is a very sparse database.  "lead_id" identifies the fields that comprise a record and "field_number" identifies the fields that are of the same kind or column.  Field id's 1,2,4 are pretty easy but the field numbers go up to 117 in the image you've posted.  If you put this in an Excel sheet, you would have to make it wide enough to accomodate up to field 117 even there may be only one record that uses that field.  I would first make a unique list of the "lead_id"s and the "field_number"s to see what I was dealing with.  Since it is so sparse, I wouldn't be surprised if there were numbers missing in fields.

An SQL query to return a single 'record' of related info would be something like:

"SELECT * FROM mytable WHERE lead_id = 'x' ORDER BY field_number"

You can expect different amounts of data for the different 'records'.
0
 
LVL 4

Expert Comment

by:mawni
ID: 35119330
Actually I didn't get the point exactly, but if you need to import data from Excel file, the best free application that do it is TOAD for mySQL
0
 
LVL 1

Author Comment

by:befidled
ID: 35119920
@DaveBaldwin: Thanks, but I need a way to get the data into my database, not extract it from my db.

@mawni: I'm not sure what TOAD is, I'll take a look but I use Navicat for most of my MySQL db needs. This particular import need however seems to be more of a design pattern which probably exists but I don't know how to do.

Any practical help would be great.

thanks.

0
 
LVL 82

Expert Comment

by:Dave Baldwin
ID: 35120021
I guess what I said didn't make any sense to you?  You see that the fields for a particular record are on different lines.  Did you notice that they aren't in the same order for different records?
0
 
LVL 1

Author Comment

by:befidled
ID: 35120134
@DaveBaldwin: I understand what you were saying however the order doesn't really matter. For instance, the Company name field will always have a field_number==1 and likewise the street address will always have a field_number==92.1. The field_number is essentially the key to each specific field. And querying a single record isn't really my issue at all. That is very straightforward because the lead_id field connects a records fields.

I need a script that will allow me to loop through each row of my spreadsheet or csv file, and insert each column into its own row but where all items from column A (which is the company name) would also have a field_number set to 1, while all items from column 3/column C (which is the street address) would include a field number of 92.1.

There are really only 10 columns I need to iterate through. Where you see the field_number ==  117, those are categories which we don't have in our import set. So basically I have the following fields I need to import and the rest will need an actual person to manually set categoies.

The fields we need to import are:

Column A == Company name
Column B == Contact name
Column C == Street Address
Column D == City
Column E == State
Column F == Zip
Column G == Phone
Column H == Fax
Column I == Email
Column J == Website
0
 
LVL 1

Author Comment

by:befidled
ID: 35120139
The final solution may even be to import each column individually. I'm not sure, really looking for a best practice solution because this is likely not the only time I'll have to do this.

thanks.
brian
0
 
LVL 82

Expert Comment

by:Dave Baldwin
ID: 35120283
The pseudo code I listed above I would put in a loop to go thru all the 'x's.  It would get the data for each one and turn it into an insert into the table you need.  Since you don't need all the fields that makes it fairly easy.  A few thousand rows should convert in a few seconds.
0
 
LVL 1

Author Comment

by:befidled
ID: 35120794
I understand that. I am not clear on how that insert would look in PHP, can you maybe give me a sample of how I would get the data from an Excel doc (or CSV or Text file) and into my db?
0
 
LVL 82

Expert Comment

by:Dave Baldwin
ID: 35123662
It took a while but this will do it.  Do a CSV export with only the 'value' column defined as text.  There are some of your desired fields that don't show up in your sample data so you'll have to fix the 'case' statements to recognize them.  I've included an echo of the data for troubleshooting that you will want to comment out when you run against the real data.
<?php 
error_reporting(E_ALL ^ E_NOTICE);
$dbhost = "10.202.46.41";    // Your database server 192.168.0.10
$dbuser = "csvimp";      // Your db username
$dbpass = "impass";      // Your db password
$dbname = "csvimport";      // Your database name
$dbtable = "nads";  // the table we're using

$dbc = new mysqli($dbhost, $dbuser, $dbpass, $dbname);

/*
 * Use this instead of $connect_error if you need to ensure
 * compatibility with PHP versions prior to 5.2.9 and 5.3.0.
 */
if (mysqli_connect_error()) {
    die('Connect Error (' . mysqli_connect_errno() . ') '
            . mysqli_connect_error());
}
//echo 'Success... ' . $mysqli->host_info . "\n";

?>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<html>
<head>
<title>CSV Import PHP</title>
<meta name="author" content="Dave Baldwin" />
<style type="text/css">
<!-- 
body {color: #000000; background-color: #ffffff; font-family: Arial; font-size: 12pt; margin-top: 1px;}

#wrap { width: 980px; margin-left: auto; margin-right: auto; }

A {color: #000000; text-decoration: none;}
A:link {color: #000080; background-color: #eeeeee;}
A:visited {color: #000080; background-color: #eeeeee;}
A:hover {color: #ffffff; background-color: #000080;}
A:active {color: #cccccc; background-color: #eeeeee;}

.nav {color: #000080; font-family: Arial; font-size: 12pt; font-weight: bold; text-decoration: none; background-color: #f0f0f0;}
.nav A {color: #000080; font-family: Arial; font-size: 12pt; text-decoration: none;}
.nav A:link {color: #000080; font-weight: bold;}
.nav A:visited {color: #000080;}
.nav A:hover {color: #ffffff; background-color: #000080;}
.nav A:active {color: #000080; }
 
-->
</style>
</head>
<body>
<div id="wrap">
<h2 align="center" style="margin: 1px;">CSV Import PHP Demo</h2>
<?php
if ($dbc != "") { 

$impfn = "CSVimport1.csv";
$file_handle = fopen($impfn, "r");
$prev = 0;
while (!feof($file_handle)) {
$line_of_text = fgets($file_handle);
echo ":: ".$line_of_text."<br>";
$parts = explode(",", $line_of_text);
if ($parts[1] != $prev) {
	if($prev != 0) {
		$query = "INSERT INTO nads (company, contact, street, city, state, zip, phone, fax, email, website) VALUES ('$company', '$contact', '$street $street2', '$city', '$state', '$zip', '$phone', '$fax', '$email', '$website')";
//echo $query."<br>";

		$result = mysqli_query($dbc, $query)
	  	or die('Error inserting in '.$dbname." ".mysqli_error($dbc));
		}
	// clear data for next row
	$company = "--";
	$contact = "--";
	$street = "--";
	$street2 = "--";
	$city = "--";
	$state = "--";
	$zip = "--";
	$phone = "--";
	$fax = "--";
	$email = "--";
	$website = "--";
	// set prev for next loop
	$prev = $parts[1];
	}
//$plen = strlen($parts[4])-3;
//$parts[4] = substr($parts[4],1);
//$parts[4] = substr($parts[4],0,-1);
$parts[4] = str_replace('"','',$parts[4]);
switch($parts[3]) {
	case "1":
		$company = $parts[4];
		break;
	case "2":
		$website = $parts[4];
		break;
	case "92.1":
		$street = $parts[4];
		break;
	case "92.2":
		$street2 = $parts[4];
		break;
	case "92.3":
		$city = $parts[4];
		break;
	case "92.4":
		$state = $parts[4];
		break;
	case "92.5":
		$zip = $parts[4];
		break;
	case "90":
		$phone = $parts[4];
		break;
	case "91":
		$email = $parts[4];
		break;
	case "89":  // not in sample listing
		$fax = $parts[4];
		break;
	case "5":  // not in sample listing
		$contact = $parts[4];
		break;
		}
	}

}
 ?>

<?php 
echo '<table border="1" cellpadding="1" cellspacing="1">';
//$lid = mysqli_insert_id($dbc);
if($lid < 1) $lid = 1;			
$sql = "SELECT company, contact, street, city, state, zip, phone, fax, email, website FROM nads LIMIT 12";
//echo $sql;
$result = @mysqli_query($dbc, $sql)
	  	or die('Error selecting from '.$dbname." ".mysqli_error($dbc));

while($row = mysqli_fetch_object($result)) {
	//print_r($row);
	echo "<td>$row->company</td>";
	echo "<td>$row->contact</td>";
	echo "<td>$row->lastName</td>";
	echo "<td>$row->street</td>";
	echo "<td>$row->city</td>";
	echo "<td>$row->state&nbsp;&nbsp; $row->zip</td>";
	echo "<td>$row->phone</td>";
	echo "<td>$row->website</td>";
	echo "<td>$row->email</td>";
	echo "</tr>";
	}
echo "</table>";
?>
</div>

</body>
</html>

Open in new window

0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 1

Author Comment

by:befidled
ID: 35124058
Wow, thanks Dave, I'll give this a try and let you know how it goes.
0
 
LVL 82

Expert Comment

by:Dave Baldwin
ID: 35124097
Here's my test file so you can see what it should look like.
CSVimport1.csv
0
 
LVL 1

Author Comment

by:befidled
ID: 35124323
Unfortunately, that doesn't work with the data structure I currently have. I'm restricted to this db structure so need to figure out how to loop through my data so that it populates appropriately. That's what I really need help with, I know how to insert a standard csv file if I have total control over the field names, but this case is not very intuitive for me.

 I only have 5 columns with column names of id, lead_id, form_id, field_number, and value (see image in first post)

So the first record will actually use 10 rows of data.
id=1, lead_id=1, form_id=1, field_number=1, and value=Company Name
id=2, lead_id=1, form_id=1, field_number=2. and value=Company website
id=3, lead_id=1, form_id=1, field_number= 92.1 and value=111 E Camelback Rd

etc.

I'm thinking I might simply import each column of data as a batch, for instance instead of trying to import record by record, I'll import all company names in one insert. Since each of them has a field_number of 1 and their own unique lead_id, it doesn't matter what order they appear in the db.

Does that make any sense?
0
 
LVL 82

Expert Comment

by:Dave Baldwin
ID: 35124775
Since my code does exactly what you said you needed, I'm thinking again that maybe you don't understand what I'm saying or doing.  Did you look at the sample CSV file I posted?

The SQL statement is for the 'output' table.  I copied that from the column list you posted above.  The 'while' loop reads a line at a time from the CSV file that you displayed above, collects the data into the appropriate variables, and writes it out to the database when it has read all of the data for one 'lead_id'.  I used the 'field_number' to decide which variable the data belonged to.

If you are looking for the names you see in your pic above, they won't be because I read the line in and split it into an array and I use the array for processing.  I did include them in the first line of the CSV file.

0
 
LVL 1

Author Comment

by:befidled
ID: 35125169
Thanks Dave, I do fully understand what you are doing, however it isn't solving my problem because the structure of your CSV file is nothing like the CSV file I need to import. Take a look at my attached sample CSV file and you'll see what I mean. And maybe my problem will be more clear, I am trying to migrate data that is structured in one way, into a db that is structured another way. I don't have a choice in how either is structured, I just need to find a solution to migrate the data.

My Excel table is much more traditional, with Column A==Company, Column B==Contact name, Column C==Street Address, etc.

So while your code does import the data, I would need to reformat my spreadsheet which would create the same issues only at the spreadsheet level.


test-data-import.csv
0
 
LVL 82

Expert Comment

by:Dave Baldwin
ID: 35125551
Now you have me confused.  Which is the source and which is the destination?  Are you wanting to convert from one CSV file to another one in a different format?  Are you calling the Excel file your database?  I figured you were using a database like MySQL.
0
 
LVL 1

Author Comment

by:befidled
ID: 35125554
I've managed to simply break my spreadsheet into 10 separate CSV files and am using Navicat to import them in. So in my db the first 1000 entries are Company name, the next 1000 are Contact name, etc. But since the row number for each record in my spreadsheet is imported as the Lead_Id, and since that Lead_id is used to connect records, I can still run queries for a specific record even though it's fields are basically 1000+ rows apart.

It's not the most elegant solution and I'd still like to find a good looping solution but if it works I'm happy with it!
0
 
LVL 1

Author Comment

by:befidled
ID: 35125562
@Dave, as I indicated in my original post:

If I have an Excel spreadsheet where each row includes the entire record (i.e. column 1 is company name, column 2 is contact name, column 3 is street address, etc.) what is the best way to import this into my database (using PHP) so that each field in my Excel Row (i.e. A1) is put in its own row in the db like in the image attached?

I have a CSV file like the one I described in my original question (probably should have attached a sample also) and like the sample I attached a few posts ago. I need to import that data into a db structure like the image that I included in my original post.

You kept explaining to me that I didn't understand your answer but you didn't every understand the problem.

No big deal, it's solved now. Thanks for taking a crack at it.

brian
0
 
LVL 1

Accepted Solution

by:
befidled earned 0 total points
ID: 36221018
I solved this just by using brute force and inserting each record into the database individually.
0
 
LVL 1

Author Closing Comment

by:befidled
ID: 36246768
I had to find my own solution because the solutions provided did not work.
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
Generating table dynamically is the most common issue faced by php developers.... So it seems there is a need of an article that explains the basic concept of generating tables dynamically. It just requires a basic knowledge of html and little maths…
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now