importing space delimited text file

[Database Administrator]

i've been looking on this subject in this group with no luck.

i've been trying to import a text file which has multiple space
delimited column to a SQL server 2000.

i can't seem to find any answer on this subject. i tried typing space
on the delimiter field, but i get
"error source: microsoft data transformation services flat file rowset
provider

error description: invalid delimited data: text qualifier must be
followed by a column delimiter(except the last column).

context: error calling openrowset on the provider"

could anyone help?

[Database Administrator]

Are you sure that you are not referring to 'fixed width' fields?

--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc

Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous

You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf


"jesbuddy07" wrote in message
news:1165431841.876553.93600@16g2000cwy.googlegrou ps.com...
> i've been looking on this subject in this group with no luck.
>
> i've been trying to import a text file which has multiple space
> delimited column to a SQL server 2000.
>
> i can't seem to find any answer on this subject. i tried typing space
> on the delimiter field, but i get
> "error source: microsoft data transformation services flat file rowset
> provider
>
> error description: invalid delimited data: text qualifier must be
> followed by a column delimiter(except the last column).
>
> context: error calling openrowset on the provider"
>
> could anyone help?
>

[Database Administrator]

yes i'm sure.
i need to use delimited because the width of the columns are not fixed
for every column.
they are delimited by space.


Arnie Rowland wrote:
> Are you sure that you are not referring to 'fixed width' fields?
>
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
>
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> You can't help someone get up a hill without getting a little closer to the
> top yourself.
> - H. Norman Schwarzkopf
>
>
> "jesbuddy07" wrote in message
> news:1165431841.876553.93600@16g2000cwy.googlegrou ps.com...
> > i've been looking on this subject in this group with no luck.
> >
> > i've been trying to import a text file which has multiple space
> > delimited column to a SQL server 2000.
> >
> > i can't seem to find any answer on this subject. i tried typing space
> > on the delimiter field, but i get
> > "error source: microsoft data transformation services flat file rowset
> > provider
> >
> > error description: invalid delimited data: text qualifier must be
> > followed by a column delimiter(except the last column).
> >
> > context: error calling openrowset on the provider"
> >
> > could anyone help?
> >

[Database Administrator]

Hello jesbuddy07,


What does your file look like I have a file that looks like

A B C D

I typed a space as the column delimiters and it worked.


Regards

Allan Mitchell
Konesans Ltd
T +44 7966 476 572
F +44 2071 008 479
http://www.konesans.com

> i've been looking on this subject in this group with no luck.
>
> i've been trying to import a text file which has multiple space
> delimited column to a SQL server 2000.
>
> i can't seem to find any answer on this subject. i tried typing space
> on the delimiter field, but i get
> "error source: microsoft data transformation services flat file rowset
> provider
> error description: invalid delimited data: text qualifier must be
> followed by a column delimiter(except the last column).
>
> context: error calling openrowset on the provider"
>
> could anyone help?
>

[Database Administrator]

i'm trying to parse a log file
66.249.65.80 - - [04/Dec/2006:00:00:03 -0500] "GET
/cdm4/document.php?CISOROOT=/LSTACivWar&CISOPTR=1975&CISOSHOW=1973&REC=20
HTTP/1.1" 200 35843

i thought it would work if i have a double quote(") as a text
qualifier, but it wouldn't. that's how i get the error i mentioned
above, but after removing the text qualifier, it works just well.

thank you!!

Allan Mitchell wrote:
> Hello jesbuddy07,
>
>
> What does your file look like I have a file that looks like
>
> A B C D
>
> I typed a space as the column delimiters and it worked.
>
>
> Regards
>
> Allan Mitchell
> Konesans Ltd
> T +44 7966 476 572
> F +44 2071 008 479
> http://www.konesans.com
>
> > i've been looking on this subject in this group with no luck.
> >
> > i've been trying to import a text file which has multiple space
> > delimited column to a SQL server 2000.
> >
> > i can't seem to find any answer on this subject. i tried typing space
> > on the delimiter field, but i get
> > "error source: microsoft data transformation services flat file rowset
> > provider
> > error description: invalid delimited data: text qualifier must be
> > followed by a column delimiter(except the last column).
> >
> > context: error calling openrowset on the provider"
> >
> > could anyone help?
> >

[Database Administrator]

i'm trying to parse a log file
IP.IP.IP.IP - - [04/Dec/2006:00:00:03 -0500] "GET
/yyy/document.php?YYY=/LSTACivWar&VVV=1975&VVV=1973&REC=20
HTTP/1.1" 200 35843

i thought it would work if i have a double quote(") as a text
qualifier, but it wouldn't. that's how i get the error i mentioned
above, but after removing the text qualifier, it works just well.


thank you!!



Allan Mitchell wrote:
> Hello jesbuddy07,
>
>
> What does your file look like I have a file that looks like
>
> A B C D
>
> I typed a space as the column delimiters and it worked.
>
>
> Regards
>
> Allan Mitchell
> Konesans Ltd
> T +44 7966 476 572
> F +44 2071 008 479
> http://www.konesans.com
>
> > i've been looking on this subject in this group with no luck.
> >
> > i've been trying to import a text file which has multiple space
> > delimited column to a SQL server 2000.
> >
> > i can't seem to find any answer on this subject. i tried typing space
> > on the delimiter field, but i get
> > "error source: microsoft data transformation services flat file rowset
> > provider
> > error description: invalid delimited data: text qualifier must be
> > followed by a column delimiter(except the last column).
> >
> > context: error calling openrowset on the provider"
> >
> > could anyone help?
> >

[Database Administrator]

Hello jesbuddy07,


That's all one line right?

I have just created a Source adapter which based on " being the text qualifier
and space being the column delimiter gets me 7 columns


Regards

Allan Mitchell
Konesans Ltd
T +44 7966 476 572
F +44 2071 008 479
http://www.konesans.com

> i'm trying to parse a log file
> IP.IP.IP.IP - - [04/Dec/2006:00:00:03 -0500] "GET
> /yyy/document.php?YYY=/LSTACivWar&VVV=1975&VVV=1973&REC=20
> HTTP/1.1" 200 35843
> i thought it would work if i have a double quote(") as a text
> qualifier, but it wouldn't. that's how i get the error i mentioned
> above, but after removing the text qualifier, it works just well.
>
> thank you!!
>
> Allan Mitchell wrote:
>
>> Hello jesbuddy07,
>>
>> What does your file look like I have a file that looks like
>>
>> A B C D
>>
>> I typed a space as the column delimiters and it worked.
>>
>> Regards
>>
>> Allan Mitchell
>> Konesans Ltd
>> T +44 7966 476 572
>> F +44 2071 008 479
>> http://www.konesans.com
>>> i've been looking on this subject in this group with no luck.
>>>
>>> i've been trying to import a text file which has multiple space
>>> delimited column to a SQL server 2000.
>>>
>>> i can't seem to find any answer on this subject. i tried typing
>>> space
>>> on the delimiter field, but i get
>>> "error source: microsoft data transformation services flat file
>>> rowset
>>> provider
>>> error description: invalid delimited data: text qualifier must be
>>> followed by a column delimiter(except the last column).
>>> context: error calling openrowset on the provider"
>>>
>>> could anyone help?
>>>

[PatrickMc]

You may want to take a look at this script - http://www.biterscripting.com/SS_WebLogParser.html . It is a sample script that processes a log file exactly similar to yours.

That script uses the space for delimeter, and is able to handle the double quoted fields.