How to import this text file into a MySQL db using CFMX??

[longcroft]

I'm developing a books acquisition system, and have been given some sample bibliographic data as a text file, which I will need to import into my db on a weekly basis. I've only dealt with straightforward comma delimited text imports previously, so any suggestions for an efficient method to import data in this format would be appreciated!

Here are a few sample records from the text file:

**
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
**


There will be 1000s of records in each text file to be imported, so I'm concerned that whatever method I use won't grind the server to a halt in the process

TIA for any help or advice you can offer!

 

[TruthInSatire]

I don't suppose asking them to put a tab between the two fields is possible? then you could use cfhttp with "tab" as a delimiter.

http://thereptilian.com/

 

[TruthInSatire]

I've never tried, but I think you may be able to do it with phpMyAdmin?

http://thereptilian.com/

 

[longcroft]

Thanks for your replies bombboy - we're using MySQL Front for administering the db, and it doesn't wanna know about importing a file in this format!

I can't really see how CFHTTP can help us either, but to be fair I've never used it before, so perhaps I'm missing something. The first two characters of each line within each record represent the field identifier, and the record delimiter is the "**".

I'm waiting for the company who supplies the data to ring me back to ask if they can advise - there must be loads of other people using the same file as this is a major books supplier in the UK. Am I just being really dumb?

 

[longcroft]

Forgot to add - the file is in the region of 130 Mb in size ... AAAAAAAAARGH!

 

[TruthInSatire]

if all that junk goes in the same record, replace the "**" with a "," and ad a field name prior to all the records. I say replace because i think cfhttp only allows a "," or TAB delimiter

cfhttp can be used to read a text file like a db table.
run a select query from the text file, then insert it into the db.

http://thereptilian.com/

 

[longcroft]

thanks again bb for taking the time to reply

am reading up on CFHTTP here ... i can see where you're coming from, but I can't see how replacing the record delimiter (**) with a comma is going to help - the field contents will have commas in them, so I would also need to escape these somehow, and then somehow delimit the field contents themselves (will to live diminishing here just trying to describe it!). I could do all the necessary parsing if it was a small file (I think), but the file is 133Mb - surely the server would just grind to a halt if I attempt to manipulate such a huge string?

 

[longcroft]

another thought - would it be better to post this on the MySQL forum and avoid going through CF altogether?!

 

[TruthInSatire]

good point you could replace them with " "," " (double quote comma double quote) add the begining " and ending " manualy and use a " qualifier that would ignore the ,'s in your field.

http://thereptilian.com/

 

[longcroft]

adding anything manually just isn't an option with a 133Mb text file!

 

[TruthInSatire]

you'd only have to do the very first and last record and very last.

http://thereptilian.com/

 

[longcroft]

ok, will have a play with it using your suggestions bb

thanks again

 

[TruthInSatire]

sorry i probably wasn't clear
you've got
**
bla bla bla
**
bla bla bla
**
more bla
**
after you do the replace you have
","
bla bla bla
","
bla bla bla
","
more bla
","
the first and last "," just change to "
that gives you the "bla","bla","bla" syntax you need to make a comma delimited quote qualified list. hope it helps

http://thereptilian.com/

 

[longcroft]

Just an update on this one - have decided although not impossible to convert this file into a CSV with CF, it would more than likely grind our server to a halt owing to the sheer size of the file and the convoluted parsing we would need to do to convert it. We're getting a 3rd party supplier to write us a VB app which will whizz thru and create a CSV containing just the fields we need, which we'll then import using CF.

Thanks BB for your help anyway!