homepage Welcome to WebmasterWorld Guest from 54.211.219.178
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member

Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
Forum Library, Charter, Moderators: coopster & jatar k

PHP Server Side Scripting Forum

    
Importing Flat File into mySQL
dvduval




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

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




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

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

bonanza




msg:1270110
 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...]

dvduval




msg:1270111
 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?

jatar_k




msg:1270112
 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]])

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

dvduval




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

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

jatar_k




msg:1270114
 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.

Robber




msg:1270115
 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!)

jatar_k




msg:1270116
 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.

dvduval




msg:1270117
 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.

Thank you

dvduval




msg:1270118
 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.

Robber




msg:1270119
 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.

BCMG_Scott




msg:1270120
 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.

Scott Geiger

Morgan




msg:1270121
 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.

zeb




msg:1270122
 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"!

crankin




msg:1270123
 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.

cheers,

Chris

Islander




msg:1270124
 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.

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved