Remove quotations/text qualifier from text file when importing to MSSQL using ASP.
Posted on 2006-10-23
I'm using ASP to execute the BULK INSERT statement to import data from an .xls file. The quotation marks are being inputted into the MSSQL DB table. I need to remove the quotation marks (the text qualifier) when importing data from the text file (.csv or .xls file) using ASP. I'm not having any permissions problems.
Please provide the needed code using the variables in the code provided below. The code is needed ASAP so I'm giving it a level of difficult at 250 points.
In the DB, this is what's being imported (with quotations)
"Company1" | "First1" | "Last1" | "Address1"
"Company2" | "First2" | "Last2" | "Address2"
But, this is what I want (without quotations)
Company1 | First1 | Last1 | Address1
Company2 | First2 | Last2 | Address2
Here's my code
<%@ Language=VBScript %>
<% pageTitle = "Bulk Insert Page" %>
filePath = "C:\cold.xls"
set Conn = Server.CreateObject("ADODB.Connection")
Conn.execute("bulk insert Cold from '"&filePath&"' WITH (FIRSTROW = 2, FIELDTERMINATOR = ',', ROWTERMINATOR = '\n')")
set Conn = nothing
<title>Bulk Insert Page</title>
Text File Imported
End of Code