Importing Flat File into mySQL

[dvduval]

I'm trying to import a file called products.dat into mySQL. I can get it into Excel, but I keep having a problem with extra quotation marks being inserted into the fields.

In general, what is available for moving files to mySQL from text, tab delimited, Excel, Access, dbf, etc.?

[jatar_k]

If it is a comma delimited file check this out
fgetcsv [php.net]

[bonanza]

What method are you using for the import? Command line MysqlImport? There are lots of options to tell it the format, including fields-enclosed-by settings: [mysql.com...]

You might want to check out PHPmyAdmin. It's a web interface for managing databases and has an import screen that makes it all too easy: [phpmyadmin.net...]

[dvduval]

Thanks for your answers.
The file I am trying to import contains these fields:
PRODUCT_CODE, PRODUCT_NAME, CATEGORY_CODES, PRODUCT_PRICE, PRODUCT_COST, PRODUCT_WEIGHT, PRODUCT_DESC, PRODUCT_TAXABLE, PRODUCT_ACTIVE, PRODUCT_THUMBNAIL, PRODUCT_IMAGE

The PRODUCT_DESC has been a problem because it contains quotes and html.

I do have access to phpMyAdmin and use it frequently. I know how to create a table and insert the fields. I just need help understanding how to import from the products.dat file. It appears to be tab delimited with each row ending with a line break.

Does this help?

[jatar_k]

well fgetcsv is a great function for importing csv files just set the delimiter

array fgetcsv ( resource handle, int length [, string delimiter [, string enclosure]])

fgetcsv($fp,10000,"\t")

[dvduval]

Thanks jatar
I'll see if I can fill in the blanks in the fgetcsv parameters and start importing.

[jatar_k]

you could even do a find and replace for all of the tabs in the file with [insert text editor name here] and swap them for some other delimiter if you have trouble, I know I've had to do that enough times.

[Robber]

Just the other day had to do something like this, first headed to excel to strip out the crap then used:

LOAD DATA INFILE 'c:/blah/blah.txt' INTO TABLE xyz IGNORE 1 LINES

Worked a treat

(should say that was for tab delimited though, so in excel I just changed it from csv to tab delimited - saved messing about!)

[jatar_k]

That's a great thought Robber, I was thinking php instead of mysql. LOAD DATA [mysql.com] has a very versatile syntax as well.

[dvduval]

I take it I could use LOAD FILE within a PHP file to be used as my importer? Or would it work best to do it manually through mySQL?

I'm glad you demonstrated the LOAD FILE syntax. I had looked at it before, but didn't know how to write the syntax.

Thank you

[dvduval]

Using Excel 2002, when I try to save the .dat file to a text file (tab delimited), it adds quotes into some of the fields. This has been the source of many problems for me. I can't figure out how to get Excel to stop doing that!

When I try to use LOAD DATA INFIELD in phpMyAdmin I get an access denied for user 'me@localhost'.

Once again, I'm stumped.

[Robber]

Not sure about the quotes - I havent had that problem, but some potential solutions could be to take a look at the data type format of the cells, also I think there may be some options for LOAD regarding quotes so you might be able to solve it there.

On your other question re using LOAD in a php file, thats exactly what I did - it was basically a php sql query analyser, works fine.

[BCMG_Scott]

In more recent versions of 3.23 and 4.0 of MySQL the LOAD DATA command is not enabled by default. You must specify --enable-local-infile when compiling mysql or you cannot use that option. If you (or the server admin) has not done this you will get an error when you try to load.

Scott Geiger

[Morgan]

I use LOAD DATA INFILE a lot with Excel, it works great, but it does have to be enabled to work. I'll look for some code to help you with it, but I remember that the

OPTIONALLY ENCLOSED BY '"'

worked for me. If it doesn't and you just want to get rid of the added quotes, you do a str_replace and turn \t" and "\t, and /n" and "/n into just \t, that way the only quotes left are the ones within fields.

Hope this makes sense.

[zeb]

Not so long ago I created a database of more than 12000 products. I used the "Insert data from a textfile into table" feature in phpmyadmin. This feature lets you choose the deliminator, in my case it was ,,. You can find this button as last row in the table-frame.

I second what bonanza wrote, with phpmyadmin this is "too easy"!

[crankin]

There's a simple tool available to format text flatfiles to SQL, called "Twang". You can get it from tools2test.com, only costs a few bucks. I use it a lot, saves me all kinds of headaches, puts all the right wrappers on the data.

cheers,

Chris

[Islander]

Perl is a great swiss army knife for one-time data manipulation of this kind. Any competent Perl hacker could write a script to read in the flat file and write out a SQL script with INSERT statements tailored to your database.