Welcome to WebmasterWorld Guest from 50.16.78.128

Forum Moderators: coopster & jatar k

Message Too Old, No Replies

Importing Flat File into mySQL

   
8:24 pm on May 21, 2003 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member



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.?

8:27 pm on May 21, 2003 (gmt 0)

WebmasterWorld Administrator jatar_k is a WebmasterWorld Top Contributor of All Time 10+ Year Member



If it is a comma delimited file check this out
fgetcsv [php.net]
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)

WebmasterWorld Senior Member 10+ Year Member



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)

WebmasterWorld Administrator jatar_k is a WebmasterWorld Top Contributor of All Time 10+ Year Member



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")

9:37 pm on May 21, 2003 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member



Thanks jatar
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)

WebmasterWorld Administrator jatar_k is a WebmasterWorld Top Contributor of All Time 10+ Year Member



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)

10+ Year Member



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)

WebmasterWorld Administrator jatar_k is a WebmasterWorld Top Contributor of All Time 10+ Year Member



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)

WebmasterWorld Senior Member 10+ Year Member



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

1:46 am on May 23, 2003 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member



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)

10+ Year Member



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)

10+ Year Member



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

5:08 am on May 24, 2003 (gmt 0)

10+ Year Member



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

5:58 am on May 24, 2003 (gmt 0)

10+ Year Member



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)

10+ Year Member



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

11:12 am on May 27, 2003 (gmt 0)

10+ Year Member



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.
 

Featured Threads

Hot Threads This Week

Hot Threads This Month