On Aug 1, 4:51 am, Jerry Stuckle <jstuck...@attglobal.net> wrote:
> lyonnytewrote:
> > On Jul 31, 11:52 am, Jerry Stuckle <jstuck...@attglobal.net> wrote:
> >> Peter H. Coffin wrote:
> >>> On Tue, 31 Jul 2007 12:56:50 -0400, Jerry Stuckle wrote:
> >>>>lyonnytewrote:
> >>>>> Is there a way to import using LOAD DATA INFILE when the file is non-
> >>>>> delineated?
> >>>> How do you know where one field ends and another one begins?
> >>> Often, in general, this kind of thing is done by column position. Which
> >>> makes the answer, I believe, no.
> >> Yes, that's a possibility (and one I'm surprised MySQL doesn't support).
> >> But are there others?
>
> >> For instance, I've seen cases where you have a mix of numbers and text.
> >> Numbers are separated by blanks, while text is enclosed in quotes.
> >> Similar to CSV, except there is no comma between the values. What a
> >> pain to parse :-)
>
> >> --
> >> ==================
> >> Remove the "x" from my email address
> >> Jerry Stuckle
> >> JDS Computer Training Corp.
> >> jstuck...@attglobal.net
> >> ==================
>
> > Running into the same problem as Jerry, it's a mix of numbers and
> > text. Let's say the file contains these 2 records.
>
> > "John Doe" 38 "123 Spooner Street" 126-67-8888 "Jane Doe" 36 "124
> > Spooner Street" 121-52-9982
>
> Yep, this makes it more difficult.
>
> Can you ever have embedded quote marks? If not, your work is much
> easier - you can use a regex to do it.
>
> If you may have embedded quotes, how are they identified? Are they
> doubled (i.e. ""), or escaped (i.e. \")?
>
> If they're doubled (like mine were) it's a bit harder. I wasn't able to
> come up with a decent regex to handle it, but then I'm not a regex
> expert, either. I ended up writing a short PHP program which parsed the
> data, and since my files were small (a few thousand rows max), I just
> inserted them right then. But if the files are larger, you could write
> it out in CSV format and use LOAD DATA INFILE.
>
> --
> ==================
> Remove the "x" from my email address
> Jerry Stuckle
> JDS Computer Training Corp.
> jstuck...@attglobal.net
> ==================- Hide quoted text -
>
> - Show quoted text -

Thanks for the suggestions everyone. I ended up creating new columns
and then creating space as the terminator.
So it looks like this:
FName LName Age StreetNo StreetName1 StreetName2 Social

Luckily I didn't have apartment numbers and such