Solved

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

Posted on 2006-10-23
3
991 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

An enjoyable and seamless user experience can go a long way on an eCommerce site. While a cohesive layout and engaging copy play roles in creating a positive user experience, some sites neglect aspects that seem marginal but in actuality prove very …
When it comes to security, close monitoring is a must. According to WhiteHat Security annual report, a substantial number of all web applications are vulnerable always. Monitis offers a new product - fully-featured Website security monitoring and pr…
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
Learn how to create flexible layouts using relative units in CSS.  New relative units added in CSS3 include vw(viewports width), vh(viewports height), vmin(minimum of viewports height and width), and vmax (maximum of viewports height and width).

632 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