"Accesshelp" <Accessh***@discussions.microsoft.com> wrote in message
news:8680B4EE-D16D-42AD-853F-2A58CECBF780@microsoft.com...
> Hello all,
>
> I append/import data from a csv file into a table in SQL Server via BCP.
> The twist is the table that I append into has two extra columns than the
> number of columns in the csv file.  I use the following code to import the
> data, and I got an error and it would not import.
>
> bcp myDB.dbo.my_Table in c:\File_2009.csv -c -t , -T -S
> SERVERNAME\NAMEDINSTANCE
>
> Is there something that I need to do import the data into a table with two
> extra columns?  The two extra columns will be blank and will be populated
> with update query.  I am using SQL Server 2005 Express.
>
> Thanks.

"William Vaughn MVP" wrote:

> Sure, as I discuss in the book, I recommend that you BCP into a work table
> and run validating queries to move the data into production table(s).
> Another approach is to use SqlBulkCopy from ADO.NET where the initial query
> that selects the columns to import can be modified to include just those
> columns of the source table that you need. I expect this can be done in an
> SSIS script as well.
>
> --
> __________________________________________________________________________
> William R. Vaughn
> President and Founder Beta V Corporation
> Author, Mentor, Dad, Grandpa
> Microsoft MVP
> (425) 556-9205  (Pacific time)
> Hitchhiker’s Guide to Visual Studio and SQL Server (7th Edition)
> http://betav.com http://betav.com/blog/billva
> ____________________________________________________________________________________________
>
>
>
> "Accesshelp" <Accessh***@discussions.microsoft.com> wrote in message
> news:8680B4EE-D16D-42AD-853F-2A58CECBF780@microsoft.com...
> > Hello all,
> >
> > I append/import data from a csv file into a table in SQL Server via BCP.
> > The twist is the table that I append into has two extra columns than the
> > number of columns in the csv file.  I use the following code to import the
> > data, and I got an error and it would not import.
> >
> > bcp myDB.dbo.my_Table in c:\File_2009.csv -c -t , -T -S
> > SERVERNAME\NAMEDINSTANCE
> >
> > Is there something that I need to do import the data into a table with two
> > extra columns?  The two extra columns will be blank and will be populated
> > with update query.  I am using SQL Server 2005 Express.
> >
> > Thanks.
>

"Erland Sommarskog" wrote:

> Accesshelp (Accessh***@discussions.microsoft.com) writes:
> > I append/import data from a csv file into a table in SQL Server via BCP. 
> > The twist is the table that I append into has two extra columns than the
> > number of columns in the csv file.  I use the following code to import the
> > data, and I got an error and it would not import. 
> >
> > bcp myDB.dbo.my_Table in c:\File_2009.csv -c -t , -T -S
> > SERVERNAME\NAMEDINSTANCE
> >
> > Is there something that I need to do import the data into a table with two
> > extra columns?  The two extra columns will be blank and will be populated
> > with update query.  I am using SQL Server 2005 Express. 
> 
> You need to use a format file in this case. Here is a sample format
> file for your case, assuming that your file have 4 fields, and your
> table has 6 columns, and that the extra columns are columns 2 and 5
>
> 9.0
> 4
> 1 SQLCHAR 0 0 ","  1 col1 ""
> 2 SQLCHAR 0 0 ","  3 col3 ""
> 3 SQLCHAR 0 0 ","  4 col4 ""
> 4 SQLCHAR 0 0 "\r\n"  6 col6 ""
>
> The first line is the version number of the format.
> The second is the number of fields in the file.
> The following lines describe the fields.
>
> The first field in the field-desciprion is the field number, a running
> number from 1 and up. The next is the data type for the *field* in
> the file. If you import a text file, this is always SQLCHAR or SQLNCHAR,
> the latter for Unicode files.
>
> The the third field is the prefix length, which you only use with binary
> data files. The fourth field is the length of the field. You would used
> it for fixed-length format.
>
> The fifth field is the delimiter, which is comman for the first three
> fields, and CR-LF for the last.
>
> The sixth field gives the column number in the target table, with 1 for
> the first column. By specifying 0, you can opt to not import a certain
> field in the file.
>
> The seventh field holds the column name, but this is informational
> only. BCP ignores it.
>
> The eighth field finally is the collation of the data. If you specify
> "" as I have done here, SQL Server will apply some default.
>
> You use the -f option to specify a format file.
>
> --
> Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se
>
> Links for SQL Server Books Online:
> SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
> SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
> SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
>
>

"Accesshelp" wrote:

> Hello all,
>
> I append/import data from a csv file into a table in SQL Server via BCP. 
> The twist is the table that I append into has two extra columns than the
> number of columns in the csv file.  I use the following code to import the
> data, and I got an error and it would not import. 
>
> bcp myDB.dbo.my_Table in c:\File_2009.csv -c -t , -T -S
> SERVERNAME\NAMEDINSTANCE
>
> Is there something that I need to do import the data into a table with two
> extra columns?  The two extra columns will be blank and will be populated
> with update query.  I am using SQL Server 2005 Express. 
>
> Thanks.

"Linchi Shea" wrote:

> In addition, you may be able to BCP the data into a view on that table. The
> view can exclude the two extra columns.
>
> Linchi
>
> "Accesshelp" wrote:
>
> > Hello all,
> >
> > I append/import data from a csv file into a table in SQL Server via BCP. 
> > The twist is the table that I append into has two extra columns than the
> > number of columns in the csv file.  I use the following code to import the
> > data, and I got an error and it would not import. 
> >
> > bcp myDB.dbo.my_Table in c:\File_2009.csv -c -t , -T -S
> > SERVERNAME\NAMEDINSTANCE
> >
> > Is there something that I need to do import the data into a table with two
> > extra columns?  The two extra columns will be blank and will be populated
> > with update query.  I am using SQL Server 2005 Express. 
> >
> > Thanks.