[SOLVED] .txt to MySQL import problem

[quillspirit]

I have over 30,000 product descriptions that I need to import into my database. These descriptions are currently in individual .txt files (Named for the item_id) such as 10004.txt

 

I managed to merge them all into one single .txt file - here is a sample: Note that all chars are represented in the descriptions: , " / - ; () [] {} | etc.

 

23018 14K gold tubular hoop earrings, (3mm tube, 1 3/16" diameter).  Pair

23019 14K gold tubular hoop earrings, (2mm tube, 3/4" diameter).  Pair

23047 14K gold cultured pearl diamond ring. (6 x 10mm, 1.5 points TDW).

23059 14K gold cultured pearl heart post earrings, (4.5-5 mm). Pair

23064 14K gold Figaro necklace, (1mm).

23065 14K gold 18" twisted serpentine necklace, (.5mm).

23067 14K gold 18" box necklace, (.5mm).

23069 14K gold serpentine necklace, (.75mm).

23073 14K gold box necklace (.5mm) with hearts (6 x 6mm).

23074 14K gold box necklace, (.5mm).

23075 14K gold diamond cut Boston cable necklace, (.5 mm).

23077 14K gold twisted Singapore necklace, (1 mm).

 

How can I make this .txt file importable into MySQL database? I need to drop it into an empty column, between the rest of the data that is already in there. (MAKING SURE it matches up with the right item_num)

 

 

item_num  item_page_num  item_name  item_description  item_units  item_price1 

x                   x                     x                     ???                      x              x

 

 

Right now, I have my scripting setup to include the needed .txt description to display with the other product info, but that just isn't very good. I want the descriptions to be searchable, and editable in the database.Another bonus would be if I could trim off the item ID numbers before each description, but some of them are alphanumeric or have a space. That is a project for another day.

 

Thanks for any help!

 

 

[bubblegum.anarchy]

If at all possible, a regular expression may be able to assure that all columns are quoted and comma seperated - then a simple CSV import into an existing database table that has the appropriate table column definitions.

[quillspirit]

If at all possible, a regular expression may be able to assure that all columns are quoted and comma seperated

 

Example please?

[bubblegum.anarchy]

example of what? - the regular expression?

[btherl]

Are you using unix or windows?

[quillspirit]

Yes... example of what you mean by regular expression.

 

Unix with:

 

PHP version 4.4.2

MySQL version 4.0.27-standard

 

 

btw, I do know how to import CSV from Excel... but I can't figure out how to break this up into rows into Excel.

[bubblegum.anarchy]

With the code block of data from your original post opened in vim (a text editor) and the following regular expression command

 

:%s/^\([0-9]*\)\s\(.*\)/'\1','\2'/g

 

will result in each line quoted as so:

 

'23018','14K gold tubular hoop earrings, (3mm tube, 1 3/16" diameter).  Pair'

'23019','14K gold tubular hoop earrings, (2mm tube, 3/4" diameter).  Pair'

'23047','14K gold cultured pearl diamond ring. (6 x 10mm, 1.5 points TDW).'

'23059','14K gold cultured pearl heart post earrings, (4.5-5 mm). Pair'

'23064','14K gold Figaro necklace, (1mm).'

'23065','14K gold 18" twisted serpentine necklace, (.5mm). '

'23067','14K gold 18" box necklace, (.5mm). '

'23069','14K gold serpentine necklace, (.75mm).'

'23073','14K gold box necklace (.5mm) with hearts (6 x 6mm).'

'23074','14K gold box necklace, (.5mm).'

'23075','14K gold diamond cut Boston cable necklace, (.5 mm).'

'23077','14K gold twisted Singapore necklace, (1 mm).'

 

You may need to run a quick regular expression to addslashes to single quotes before wrapping the id and description in quotes.

 

EDIT: the regular expression is playing havoc with the bbcode

[quillspirit]

I'm totally lost, bubblegum.

 

Regular Expressions have always looked like greek to me.

[quillspirit]

Ok how about this... is there any way to batch process (To CSV), the individual .txt files, which only contain the one line of description? Once that is done, I can handle it.

[bubblegum.anarchy]

I suppose so.. but I am not highly experienced with csv import/exporting.

 

Besides, opening the file in vim and executing the regexp:

 

%s/^\([0-9]*\)\s\(.*\)/('\1','\2'),/g

 

then replacing the last comma with a semicolon, appending the entire string with INSERT INTO tbl_name (id, description) VALUES would result in a SQL query that you could execute.

[quillspirit]

Ok, I've never used VIM (I use TextPad), but I'll give it a shot.

[quillspirit]

Ok, got VIM downloaded and installed, and am completely and utterly lost. :0/

[bubblegum.anarchy]

1. open vim

2. open the text file

3. type : (typing the colon should drop vim's focus the command line)

4. type %s/^\([0-9]*\)\s\(.*\)/('\1','\2'),/g

5. hit the enter key

5. swap the last comma in the text file with a semicolon

6. add the INSERT statement to the front of the entire string

 

.... and and you have yourself a SQL query.

[quillspirit]

Awesome... thank you so much. Now I just need to clean some of the data and sort it for import.

 

It looks like TextPad can run those expressions too, but I'm still lost, lol.