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

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

Date: Fri, 24 Sep 2004 14:51:23 -0700

Yea, Bulk Copy and BCP you have to specify a static
destination and pretty much an exact match unless you use
a format file. But if it changes you're hosed.

Here's something I've done before. Use the Text ODBC
driver with either OpenRowset or OpenQuery and load into
a temp table first. OpenQuery you have to build a linked
server. OpenRowset you can specify the DSNless ODBC Text
Driver connect string right in your TSQL, but
theoretically is a little slower as the server has to
parse/confirm the connection string each time rather than
a DSN/Linked Server. Look at the help for both and
you'll see what I'm talking about.

Anyway, in the T-SQL procedure you setup to import the
file...

Select *
Into #Temp
>From OpenRowset(<OCBC; Connect String>,
  'Select * From C:\TextFile.txt')

The text file will be loaded into an automatically
created temp table regardless of the varying number of
columns. (I think they all will default to text fields
usually when you do this.) From there you can add
additional steps to pull out the columns you need and do
whatever else you need.

One thing that may bite you is the field/row seperators
in your file. I believe you can specify tab, comma,
etc... in the text odbc connect string. Play around and
see.

Even if the file has seperators that text ODBC won't
accept you still aren't out of luck. Import the file
into a single field table and run an update replacing
the "bad" sepeators with comma or tab, then bcp it back
out to a file, then reload the new file with the sql
above and text odbc. Ugly and slow, but you can
massage/load the file all within T-SQL without any
external SQL steps or nasty T-SQL parsing.

I've even done this in conjunction with an automated FTP
transfer and load from T-SQL. I made a one field table
with automated FTP commands and BCPed it out to a file,
then ran xp_cmdshell and called FTP with the command line
options to use the BDP'd FTP config file for the steps.
It copied the file then loaded it. Probably the nastest
T-SQL I've ever thrown together but it works great.

Good Luck!

Relevant Pages

  • Re: Beginner: How do I link via ODBC from a PC into an AS400?
    ... I would like some basic help to set up a ODBC link across the network ... Install the IBM ODBC driver for the AS400 on every PC that needs to connect. ... those cases you will need to specify the fields that Access can use to specify a ... passthrough queries as inputs will not be efficient. ...
    (microsoft.public.access.externaldata)
  • ODBC timestamp formats in T-SQL
    ... This works in SQL 2005 T-SQL: ... I understand that this is known as the "ODBC timestamp literal format". ...
    (microsoft.public.sqlserver.programming)
  • Re: ISA2004 - logging to msaccess database via odbc system dsn
    ... >> Access database. ... >> the field definitions so that they match up, but everytime i specify the ... >> logging options to log to an SQL database, ... >> The ISA Server Web filter was unable to open ODBC Data Source ProxyLog, ...
    (microsoft.public.isa)
  • Re: newer versions of Word take longer to merge
    ... Thanks Cindy. ... to specify a DSN, ... >Are you absolutely certain this is an ODBC connection, ... or selecting the DSN entry ...
    (microsoft.public.word.mailmerge.fields)
  • Re: Access SQL dialect documentation?"
    ... > there a way to invoke this SQL Server Compatible Syntax through ODBC? ... > Since I'm much more familiar with SQL Server (T-SQL), ... I believe the compliance has to be invoked by Access ... matter - T-SQL having many enhancements over SQL92 & JET-SQL). ...
    (microsoft.public.access.queries)