| 8:27 pm on May 21, 2003 (gmt 0)|
If it is a comma delimited file check this out
| 8:33 pm on May 21, 2003 (gmt 0)|
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...]
| 9:15 pm on May 21, 2003 (gmt 0)|
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?
| 9:19 pm on May 21, 2003 (gmt 0)|
well fgetcsv is a great function for importing csv files just set the delimiter
array fgetcsv ( resource handle, int length [, string delimiter [, string enclosure]])
| 9:37 pm on May 21, 2003 (gmt 0)|
I'll see if I can fill in the blanks in the fgetcsv parameters and start importing.
| 9:39 pm on May 21, 2003 (gmt 0)|
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.
| 10:22 am on May 22, 2003 (gmt 0)|
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!)
| 4:15 pm on May 22, 2003 (gmt 0)|
That's a great thought Robber, I was thinking php instead of mysql. LOAD DATA [mysql.com] has a very versatile syntax as well.
| 4:19 pm on May 22, 2003 (gmt 0)|
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.
| 1:46 am on May 23, 2003 (gmt 0)|
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.
| 9:17 am on May 23, 2003 (gmt 0)|
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.
| 1:27 pm on May 23, 2003 (gmt 0)|
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.
| 5:08 am on May 24, 2003 (gmt 0)|
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.
| 5:58 am on May 24, 2003 (gmt 0)|
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"!
| 1:56 pm on May 24, 2003 (gmt 0)|
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.
| 11:12 am on May 27, 2003 (gmt 0)|
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.