Load Infile Help

[PHP_apprentice]

We are recieving a file from a client that needs to be stripped apart and read into a DB. WE have everything working well with most of the files however one specific format uses commas within the field values themselves. Does anyone out there no of a solution or have you seen thi8s before? In any event any help id useful.

Thanks

below is a sample quote from one of the files it contains 2 comma delimited fields the problem is like the date below

,"75009-3113","January 1, 2004",

[natsand]

I have not done this, but it seems like it could work.

If all of the fields are actually delimmeted by "," rather than just a comma (as it looks like from your sample), you could use the php function substr_replace to substitute another delimeter that is not likely to exist within the fields themselves (";", for example, or ";;").

Then when you use LOAD DATA INFILE you could specify the new delimeter using the optional FIELDS ENCLOSED BY parameter, setting the value to your new delimeter.

All of the information for these two steps is available in the PHP and MySQL documentations on their respective sites. Since I haven't done this, I don't have an example to give you.

Hope this works/helps.

[wdglide]

I had the exact same problem. If its a one time thing, I suggest using phpMyAdmin's tool.

Then do some prep work on the text file. Find/replace

","

with

"#"

Then use # as the field delimiter. Just make sure that "," does not appear anywhere other than in cases where it is a delimiter.

Test it by importing it into Excel.