Solved

Remove quotations/text qualifier from text file when importing to MSSQL using ASP.

Posted on 2006-10-23
3
986 Views
Last Modified: 2012-08-14
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
Start Code:
<%@ Language=VBScript %>
<% pageTitle = "Bulk Insert Page" %>
<!--#include file="adovbs.inc"-->
<!--#include file="db.asp"-->

<%
dim Conn
dim filePath

filePath = "C:\cold.xls"

set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open ConString
   
Conn.execute("bulk insert Cold from '"&filePath&"' WITH (FIRSTROW = 2, FIELDTERMINATOR = ',', ROWTERMINATOR = '\n')")

Conn.close
set Conn = nothing
%>

<html>
<title>Bulk Insert Page</title>

<body>
Text File Imported
</body>
</html>

End of Code
0
Comment
Question by:Patriotec
3 Comments
 
LVL 3

Accepted Solution

by:
zawedikian earned 250 total points
ID: 17801237
hi

what u shoudl do is before you insert, you should read the data from the file and put in a string let s say names Str.

then put: Str = replace(Str,"'","")  (this is: Str, " ' " , " " without the spaces)

then insert Str in the db.
0

Featured Post

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Envision that you are chipping away at another e-business site with a team of pundit developers and designers. Everything seems, by all accounts, to be going easily.
Color can increase conversions, create feelings of warmth or even incite people to get behind a cause. If you want your website to really impact site visitors, then it is vital to consider the impact color has on them.
This tutorial walks through the best practices in adding a local business to Google Maps including how to properly search for duplicates, marker placement, and inputing business details. Login to your Google Account, then search for "Google Mapmaker…
Any person in technology especially those working for big companies should at least know about the basics of web accessibility. Believe it or not there are even laws in place that require businesses to provide such means for the disabled and aging p…

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question