Solved

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

Posted on 2006-10-23
3
988 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

Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

Question has a verified solution.

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

Although it can be difficult to imagine, someday your child will have a career of his or her own. He or she will likely start a family, buy a home and start having their own children. So, while being a kid is still extremely important, it’s also …
Developer portfolios can be a bit of an enigma—how do you present yourself to employers without burying them in lines of code?  A modern portfolio is more than just work samples, it’s also a statement of how you work.
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).

733 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