• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 246
  • Last Modified:

Need to import data into Database that has strange architecture

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).

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?

  • 11
  • 7
1 Solution
Dave BaldwinFixer of ProblemsCommented:
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'.
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
befidledAuthor Commented:
@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.


Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

Dave BaldwinFixer of ProblemsCommented:
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?
befidledAuthor Commented:
@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
befidledAuthor Commented:
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.

Dave BaldwinFixer of ProblemsCommented:
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.
befidledAuthor Commented:
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?
Dave BaldwinFixer of ProblemsCommented:
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.
error_reporting(E_ALL ^ E_NOTICE);
$dbhost = "";    // Your database server
$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">
<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; }
<div id="wrap">
<h2 align="center" style="margin: 1px;">CSV Import PHP Demo</h2>
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];
	case "2":
		$website = $parts[4];
	case "92.1":
		$street = $parts[4];
	case "92.2":
		$street2 = $parts[4];
	case "92.3":
		$city = $parts[4];
	case "92.4":
		$state = $parts[4];
	case "92.5":
		$zip = $parts[4];
	case "90":
		$phone = $parts[4];
	case "91":
		$email = $parts[4];
	case "89":  // not in sample listing
		$fax = $parts[4];
	case "5":  // not in sample listing
		$contact = $parts[4];


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)) {
	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>";


Open in new window

befidledAuthor Commented:
Wow, thanks Dave, I'll give this a try and let you know how it goes.
Dave BaldwinFixer of ProblemsCommented:
Here's my test file so you can see what it should look like.
befidledAuthor Commented:
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


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?
Dave BaldwinFixer of ProblemsCommented:
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.

befidledAuthor Commented:
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.

Dave BaldwinFixer of ProblemsCommented:
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.
befidledAuthor Commented:
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!
befidledAuthor Commented:
@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.

befidledAuthor Commented:
I solved this just by using brute force and inserting each record into the database individually.
befidledAuthor Commented:
I had to find my own solution because the solutions provided did not work.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 11
  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now