Solved

How to read a CSV with selective text qualifiers into a database using ASP Classic

Posted on 2010-09-08
13
720 Views
Last Modified: 2012-05-10
I am using ASP Classic to read a CSV and import it into a database.  The data file I am given only has selective text qualifiers; i.e. if a field has a comma, the field is surrounded by a double quote.  Therefore, the file has both fields surrounded by double quotes and fields surrounded by nothing.

In other words, the file looks as follows:
John Doe,"123 Main St., Apt. 4",Athens,GA

How do I read this file properly into a DB when there are comma delimiters within the field?

For background, I am currently using a Excel macro called "Text Write Program" to convert the CSV into a pipe-delimited TXT, which my ASP script can read properly.  But I am looking to cut out this manual workaround.
0
Comment
Question by:exactly
  • 4
  • 4
  • 3
  • +1
13 Comments
 
LVL 3

Accepted Solution

by:
Marbleman earned 500 total points
ID: 33632328
I guess it is a little bit tricky:

Split the line into an array using the "," separator

Walk trough the array and check the first character of every field. If it is a quote, remove the qoute and add this field and all following array fields to a new string until you find one field with a qoute as the LAST character.

While walking through the array you can build your SQL-Query...

0
 
LVL 17

Expert Comment

by:nepaluz
ID: 33632706
try this

0
 
LVL 17

Expert Comment

by:nepaluz
ID: 33632709
sorry, forgot to attach the code, try this
0
Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

 
LVL 17

Expert Comment

by:nepaluz
ID: 33632710
i give up!

            Dim MyDelimiter = ","
            Dim IsQuoted As Boolean = True
            Dim sr As New Microsoft.VisualBasic.FileIO.TextFieldParser("YourPath")
            sr.TextFieldType = Microsoft.VisualBasic.FileIO.FieldType.Delimited
            sr.Delimiters = New String() {MyDelimiter}
            sr.HasFieldsEnclosedInQuotes = IsQuoted
0
 

Author Comment

by:exactly
ID: 33635943
nepaluz, how would I incorporate your solution into an ASP Classic page?
0
 
LVL 3

Expert Comment

by:Marbleman
ID: 33636017
nepaluz wrote the code in VB or something else and you are probaly using VBScript.

If you can generate the Object (have the right dll - probably vbrun.dll - on your server) you are doing it that way:

sr = Server.CreateObject("Microsoft.VisualBasic.FileIO.TextFieldParser")

Howerver, you'll probably won't have the Microsoft.VisualBasic stuff available on your server.
That's why I think you'll be faster with a little code...

0
 
LVL 17

Expert Comment

by:nepaluz
ID: 33636023
copy and paste.......?
(and I still have a straight face on too!)
0
 
LVL 11

Expert Comment

by:govindarajan78
ID: 33636988
use oledb provider for csv/text

you can get the data into a recordset using simple select query

http://www.connectionstrings.com/textfile
0
 

Author Comment

by:exactly
ID: 33637802
Marbleman, I am using your methodology for doing this and so far so good.  The only problem I'm having is assigning a variable name to the field.  I tried using f(fieldpos) as shown below but no luck.  Any suggestions?  By the way, I'm not a developer by trade, and I haven't tested/debugged, so and other tips are welcome!

dim strPath,objFso,objTxtStream,theLine,myArr,i
strPath = Server.MapPath("customer_out.csv")
Set objFso = createObject("Scripting.FileSystemObject")
Set objTxtStream = objFso.OpenTextFile( strPath )

while not objTxtStream.AtEndOfStream
Err.Number=0
theLine = objTxtStream.ReadLine
myArr = split(theLine, ",")
i = 0
adj = 0
fieldpos = 0

do while fieldpos<12
if isNull(myArr(i+fieldpos+adj)) then
      f(fieldpos) = ""
      else
                  if  left(myArr(i+fieldpos+adj),1) = """" then
                  f(fieldpos) = replace(myArr(i+fieldpos+adj) & myArr(i+fieldpos+1+adj),"""","" )
                  adj = adj+1
                  else
                  f(fieldpos) = myArr(i+fieldpos+adj)
                  end if
end if
response.write f(fieldpos)
fieldpos=fieldpos+1
loop

response.write "<br>"

wend
0
 

Author Closing Comment

by:exactly
ID: 33637812
very helpful!
0
 
LVL 11

Expert Comment

by:govindarajan78
ID: 33638009
if you face any trouble in current method, try mine its easy.
0
 
LVL 3

Expert Comment

by:Marbleman
ID: 33638904
if you question about the fieldpos and the fieldname is still open:

simply do it that way:

strFields="name1,name2,name3,..."

Split it the same way into an array called aFieldnames and you can access your fieldname
with

aFieldnames(fieldpos)

Or did I get you wrong on this?
0
 

Author Comment

by:exactly
ID: 33639079
Marbleman, that fixed it.  Working 100% now.  Thanks for leading the way!
0

Featured Post

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

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

Suggested Solutions

I have helped a lot of people on EE with their coding sources and have enjoyed near about every minute of it. Sometimes it can get a little tedious but it is always a challenge and the one thing that I always say is:  The Exchange of information …
This demonstration started out as a follow up to some recently posted questions on the subject of logging in: http://www.experts-exchange.com/Programming/Languages/Scripting/JavaScript/Q_28634665.html and http://www.experts-exchange.com/Programming/…
In a recent question (https://www.experts-exchange.com/questions/28997919/Pagination-in-Adobe-Acrobat.html) here at Experts Exchange, a member asked how to add page numbers to a PDF file using Adobe Acrobat XI Pro. This short video Micro Tutorial sh…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

776 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