SEO

Is there a way to import using LOAD DATA INFILE when the file is non-
delineated?

lyonnyte wrote:
> 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?

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

On Tue, 31 Jul 2007 12:56:50 -0400, Jerry Stuckle wrote:
> lyonnyte wrote:
>> 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.

--
6. I will not gloat over my enemies' predicament before killing them.
--Peter Anspach's list of things to do as an Evil Overlord

Peter H. Coffin wrote:
> On Tue, 31 Jul 2007 12:56:50 -0400, Jerry Stuckle wrote:
>> lyonnyte wrote:
>>> 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.
jstucklex@attglobal.net
==================

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:
> >> lyonnyte wrote:
> >>> 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

On Wed, 01 Aug 2007 04:35:33 -0000, lyonnyte <lyonnyte@gmail.com>
wrote:

>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

Assuming your file doesn't have any missing or empty field...

I would put it in emacs, launch a regexp-replace to make it change
line at the end of each row.

You replace this :

\("[^"]*"\|[^ ]+\) \("[^"]*"\|[^ ]+\) \("[^"]*"\|[^ ]+\)
\("[^"]*"\|[^]+\)

(it is 4 times : \("[^"]*"\|[^]+\)
followed each time by a space, even the last one)

[[[ If the pattern is *always* : "text" num "text" num
then you have more efficiently

\("[^"]*"\) \([^ ]+\) \("[^"]*"\) \([^ ]+\)

(with a space character at the end...)]]]

for that
\1\t\2\t\3\t\4\n

And then you can delete all the " characters, because your values are
separated by tabs, and LOAD DATA INFILE with a tab separator.

lyonnyte wrote:
> 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:
>>>> lyonnyte wrote:
>>>>> 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.
jstucklex@attglobal.net
==================

On Tue, 31 Jul 2007 14:52:21 -0400, Jerry Stuckle wrote:
> Peter H. Coffin wrote:
>> On Tue, 31 Jul 2007 12:56:50 -0400, Jerry Stuckle wrote:
>>> lyonnyte wrote:
>>>> 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 :-)
>

.... FIELDS TERMINATED BY '\ ' ENCLOSED BY '\"' ESCAPED BY '\\' ...

That one's *dumb*, but not horrible... (;

--
6. I will not gloat over my enemies' predicament before killing them.
--Peter Anspach's list of things to do as an Evil Overlord

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