RE: Bulk Copy / BCP problem....please advise

From: Scott (anonymous_at_discussions.microsoft.com)
Date: 09/24/04 

Date: Fri, 24 Sep 2004 14:07:53 -0700

Thanks for responding,

     I could do that, but would much rather do it with
SQL. Actually I might be more inclined to import every
row as one big field and them split it out with T-SQL.

Anyone else have any other ideas?

>-----Original Message-----
>You can use vb6, vb.net, c#, etc. to open the text file
and then using ADO or
>ADO.NET connect to your db and insert the rows. I know
this solution is
>slower than using bcp or bulk insert but it could help.
>
>
>
>AMB
>
>"Scott" wrote:
>
>> I have a text file that I would like to import on a
>> regular basis into my SQL Server using either Bulk
Copy /
>> BCP. The problem I have is that the number of fields
in
>> the text file varies per row. The maxiumum number of
>> fields that a row will contain is 26. If I edit the
file
>> so that each row has the same number of fields, then
the
>> import works great, but I cannot do this on a regular
>> basis. I have tried using a format file, but with no
>> luck.
>>
>> Is there a trick to having a text file with a varying
>> number of fields per row automatically imported? I
even
>> tried DTS, but could not get the "transform data task"
or
>> the "bulk insert task" to work there.
>>
>> My only solution thus far is to have bulk insert/bcp
>> import into the minimum number of fields (3) and then
try
>> and use SQL to split them out later (if the row has
more
>> than three fields, then the rest of the fields for that
>> row are run together in the 3rd field).
>>
>> HELP!!!
>>
>> Thank you.
>>
>.
>

Relevant Pages

  • Re: can not bulk insert a network file onto a remote SQL server 2005
    ... SQL 2000 DTS can import the same file with no ... Bulk Insert task and when using the Import Column in a data flow task. ... There are clearly multiple people out here fighting it. ... > I get this error when run from Query analyser in remote machine ...
    (microsoft.public.sqlserver.dts)
  • Re: C2 Audit and BCP
    ... I'd like to be able to identify that an "insert bulk" statement has ... All of the SQL Statements that are run are identified solely by the ... events but nothing legible in the text data column. ... This isn't an issue with the functionality of BCP, ...
    (microsoft.public.sqlserver.security)
  • Re: Use a sequence to bulk collect into a collection?
    ... Patrick Demets wrote: ... And this cursor returns one record. ... FETCH ew_eqmt_asgn_csr BULK COLLECT INTO ... SQL> create sequence testseq; ...
    (comp.databases.oracle.server)
  • Re: SqlXml Bulk Upload Lookups
    ... You may have to break things up - or do the bulk load into staging ... tables and then do INSERT/UPDATE via SQL. ... SQL Server MVP ... Tom, a slight irritation, which i suspected may happen is that items with ...
    (microsoft.public.sqlserver.xml)
  • Re: Bulkinsert Problem
    ... An alternative approach may me to use the SQL Server Bulk Copy Command ... For more details refer to the "bcp Utility" article in SQL ...
    (microsoft.public.sqlserver.programming)