Importing Comma Delimited Data.

Hello,
I am trying to import files (hundreds) with Comma Delimited Data, because
some columns within the data contain comma's themselves those fields have
been double qouted.
Small example.
1234, hello John, mega
2345, " Hello Andrew, Marie", tall
I have tried to use Bulk insert with a Formatfile in xml and suggested by
Uri Dimant a formatted file.
But with the bulk insert, the field with the comma breaks into two sepperate
fields and this generetes
sometimes an error because the fields are not in sync.
With the formatted file the lines without quotes do not work correctly. I
tried to read the help files on this, but could not find a solution to the
problem.
In total it's about millions of rows, in hundreds of files, for a number off
tables.
Can anybody give advise how to solve this, or where to look in the help
system?
Thanks for your time and attention,
Ben Brugman> Can anybody give advise how to solve this, or where to look in the help
> system?
SSIS will better handle enclosed fields. You can use the import data wizard
from SQL Server Management Studio to generate the package. Right-click the
database in object explorer and select Tasks-->Import Data.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"ben brugman" <ben@.niethier.nl> wrote in message
news:32e3c$473aef81$53557893$24038@.cache100.multikabel.net...
> Hello,
> I am trying to import files (hundreds) with Comma Delimited Data, because
> some columns within the data contain comma's themselves those fields have
> been double qouted.
> Small example.
> 1234, hello John, mega
> 2345, " Hello Andrew, Marie", tall
> I have tried to use Bulk insert with a Formatfile in xml and suggested by
> Uri Dimant a formatted file.
> But with the bulk insert, the field with the comma breaks into two
> sepperate fields and this generetes
> sometimes an error because the fields are not in sync.
> With the formatted file the lines without quotes do not work correctly. I
> tried to read the help files on this, but could not find a solution to the
> problem.
> In total it's about millions of rows, in hundreds of files, for a number
> off tables.
> Can anybody give advise how to solve this, or where to look in the help
> system?
> Thanks for your time and attention,
> Ben Brugman
>|||Thanks Dan,
At the moment the target system does not have SSIS installed.
(I'll will ask for SSIS to be installed on the target system.)
Two questions:
Is there another solution, because I should think that normal import
procedures like bulk insert should be able to handle Comma delimited files?
(To my knowledge my situation is fairly common).
Because I allready tried two differend methods, which I could not get to
work I am a bit apprihencive to try a third method, is SSIS going to work
for this kind of situations, or is this something I just have to try?
This is actually my first project really working with SQLserver 2005. With
the previous version I normally succeeded with importing everything I wanted
to import, so at the moment I am feeling a bit stupid. Spending time on the
methods I could not get to work is rather frustrating,
Thanks for your attention and advise.
Ben Brugman
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> schreef in bericht
news:%2369Uz5rJIHA.280@.TK2MSFTNGP03.phx.gbl...
>> Can anybody give advise how to solve this, or where to look in the help
>> system?
> SSIS will better handle enclosed fields. You can use the import data
> wizard from SQL Server Management Studio to generate the package.
> Right-click the database in object explorer and select Tasks-->Import
> Data.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "ben brugman" <ben@.niethier.nl> wrote in message
> news:32e3c$473aef81$53557893$24038@.cache100.multikabel.net...
>> Hello,
>> I am trying to import files (hundreds) with Comma Delimited Data, because
>> some columns within the data contain comma's themselves those fields have
>> been double qouted.
>> Small example.
>> 1234, hello John, mega
>> 2345, " Hello Andrew, Marie", tall
>> I have tried to use Bulk insert with a Formatfile in xml and suggested by
>> Uri Dimant a formatted file.
>> But with the bulk insert, the field with the comma breaks into two
>> sepperate fields and this generetes
>> sometimes an error because the fields are not in sync.
>> With the formatted file the lines without quotes do not work correctly. I
>> tried to read the help files on this, but could not find a solution to
>> the problem.
>> In total it's about millions of rows, in hundreds of files, for a number
>> off tables.
>> Can anybody give advise how to solve this, or where to look in the help
>> system?
>> Thanks for your time and attention,
>> Ben Brugman
>|||> Is there another solution, because I should think that normal import
> procedures like bulk insert should be able to handle Comma delimited
> files?
BCP and BULK INSERT do a good job of parsing files as long as field
terminators do not occur in the data. For more advanced parsing, SSIS is a
better choice.
> Because I allready tried two differend methods, which I could not get to
> work I am a bit apprihencive to try a third method, is SSIS going to work
> for this kind of situations, or is this something I just have to try?
I understand your frustration. However, I can tell you that I've
successfully used SSIS to import many CSV files in a loop using a ForEach
task that executes a DataFlow for each file. There is some learning curve
if you've never used SSIS before so you might find an examples helpful. An
example of this technique at http://www.sqlis.com/55.aspx. Just be sure to
specify quotes (") as the text qualifier for the flat file connection of the
data flow task.
Hope this helps.
Dan Guzman
SQL Server MVP
"ben brugman" <ben@.niethier.nl> wrote in message
news:8b4e8$473af645$53557893$23893@.cache120.multikabel.net...
> Thanks Dan,
> At the moment the target system does not have SSIS installed.
> (I'll will ask for SSIS to be installed on the target system.)
> Two questions:
> Is there another solution, because I should think that normal import
> procedures like bulk insert should be able to handle Comma delimited
> files?
> (To my knowledge my situation is fairly common).
> Because I allready tried two differend methods, which I could not get to
> work I am a bit apprihencive to try a third method, is SSIS going to work
> for this kind of situations, or is this something I just have to try?
> This is actually my first project really working with SQLserver 2005. With
> the previous version I normally succeeded with importing everything I
> wanted to import, so at the moment I am feeling a bit stupid. Spending
> time on the methods I could not get to work is rather frustrating,
> Thanks for your attention and advise.
> Ben Brugman
>
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> schreef in bericht
> news:%2369Uz5rJIHA.280@.TK2MSFTNGP03.phx.gbl...
>> Can anybody give advise how to solve this, or where to look in the help
>> system?
>> SSIS will better handle enclosed fields. You can use the import data
>> wizard from SQL Server Management Studio to generate the package.
>> Right-click the database in object explorer and select Tasks-->Import
>> Data.
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "ben brugman" <ben@.niethier.nl> wrote in message
>> news:32e3c$473aef81$53557893$24038@.cache100.multikabel.net...
>> Hello,
>> I am trying to import files (hundreds) with Comma Delimited Data,
>> because some columns within the data contain comma's themselves those
>> fields have been double qouted.
>> Small example.
>> 1234, hello John, mega
>> 2345, " Hello Andrew, Marie", tall
>> I have tried to use Bulk insert with a Formatfile in xml and suggested
>> by Uri Dimant a formatted file.
>> But with the bulk insert, the field with the comma breaks into two
>> sepperate fields and this generetes
>> sometimes an error because the fields are not in sync.
>> With the formatted file the lines without quotes do not work correctly.
>> I tried to read the help files on this, but could not find a solution to
>> the problem.
>> In total it's about millions of rows, in hundreds of files, for a number
>> off tables.
>> Can anybody give advise how to solve this, or where to look in the help
>> system?
>> Thanks for your time and attention,
>> Ben Brugman
>>
>|||BULK INSERT and BCP are designed for speed not versatility in handling
different formats. If you don't want to try SSIS, another solution I often
use is to pre-process the files into an even simpler format before feeding
them to BCP/BULK INSERT.
Linchi
"ben brugman" wrote:
> Thanks Dan,
> At the moment the target system does not have SSIS installed.
> (I'll will ask for SSIS to be installed on the target system.)
> Two questions:
> Is there another solution, because I should think that normal import
> procedures like bulk insert should be able to handle Comma delimited files?
> (To my knowledge my situation is fairly common).
> Because I allready tried two differend methods, which I could not get to
> work I am a bit apprihencive to try a third method, is SSIS going to work
> for this kind of situations, or is this something I just have to try?
> This is actually my first project really working with SQLserver 2005. With
> the previous version I normally succeeded with importing everything I wanted
> to import, so at the moment I am feeling a bit stupid. Spending time on the
> methods I could not get to work is rather frustrating,
> Thanks for your attention and advise.
> Ben Brugman
>
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> schreef in bericht
> news:%2369Uz5rJIHA.280@.TK2MSFTNGP03.phx.gbl...
> >> Can anybody give advise how to solve this, or where to look in the help
> >> system?
> >
> > SSIS will better handle enclosed fields. You can use the import data
> > wizard from SQL Server Management Studio to generate the package.
> > Right-click the database in object explorer and select Tasks-->Import
> > Data.
> >
> > --
> > Hope this helps.
> >
> > Dan Guzman
> > SQL Server MVP
> >
> > "ben brugman" <ben@.niethier.nl> wrote in message
> > news:32e3c$473aef81$53557893$24038@.cache100.multikabel.net...
> >> Hello,
> >>
> >> I am trying to import files (hundreds) with Comma Delimited Data, because
> >> some columns within the data contain comma's themselves those fields have
> >> been double qouted.
> >>
> >> Small example.
> >>
> >> 1234, hello John, mega
> >> 2345, " Hello Andrew, Marie", tall
> >>
> >> I have tried to use Bulk insert with a Formatfile in xml and suggested by
> >> Uri Dimant a formatted file.
> >> But with the bulk insert, the field with the comma breaks into two
> >> sepperate fields and this generetes
> >> sometimes an error because the fields are not in sync.
> >>
> >> With the formatted file the lines without quotes do not work correctly. I
> >> tried to read the help files on this, but could not find a solution to
> >> the problem.
> >>
> >> In total it's about millions of rows, in hundreds of files, for a number
> >> off tables.
> >>
> >> Can anybody give advise how to solve this, or where to look in the help
> >> system?
> >>
> >> Thanks for your time and attention,
> >> Ben Brugman
> >>
> >
>|||All thanks for you participation,
Dan and Linchi, thanks for your comments.
At the moment we have adopted the solution of dropping the column which was
causing all the problems.
When we do need the column, we will probably end it with a not used symbol
or set of symbols, or quoting all strings for this column.
Thanks for you time and attention,
Ben
"Linchi Shea" <LinchiShea@.discussions.microsoft.com> schreef in bericht
news:270A3E1B-6300-43F0-B30C-C27B8291912C@.microsoft.com...
> BULK INSERT and BCP are designed for speed not versatility in handling
> different formats. If you don't want to try SSIS, another solution I often
> use is to pre-process the files into an even simpler format before feeding
> them to BCP/BULK INSERT.
> Linchi
> "ben brugman" wrote:
>> Thanks Dan,
>> At the moment the target system does not have SSIS installed.
>> (I'll will ask for SSIS to be installed on the target system.)
>> Two questions:
>> Is there another solution, because I should think that normal import
>> procedures like bulk insert should be able to handle Comma delimited
>> files?
>> (To my knowledge my situation is fairly common).
>> Because I allready tried two differend methods, which I could not get to
>> work I am a bit apprihencive to try a third method, is SSIS going to work
>> for this kind of situations, or is this something I just have to try?
>> This is actually my first project really working with SQLserver 2005.
>> With
>> the previous version I normally succeeded with importing everything I
>> wanted
>> to import, so at the moment I am feeling a bit stupid. Spending time on
>> the
>> methods I could not get to work is rather frustrating,
>> Thanks for your attention and advise.
>> Ben Brugman
>>
>> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> schreef in bericht
>> news:%2369Uz5rJIHA.280@.TK2MSFTNGP03.phx.gbl...
>> >> Can anybody give advise how to solve this, or where to look in the
>> >> help
>> >> system?
>> >
>> > SSIS will better handle enclosed fields. You can use the import data
>> > wizard from SQL Server Management Studio to generate the package.
>> > Right-click the database in object explorer and select Tasks-->Import
>> > Data.
>> >
>> > --
>> > Hope this helps.
>> >
>> > Dan Guzman
>> > SQL Server MVP
>> >
>> > "ben brugman" <ben@.niethier.nl> wrote in message
>> > news:32e3c$473aef81$53557893$24038@.cache100.multikabel.net...
>> >> Hello,
>> >>
>> >> I am trying to import files (hundreds) with Comma Delimited Data,
>> >> because
>> >> some columns within the data contain comma's themselves those fields
>> >> have
>> >> been double qouted.
>> >>
>> >> Small example.
>> >>
>> >> 1234, hello John, mega
>> >> 2345, " Hello Andrew, Marie", tall
>> >>
>> >> I have tried to use Bulk insert with a Formatfile in xml and suggested
>> >> by
>> >> Uri Dimant a formatted file.
>> >> But with the bulk insert, the field with the comma breaks into two
>> >> sepperate fields and this generetes
>> >> sometimes an error because the fields are not in sync.
>> >>
>> >> With the formatted file the lines without quotes do not work
>> >> correctly. I
>> >> tried to read the help files on this, but could not find a solution to
>> >> the problem.
>> >>
>> >> In total it's about millions of rows, in hundreds of files, for a
>> >> number
>> >> off tables.
>> >>
>> >> Can anybody give advise how to solve this, or where to look in the
>> >> help
>> >> system?
>> >>
>> >> Thanks for your time and attention,
>> >> Ben Brugman
>> >>
>> >
>>