Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2006-10-23
3
Medium Priority
?
992 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 1000 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

Containers & Docker to Create a Powerful Team

Containers are an incredibly powerful technology that can provide you and/or your engineering team with huge productivity gains. Using containers, you can deploy, back up, replicate, and move apps and their dependencies quickly and easily.

Question has a verified solution.

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

FAQ pages provide a simple way for you to supply and for customers to find answers to the most common questions about your company. Here are six reasons why your company website should have a FAQ page
Dramatic changes are revolutionizing how we build and use technology. Every company is automating, digitizing, and modernizing operations. We need a better, more connected way to work together as teams so we can harness the insights from our system…
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…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
Suggested Courses

722 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