[RESOVLED - Moving to new topic]

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

[RESOVLED - Moving to new topic]

Author

Topic

sanctos
Starting Member

19 Posts

Posted - 2004-11-05 : 15:35:59

hi all,

I am kinda new to this bulk insert thing, so here is my problem.
I have a CSV file of a large amount of records (1 million+)

I have created a table in SQL server for importing the CSV data into it. This task needs to be set up and ran every morining to append the new day's data to the DB. I need to write a VBScript to add to an already existing .BAT file that does other things.

The setup in SQL Server is a follows:

Server: MYSERVER
Database: TEST_DEV
Table: AuditJournal
Fields:
- AuditID
- AcctNo
- FName
- LName
- Address
- State
- Gender

The values from the first two columns are not enclosed in quotes, but the other 5 are. The first row is also the column header. A dummy example of my file would be:

IdNumber, AcctNumber, FName, LName, Address, State, Gender
2325,1460,"Joe","Clark","123 Main St","Wisconsin","Male"

How would I go about writing a stored procedure for this? I want to be able to set up a command object in VBScript to execute this SP.

Any help would be cool,

Sanctos

nr
SQLTeam MVY

12543 Posts

Posted - 2004-11-05 : 19:12:01

To bulk insert a quote delimitted file you need a format file (or staging table).
See
http://www.nigelrivett.net/BCP_quoted_CSV_Format_file.html
You can skip the first row.

For calling an SP from vbscript see
http://www.nigelrivett.net/DTSExecuteStoredProcedure.html

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

sanctos
Starting Member

19 Posts

Posted - 2004-11-08 : 12:22:26

Thanks Nigel, I do appreciate your help.

I do have one problem. This is the code I have to run in an ASP page for testing purposes:


<% Option Explicit %>

<%
'Declare connection string
Dim oConn, strConn

'Create connection object and open connection
Set oConn = Server.CreateObject("ADODB.Connection")
strConn = "PROVIDER=sqloledb;DRIVER=SQL Server;SERVER=MYSERVER;DATABASE=MYDATABASE;UID=MYNAME;Password=MYPWORD;"
oConn.Open strConn

'Build bulk insert string
Dim sPath, sFileName, sTableName

sPath = "\\MYSERVER\Inetpub\wwwroot\Intranet\medbill\test\"
sFileName = "MYFILE.CSV"
sTableName = "MYTABLE_DEV"

Dim strSQL
strSQL = "BULK INSERT "
strSQL = strSQL & sTableName
strSQL = strSQL & " FROM '" & sPath & sFileName & "'"
strSQL = strSQL & " WITH ("
strSQL = strSQL & "FIRSTROW = 2, "
strSQL = strSQL & "FORMATFILE = '" & sPath & "Audit_BCP.txt'" 
strSQL = strSQL & ")"

oConn.Execute strSQL

oConn.Close
Set oConn = Nothing

Response.Write "Bulk insert successful!<br><br>"
Response.Write strSQL

%>

This is my Format file "Audit_BCP.txt"


6.0
10
1	SQLNUMERIC	0	9	","	1	AuditID
2	SQLNUMERIC	0	9	","	2	AccntNo
3	SQLDATETIME	0	8	","	3	EditDate
4	SQLCHAR		0	50	","	4	EditTime
5	SQLCHAR		0	50	","	5	User
6	SQLCHAR		0	50	","	6	AccntName
7	SQLCHAR		0	50	","	7	Field
8	SQLCHAR		0	50	","	8	OldValue
9	SQLCHAR		0	50	","	9	NewValue
10	SQLCHAR		0	50	"\r\n"	10	ClientName

This is the error I am getting:

Error Type:
Microsoft OLE DB Provider for SQL Server (0x80040E14)
Bulk insert data conversion error (type mismatch) for row 2, column 3 (EditDate). Line 32

In the CSV file there are only 9 columns instead of 10 (AuditID is not in the CSV file.)

However, I need an auto-number in the SQL table (which is AuditID). How can I alter my BCP text file to accomodate this auto-number, which is of type SQLNUMERIC (18-digit precision, length = 9)

Sanctos

nr
SQLTeam MVY

12543 Posts

Posted - 2004-11-08 : 13:26:11

You don't insert anything into that column i.e. omit the auditid line.
Another option is to create a view on the table which excluds that column and bcp in to that.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

sanctos
Starting Member

19 Posts

Posted - 2004-11-08 : 13:55:01

I adjusted the file by omitting the AuditID line as such:

6.0
10
2 SQLNUMERIC 0 9 "," 2 AccntNo
3 SQLDATETIME 0 8 "," 3 EditDate
4 SQLCHAR 0 50 "," 4 EditTime
5 SQLCHAR 0 50 "," 5 User
6 SQLCHAR 0 50 "," 6 AccntName
7 SQLCHAR 0 50 "," 7 Field
8 SQLCHAR 0 50 "," 8 OldValue
9 SQLCHAR 0 50 "," 9 NewValue
10 SQLCHAR 0 50 "\r\n" 10 ClientName

I get this error:

Error Type:
Microsoft OLE DB Provider for SQL Server (0x80040E14)
Could not bulk insert. Invalid column number in format file 'pathname\Audit_BCP.fmt'.
(FYI - I changed it from TXT to FMT for reading purposes)

So then I tried this:

6.0
9
1 SQLNUMERIC 0 9 "," 1 AccntNo
2 SQLDATETIME 0 8 "," 2 EditDate
3 SQLCHAR 0 50 "," 3 EditTime
4 SQLCHAR 0 50 "," 4 User
5 SQLCHAR 0 50 "," 5 AccntName
6 SQLCHAR 0 50 "," 6 Field
7 SQLCHAR 0 50 "," 7 OldValue
8 SQLCHAR 0 50 "," 8 NewValue
9 SQLCHAR 0 50 "\r\n" 9 ClientName

And got this:

Error Type:
Microsoft OLE DB Provider for SQL Server (0x80040E07)
Disallowed implicit conversion from data type datetime to data type numeric, table 'MYDB_DEV.owner.Table_DEV', column 'AccntNo'. Use the CONVERT function to run this query.
(I covered up the db, owner and table names, so it's not syntax or anything.)

Sanctos

sanctos
Starting Member

19 Posts

Posted - 2004-11-08 : 14:32:22

I changed a few data types up ad have this:

6.0
9
1 SQLCHAR 0 8 "," 1 AccntNo
2 SQLCHAR 0 9 "," 2 EditDate
3 SQLCHAR 0 50 "," 3 EditTime
4 SQLCHAR 0 50 "," 4 User
5 SQLCHAR 0 50 "," 5 AccntName
6 SQLCHAR 0 50 "," 6 Field
7 SQLCHAR 0 50 "," 7 OldValue
8 SQLCHAR 0 50 "," 8 NewValue
9 SQLCHAR 0 50 "\r\n" 9 ClientName

Now I am getting this error:

Error Type:
Microsoft OLE DB Provider for SQL Server (0x80040E14)
Could not bulk insert. Error reading destination table column name for source column 9 in format file

sanctos
Starting Member

19 Posts

Posted - 2004-11-08 : 16:16:06

Just an Update:

I am now getting this message:

Error Type:
Microsoft OLE DB Provider for SQL Server (0x80040E14)
Cannot perform bulk insert. Invalid collation name for source column 9 in format file

I altered the Format file to 8.0 this time and I got this error. Also, I simplified things and made all of the fields SQLCHAR.

8.0
9
1 SQLCHAR 0 50 "," 2 AccntNo SQL_Latin1_General_CP1_CI_AS
2 SQLCHAR 0 50 "," 3 EditDate SQL_Latin1_General_CP1_CI_AS
3 SQLCHAR 0 50 "," 4 EditTime SQL_Latin1_General_CP1_CI_AS
4 SQLCHAR 0 50 "," 5 User SQL_Latin1_General_CP1_CI_AS
5 SQLCHAR 0 50 "," 6 AccntName SQL_Latin1_General_CP1_CI_AS
6 SQLCHAR 0 50 "," 7 Field SQL_Latin1_General_CP1_CI_AS
7 SQLCHAR 0 50 "," 8 OldValue SQL_Latin1_General_CP1_CI_AS
8 SQLCHAR 0 50 "," 9 NewValue SQL_Latin1_General_CP1_CI_AS
9 SQLCHAR 0 50 "\n" 10 ClientName SQL_Latin1_General_CP1_CI_AS

The error falls on the collation name on the last record. I tried putting RAW and "", but none of them work.

Sanctos

nr
SQLTeam MVY

12543 Posts

Posted - 2004-11-08 : 16:44:07

That's odd. Looks like a wrong error message.
Haven't time now but I'll have a look tomorrow.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources