Importing a tag formatted text file

[simppa77]

Hi guys! I recently got help from another forum on this subject,
but now I turn to you to make it complete.

I must import a tag formatted text file containing bibliographic data. I've enclosed a sample of this data at the bottom of this post. There's also the full tag listing, in case someone decides to actually help me with this. 🙂

I'm used to importing standard CSV text files using LOAD DATA INFILE, but this is a another thing. I've been told I must write a PHP script to import this data.

Here is what I've been told:

For each line of that input, let's call the first two characters the "2-code" and the rest of the line after the space the "data".

Create a table which can contain every attribute you might be sent, even if a lot of them will be empty.

In PHP, create an array which matches 2-codes to column names of your table. Something like: $translation = array ("IB" => "ISBN", "BI" => "binding", "AU" => "author");

Read each line of the file, and split it at the first space.

If the 2-code that you get this time is the same as the one you got on the last line, add data top the data you got last line.

If the 2-code is different, add the array value of $translation to a $columns string, and the value to a $values string.

For the first three lines of the first record you posted, $columns will read "ISBN, binding, author", and $values will read "'1859060196', 'PAPERBACK', 'SHINE, NORMAN'".

When you get to "**", combine everything into an SQL query, execute it, then clear out $columns and $values.

$query = "INSERT INTO books(" . $columns . ") VALUES(" . $values . ")";

Here's the sample data

IB 1859060196
BI PAPERBACK
AU SHINE, NORMAN
BC VXFN
CO UK
PD 19990923
NP 128
RP 9.99
RI 9.99
RE 9.99
PU CONNECTIONS BOOK PUBLISHING
YP 1999
TI NUMEROLOGY
TI YOUR CHARACTER AND FUTURE REVEALED IN NUMBERS
EA 9781859060193
RF R
SG 2
GC M01
DE A unique step-by-step visual approach to numerology, how to assess
DE characters and compatibility from names and birth dates.

IB 1898295395
BI SPIRAL BOUND
AU PRICE, ROBIN
BC ELX
CO UK
PD 19961231
RP 15.75
RI 15.75
RE 15.75
PU ENGLISH EXPERIENCE
YP 1996
SR BRAIN FRIENDLY RESOURCES
TI CONVERSATION 2: STUDENT TALK
EA 9781898295396
RF R
GC C01

Here are all the possible fields

** START OF RECORD

IB ISBN
AV UK AVAILABILITY

BI BINDING
AU AUTHOR

BC BIC CLASSIIFCATION

CO COUNTRY OF ORIGIN
ED EDITOR
IL ILLUSTRATOR

EI EDITION
IU ILLUSTRATIONS
CP CO-PUBLISHER
LA LANGUAGE OF BOOK
MP MIXED PACK COMPONENTS
NC NATIONAL CURRICULUM KEY STAGE
PD PUBLICATION DATE
PA PAGINATION
NP NUMBER OF PAGES
RP UK RRP SELLING PRICE
RI UK PRICE INCL VAT
RE UK PRICE EXCLUDING VAT
DI PAGE SIZE/DIMENSIONS
PU PUBLISHER NAME
YP YEAR OF PUBLICATION
RC BOOKDATA READERSHIP CODE
RS BOOKDATA READERSHIP TEXT
SR SERIES
SE SERIES EDITOR
TI TITLE
ST SUB-TITLE
PT PART-TITLE
TR TRANSLATOR
PN PART NUMBER
DE SHORT DESCRIPTION
EA EAN NUMBER
RF RETURNS FLAG
RD RETURNS DATE
SI NEW ISBN
WE WEIGHT (Grams)
SG STAR RATING
PI GARDNERS PUBLICITY DESCRIPTION
GC GARDNERS CLASSIFICATION CODE

Please help,

Simo Savonen
[e-mail]

[barand]

Interesting problem.

In addition to the translation array I think you will also need a col_type array as text values and dates in your insert sql will need to be enclosed in quotes, but not numbers

eg insert into book (author, price) values ('$author', $price)

You could get the types by querying the database but an array is an easier option.

$col_type = array(
	"IB" => "T", /*text*/
	"BI" => "T",
	"AU" => "T",
	"DE" => "T",
	"TI" => "T",
	"PD" => "D", /*date*/
	"RP" => "N"  /*num*/
	/* etc */
	);

The code below should give you good start

function ProcessBook ($cols, $vals) {
	global $translation, $col_types;
	/* I've got to leave something for you to do */
}

$F  = fopen("books.txt","r");
$lastcode="";
while (!feof($F)) {
	$line = trim(fgets($F,1024));
	if ($line=="") continue; /* ignore blanks */

$code = substr($line,0,2); /* split line into code and data*/
$data = substr($line,3);

if  ($code=="**") {
	ProcessBook($cols,$vals); /* call function to insert, passing the 2 arrays */
	$cols=array();
	$vals=array();
} else {
	if ($code==$lastcode) {
		$vals[count($vals)-1] .= " ".$data;
	} else {
		$cols[] = $code;
		$vals[] = $data;
	}
}
$lastcode=$code;
}
/* do not forget to process the last book */
ProcessBook($cols,$vals);

hth