SQLDMO.BulkCopy

[silven]

Hi all I am trying to do a Bulk Copy from a "tab delmimited" text file to a
table in my database. I have it almost working except when the file has
too few columns for the table (table has 421 columns).

Some of my the files will have 419 columns some others files will have 421
columns.

When my bulk insert script encounters a file with 419 colums it will put
some of the data from the next line in the last 2 columns.
I tried creating a DTS package with a bulk insert and I get the same out
come.
Here is my test script can anyone help
'--------------------------------------------------------------

<%
Dim objServer: Set objServer = Server.CreateOb ject("SQLDMO.SQ LServer")
Dim objBCP: Set objBCP = Server.CreateOb ject("SQLDMO.Bu lkCopy")
Dim objDB: Set objDB = Server.CreateOb ject("SQLDMO.Da tabase")

dim BulkCopy
dim objTable
dim item
dim g_strUploadPath : g_strUploadPath =Server.MapPath ("../DOC2/")&"\upload \"

dim strFileName: strFileName = g_strUploadPath & "ex_test.tx t"

'if file doesn't exist and it's an import,
'don't waste time (too be add later)
'If Import = True And Dir(FileName) = "" Then Exit Function

'On Error GoTo ErrorHandler
objServer.Conne ct "XXX.XXX.XX.XX" , "XXX", "XX"
objServer.Enabl eBcp = 1

Set objDB = objServer.Datab ases("Advia120v 2_dev")
With objBCP
.DataFilePath = strFileName

.UseBulkCopyOpt ion = True

'tab delmitted, carriage return line feed ends row
.DataFileType = 2
'.ColumnDelimit er = chr(9)
'.RowDelimiter = chr(13) & chr(10)
.IncludeIdentit yValues = False
End With
objDB.Tables("H emo_193_39552_3 9").ImportDa ta objBCP
'BCP = True

'ErrorHandler:
'Set objBCP = Nothing
'Set objServer = Nothing
%>
'--------------------------------------------------------------
thanks

[Simon Hayes]

silven <si****@removes ilven.canada.co m> wrote in message news:<Xn******* *************** *******@206.167 .113.5>...

Hi all I am trying to do a Bulk Copy from a "tab delmimited" text file to a
table in my database. I have it almost working except when the file has
too few columns for the table (table has 421 columns).

Some of my the files will have 419 columns some others files will have 421
columns.

When my bulk insert script encounters a file with 419 colums it will put
some of the data from the next line in the last 2 columns.
I tried creating a DTS package with a bulk insert and I get the same out
come.
Here is my test script can anyone help
'--------------------------------------------------------------

<%
Dim objServer: Set objServer = Server.CreateOb ject("SQLDMO.SQ LServer")
Dim objBCP: Set objBCP = Server.CreateOb ject("SQLDMO.Bu lkCopy")
Dim objDB: Set objDB = Server.CreateOb ject("SQLDMO.Da tabase")

dim BulkCopy
dim objTable
dim item
dim g_strUploadPath : g_strUploadPath =Server.MapPath ("../DOC2/")&"\upload \"

dim strFileName: strFileName = g_strUploadPath & "ex_test.tx t"

'if file doesn't exist and it's an import,
'don't waste time (too be add later)
'If Import = True And Dir(FileName) = "" Then Exit Function

'On Error GoTo ErrorHandler
objServer.Conne ct "XXX.XXX.XX.XX" , "XXX", "XX"
objServer.Enabl eBcp = 1

Set objDB = objServer.Datab ases("Advia120v 2_dev")
With objBCP
.DataFilePath = strFileName

.UseBulkCopyOpt ion = True

'tab delmitted, carriage return line feed ends row
.DataFileType = 2
'.ColumnDelimit er = chr(9)
'.RowDelimiter = chr(13) & chr(10)
.IncludeIdentit yValues = False
End With
objDB.Tables("H emo_193_39552_3 9").ImportDa ta objBCP
'BCP = True

'ErrorHandler:
'Set objBCP = Nothing
'Set objServer = Nothing
%>
'--------------------------------------------------------------
thanks

To bulk load a data file with fewer columns than the table, you can
use a format file - see "Using a Data File with Fewer Fields" in Books
Online in the section on BCP.

If only some of your files are incorrect, then you need to somehow
identify them before loading, and set the .FormatFilePath property
only if needed.

Simon

[silven]

sq*@hayes.ch (Simon Hayes) wrote in
news:60******** *************** ***@posting.goo gle.com:

silven <si****@removes ilven.canada.co m> wrote in message
news:<Xn******* *************** *******@206.167 .113.5>...

Hi all I am trying to do a Bulk Copy from a "tab delmimited" text
file to a table in my database. I have it almost working except when
the file has too few columns for the table (table has 421 columns).

Some of my the files will have 419 columns some others files will
have 421 columns.

When my bulk insert script encounters a file with 419 colums it will
put some of the data from the next line in the last 2 columns.
I tried creating a DTS package with a bulk insert and I get the same
out come.
Here is my test script can anyone help
'--------------------------------------------------------------

<%
Dim objServer: Set objServer =
Server.CreateOb ject("SQLDMO.SQ LServer") Dim objBCP: Set objBCP =
Server.CreateOb ject("SQLDMO.Bu lkCopy") Dim objDB: Set objDB =
Server.CreateOb ject("SQLDMO.Da tabase")

dim BulkCopy
dim objTable
dim item
dim g_strUploadPath : g_strUploadPath
=Server.MapPath ("../DOC2/")&"\upload \"

dim strFileName: strFileName = g_strUploadPath & "ex_test.tx t"

'if file doesn't exist and it's an import,
'don't waste time (too be add later)
'If Import = True And Dir(FileName) = "" Then Exit Function

'On Error GoTo ErrorHandler
objServer.Conne ct "XXX.XXX.XX.XX" , "XXX", "XX"
objServer.Enabl eBcp = 1

Set objDB = objServer.Datab ases("Advia120v 2_dev")
With objBCP
.DataFilePath = strFileName

.UseBulkCopyOpt ion = True

'tab delmitted, carriage return line feed ends row
.DataFileType = 2
'.ColumnDelimit er = chr(9)
'.RowDelimiter = chr(13) & chr(10)
.IncludeIdentit yValues = False
End With
objDB.Tables("H emo_193_39552_3 9").ImportDa ta objBCP
'BCP = True

'ErrorHandler:
'Set objBCP = Nothing
'Set objServer = Nothing
%>
'--------------------------------------------------------------
thanks

To bulk load a data file with fewer columns than the table, you can
use a format file - see "Using a Data File with Fewer Fields" in Books
Online in the section on BCP.

If only some of your files are incorrect, then you need to somehow
identify them before loading, and set the .FormatFilePath property
only if needed.

Simon

I am not sure if format files will work because I can never tell if the
file I am importing from has 419 or 421 columns.

If I use the import function in SQL Server, if I import a file that has
419 columns the remainning will be filled with NULLs, I need to duplicate
that. Do you think it can be done with format files
Thanks

silven

[Simon Hayes]

<snip>

I am not sure if format files will work because I can never tell if the
file I am importing from has 419 or 421 columns.

If I use the import function in SQL Server, if I import a file that has
419 columns the remainning will be filled with NULLs, I need to duplicate
that. Do you think it can be done with format files
Thanks

silven

As I said, if you can identify the number of columns before uploading,
then you can set the FormatFile property or not, as you need to. Since
you're doing this in VB, you could easily open the text file and count
the delimiters in the first line to work this out.

The extra two columns will be set to NULL, assuming that they are
NULLable and don't have DEFAULT constraints. Since it works with the
Import tool, that's probably a safe assumption.

Simon