Eric Barro
ebarro at verizon.net
Tue May 1 09:06:36 CDT 2007
John, This is the SQL query I use to get the field names... DECLARE @TableName varchar(50) SET @TableName = 'tmpemployees' SELECT syscolumns.name AS DBFieldNames, syscolumns.type AS DataType, (syscolumns.length/2) AS DataLength FROM sysobjects INNER JOIN syscolumns ON sysobjects.id = syscolumns.id WHERE sysobjects.name = @TableName ORDER BY colorder Here's what I came across when I googled it... http://www.thescripts.com/forum/thread520822.html 8.0 5 1 SQLCHAR 0 0 ";\"" 1 col1 "" 2 SQLCHAR 0 0 "\";" 2 col2 "" 3 SQLCHAR 0 0 ";" 3 col3 "" 4 SQLCHAR 0 0 ";" 4 col3 "" 5 SQLCHAR 0 0 "\r\n" 5 col3 "" The first row is the version of the file format. Next is the number of fields in the file. Following lines describe one field each. First column is record number. Second column is data type of the field in the file. For a text file this is always SQLCHAR or always SQLNCHAR for a Unicode file. Other data types are only used with binary formats. The third column is prefix-length, used only for binary files. Fourth column is the length, and is used for fixed-length fields. Fifth field is the terminator, and it is here you specify the quotes. Six column is the database column, with 1 denoting the first column. 0 means that this field is not to be imported. Seventh column is the column name, but it's informational. BCP/BULK INSERT does not use it. Last colunm is the collation for the data in the file. Overall, keep in mind that BCP/BULK INSERT reads a binary file and a row terminator is really only the terminator for the last field. Eric -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of JWColby Sent: Tuesday, May 01, 2007 4:06 AM To: 'Access Developers discussion and problem solving' Cc: dba-sqlserver at databaseadvisors.com Subject: [AccessD] Bulk copy spec - was RE: using a saved SSIS with VB.Net Is there any way to have SQL Server export a spec for a table that bulk copy can use, at least what the fields look like etc? I have never used bulk copy and there are a LOT of fields in the table. Alternately is there somewhere that I can find the bulk copy spec, what the format file is supposed to look like? John W. Colby Colby Consulting www.ColbyConsulting.com -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Eric Barro Sent: Monday, April 30, 2007 7:31 PM To: 'Access Developers discussion and problem solving' Subject: Re: [AccessD] using a saved SSIS with VB.Net Have you looked into BULK INSERT in SQL? This is supposed to be a faster data import method. Using T-SQL you can do something like this... CREATE TABLE #tmpEmployees (<fieldnames here>) BULK INSERT #tmpEmployees FROM 'c:\temp\import.csv' WITH (FORMATFILE = 'c:\temp\importCSV.fmt' importCSV.fmt would contain the file format...in this example it's fixed width 8.0 18 1 SQLCHAR 0 5 "" 1 suffix SQL_Latin1_General_CP1_CI_AS 2 SQLCHAR 0 30 "" 2 last_name SQL_Latin1_General_CP1_CI_AS 3 SQLCHAR 0 20 "" 3 first_name SQL_Latin1_General_CP1_CI_AS -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.5.467 / Virus Database: 269.6.2/782 - Release Date: 5/1/2007 2:10 AM