Uploading csv file( .txt) into access 2000 database using Classic ASP, vb Script

Hello all:

I already have a database with information in it but I want to add more information to this same database without overwriting anything. This information will come from a csv file that is a .txt file.

The table is already created in the database, waiting to be populated.

I am not sure how to do this:

There needs to be an Upload feature. I will click on the Browse button, browse to the "filetoupload.txt" file which is the csv file. This file contains information of this format one record per line:

"21313","john","smith","123 Black Road","Pinecrest","PA","21345","09/04/2006"

So, now when I upload this file it should know that the first field is [id] and the second field is [first name] and the third field is [last name], so on and so forth. And it sticks it in the right table by creating a new record with all this infofrmation from the .CSV file.

This csv file will not be large at all, say 50 records or so, maybe less.

No idea how to do this as I am a noobie and some hand-holding is necessary...[:0)

Indebted to you.

--D.

driven13Asked:
Who is Participating?
 
kevp75Commented:
it shouldn't.

they are comma delimited, but as long as the comma is inside the quotes it won't be recognized as a new field

np, glad I could help  :)
0
 
kevp75Commented:
couple things...
does the server have an upload component?
If so what is it?

in order for this to work, create a file on your server where you want the .txt file uploaded to
name it schema.ini
open it with notepad

add the following lines

[nameoffile.txt]
ColNameHeader=False
Format=CSVDelimited
CharacterSet = ANSI
Col1=columnName datatype

note: rename columnName to match the column in the database, rename datatype to the datatype for that column in the datatbase, to add more columns, just name the Col2, Col3, etc...with the column name and datatype

next, create another file in the same directory, called (whatever you like as long as it has .asp extension)
open in notepad, and add the following

cnStr="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\pathtofolderthatwillcontaintheuploadedtxtfile\;Extended Properties=""text;HDR=No;FMT=Delimited"""
'Making a connection with the database
set objConn= Server.CreateObject("ADODB.Connection")
objConn.Open cnStr
'Executing the SQL Statement
    set rs = objConn.Execute("SELECT * FROM [nameofuploadedfile.txt]")'to select from the txt file
            if not rs.eof then
                  rs.movefirst
                  do
                  'valuestoinsert = rs("columnName")
                                                'put all your columns here respective to the line above
                  rs.movenext
                  conn.execute("INSERT INTO table (fields) VALUES ('"&values&"')")'to insert into the database
                  loop until rs.eof
            end if
    set rs = nothing
'Closing down the connection
    objConn.Close
Set objConn = Nothing
response.write("Insert from text file is succesful!")


once you come back with an answer about the upload component we can tie this up
0
 
driven13Author Commented:
kevp75:

Thank you for the detailed answer.

Unfortunately, I am not sure what is on the server.  It is Windows 2003 and I have root-level access to it.

Can you tell me, how to find out what you are looking for?

Thanx,

--D.
0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
kevp75Commented:
this link here is a script that will test for components installed on your server.
http://www.pensaworks.com/prg_com.asp

just unzip it, and upload it to any site on the server then browse to http://www.sitename.com/test_asp_components.asp

if you switch one of the dropdows to 'Installed' it will show you what is currently installed for components
0
 
driven13Author Commented:
ok...here is what the installed option gave me:

1 Miscellaneous Installed  COM Details  Microsoft Content Linking Component
2 Browser Installed  COM Details  Microsoft Browser Capability
3 Miscellaneous Installed  COM Details  Microsoft Content Rotator
4 Miscellaneous Installed  COM Details  Microsoft Ad Rotator
5 Miscellaneous Installed  COM Details  Microsoft Logging Utility Component
6 XML Installed  COM Details  Microsoft ServerXMLHTTP
7 XML Installed  COM Details  Microsoft XMLDOM Component
8 XML Installed  COM Details  Microsoft XMLHTTP Component
9 File Management Installed  COM Details  MicrosoftFileSystem Object
10 Miscellaneous Installed  COM Details  MicroSoft ADOX Catalog
11 Miscellaneous Installed  COM Details  Windows Script Shell
12 Miscellaneous Installed  COM Details  Windows Script Network
13 Miscellaneous Installed  COM Details  ADODB.Connection
14 Miscellaneous Installed  COM Details  ADODB.Command
15 Miscellaneous Installed  COM Details  ADODB.Recordset
16 Miscellaneous Installed  COM Details  Scripting.Dictionary
17 Miscellaneous Installed  COM Details  Script Encoder
18 XML Installed  COM Details  Microsoft XMLDOM 3.0 Component
0
 
kevp75Commented:
ok...so no installed upload components.  This makes it a bit more difficult.
The following class will let you upload files to the server, I recommend putting it into an include file (just in case you need to re-use it)

Once you do this let me know, and I'll setup something to use the upload class.
http://www.planetsourcecode.com/vb/scripts/ShowCode.asp?txtCodeId=8525&lngWId=4
0
 
driven13Author Commented:
Then when I choose "Upload" components, this is what I got:

Show:  Show All COMs Installed COMs Not Installed COMs From:  All Categories Miscellaneous Email Browser Upload Image Documents File Management Graphs & Charts Server Management Users & Security E-Commerce Validation Forms XML  
# Category Status Details Com
1 Upload Not Installed  None Available  MicroSoft File Upload
2 Upload Not Installed  None Available  SoftArtisians Fileup
3 Upload Not Installed  None Available  w3 Upload
4 Upload Not Installed  None Available  Persits - ASPUpload
5 Upload Not Installed  None Available  DevGuru - dgFileup
6 Upload Not Installed  None Available  Dundas - Upload
7 Upload Not Installed  None Available  Dynu Upload
8 Upload Not Installed  None Available  ASP Smart - aspSmartUpload
9 Upload Not Installed  None Available  ActiveFile
10 Upload Not Installed  None Available  LyfUpload (free)
11 Upload Not Installed  None Available  IP Works WebUpload
12 Upload Not Installed  None Available  CSWebMenu
13 Upload Not Installed  None Available  Simple Upload
14 Upload Not Installed  None Available  ANUPLOAD (free)
15 Upload Not Installed  None Available  Dundas Upload
16 Upload Not Installed  None Available  Dundas Upload
17 Upload Not Installed  None Available  ABC Upload


How do I install the one you think I should?
0
 
driven13Author Commented:
I am using Classic ASP, so the link you mentioned above, also works with Classic ASP right?
0
 
kevp75Commented:
sure does, and you won't need ot install a component  (some of them are a bit pricey)
0
 
driven13Author Commented:
ok...I have installed what you had asked me to and have tested it out by uploading an image file.  It does save it to this folder:

/admin/upload/uploads

--D.
0
 
kevp75Commented:
ok cool.
I've been doing a bit of testing myself.   What I've come up with is that you actually don't need to have schema.ini

try this out:
create an .asp file and copy/paste the code below to it (this is the file we are going to post the form to.

<!--#include virtual="/includes/uploader.asp"--><!--Please change this to the virtual path to the uploader class file-->
<%
Server.ScriptTimeout = 3600
Dim Upload, Folder
Set Upload = New clsUpload
Folder = server.mappath("/")'Change this to the folder you wish to upload the file to
fPath = Upload("file").FileName
if fPath = "" OR ISNULL(fPath) then
      response.write "<div align='center' style='color:RED;font-weight:bold;'>The fields marked * are required!</div>"      
      response.end
else
Upload("file").SaveAs Folder & fPath
sconnstring= "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=d:\physicalpathtotheuploadedfile\;Extended Properties=""text;HDR=No;FMT=Delimited"""make sure to change the path to your file
Set objConn = CreateObject("ADODB.Connection")
     objConn.Open sConnString
          set rs=objConn.execute("SELECT * FROM [filetoupload.txt]")'This opens the file uploaded
               Response.Write("<TABLE border='1' cellpadding='2' cellspacing='0' width='100%'><TR style='background:#ccc'>")
               For X = 0 To rs.Fields.Count - 1
                    Response.Write("<TD>" & rs.Fields.Item(X).Name & "</TD>")'This will get your column names
               Next
               Response.Write("</TR>")
               rs.MoveFirst'Move to the first record....
               While Not rs.EOF
                    Response.Write("<TR>")
                    For X = 0 To rs.Fields.Count - 1
                         Response.write("<TD>" & rs.Fields.Item(X).Value)'This will get the data held in each row, in each column....a.k.a. the recordset
                    Next
                    rs.MoveNext
                    Response.Write("</TR>")
               Wend
               Response.Write("</TABLE>")
          set rs=nothing
     objConn.close
set objConn=nothing
end if
%>

now create the form file using the following code:
<form action="nameoffiletopostto.asp" method="post" enctype="multipart/form-data"><input name="file" type="file" /><input name="Submit" type="submit" /></form>

now create a text file called filetoupload.txt with the following (each on seperate lines)
"21313","john","smith","123 Black Road","Pinecrest","PA","21345","09/04/2006"
"21313","john","smith","123 Black Road","Pinecrest","PA","21345","09/04/2006"
"21313","john","smith","123 Black Road","Pinecrest","PA","21345","09/04/2006"
"21313","john","smith","123 Black Road","Pinecrest","PA","21345","09/04/2006"
"21313","john","smith","123 Black Road","Pinecrest","PA","21345","09/04/2006"
"21313","john","smith","123 Black Road","Pinecrest","PA","21345","09/04/2006"


once this is said and done, browse to the form file you created (http://yoursite.com/uploaderform.asp)

once there hit 'Browse' and browse to the filetoupload.txt that you created and hit submit.
(here is a working example, please note it will only work with filetoupload.txt.....http://www.portalfanatic.com/fup.asp)

once you go through and make your files and stuff, upload the filetoupload.txt file using your form, and let me know what it displays
0
 
driven13Author Commented:
I have it almost working.

This is the error I am getting:

Upload Without COM 3.0 error '80040007'

Folder does not exist: D:\websites\my-site.net\website\admin\upload\admin\upload[Need help? Contact Lewis Moten, lewis@moten.com, http://www.lewismoten.com]

/admin/upload/clsField.asp, line 431

This is the top part of the code in uploadreturn.asp called by uploadform.asp:

<!--#include file="clsUpload.asp"-->
<%
Server.ScriptTimeout = 3600
Dim Upload, Folder
Set Upload = New clsUpload
Folder = server.mappath("admin/upload/uploads/")'Folder you wish to upload the file to
fPath = Upload("file").FileName
if fPath = "" OR ISNULL(fPath) then
     response.write "<div align='center' style='color:RED;font-weight:bold;'>The fields marked * are required!</div>"    
     response.end
else
Upload("file").SaveAs Folder & fPath
sconnstring= "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=d:\websites\my-site.net\website\admin\upload\uploads\;Extended Properties=""text;HDR=No;FMT=Delimited"""
Response.Write sconnstring
Set objConn = CreateObject("ADODB.Connection")
     objConn.Open sConnString
          set rs=objConn.execute("SELECT * FROM [filetoupload.txt]")'This opens the file uploaded
               Response.Write("<TABLE border='1' cellpadding='2' cellspacing='0' width='100%'><TR style='background:#ccc'>")
0
 
driven13Author Commented:
Print outs:

Response.Write Folder
D:\websites\my-site.net\website\admin\upload\uploads

Response.Write sconnstring
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=d:\websites\my-site.net\website\admin\upload\uploads\;Extended Properties="text;HDR=No;FMT=Delimited"

And the file is being uploaded to D:\websites\my-site.net\website\admin\upload\
with the name: uploadsfiletoupload.txt
0
 
kevp75Commented:
ok.  On this line:
Upload("file").SaveAs Folder & fPath

change it to:
Upload("file").SaveAs Folder & "\" & fPath
0
 
kevp75Commented:
looks like server.mappath was taking away that last \
0
 
driven13Author Commented:
Yup!!

It's working and the output is exactly like the one on your page.

0
 
kevp75Commented:
awesome.  Now to get it to insert into the table :)
0
 
driven13Author Commented:
Hey man:

 Sorry to bug you, but have you had a chance to take a look at the "table-insertion" bit....??

Thanx,

--D.
0
 
kevp75Commented:
eeek..sorry bout that.

umm...
replace:
 Response.Write("<TABLE border='1' cellpadding='2' cellspacing='0' width='100%'><TR style='background:#ccc'>")
               For X = 0 To rs.Fields.Count - 1
                    Response.Write("<TD>" & rs.Fields.Item(X).Name & "</TD>")'This will get your column names
               Next
               Response.Write("</TR>")
               rs.MoveFirst'Move to the first record....
               While Not rs.EOF
                    Response.Write("<TR>")
                    For X = 0 To rs.Fields.Count - 1
                         Response.write("<TD>" & rs.Fields.Item(X).Value)'This will get the data held in each row, in each column....a.k.a. the recordset
                    Next
                    rs.MoveNext
                    Response.Write("</TR>")
               Wend
               Response.Write("</TABLE>")


with:
rs.MoveFirst'Move to the first record....
               While Not rs.EOF
                    For X = 0 To rs.Fields.Count - 1
                         conn.execute("INSERT INTO table (fields) VALUES ('"&rs.Fields.Item(X).Value)&"')")'in rs.Fields.Item(X).Value you will have to replace X with a number representing the column number in the text file, starting with 0.
                    Next
                    rs.MoveNext
               Wend

add to just below the connection for the txt file:
conntodb = yourconnectionstringtothedatabase
set conn = createobject("ADODB.Connection")
conn.open conntodb



then close the connection, just before closing the connection to the text file (this will prevent memory leaks):
conn.close
set conn=nothing
0
 
driven13Author Commented:
OK....here is the error message it is giving me now:

Microsoft VBScript compilation error '800a0401'

Expected end of statement

/admin/upload/uploadreturn.asp, line 25

Con.execute("INSERT INTO ln_return (fields) VALUES ('"&rs.Fields.Item(0).Value)&"')")


Also, do I have to spell out the field names in the database somewhere in the script?

Almost there...[:0)
0
 
kevp75Commented:
yes.  replace fields in the INSERT INTO with the fields in your database.  Keep in mind though that you have to match them up to the column #'s of the text file.

if you don't mind, eould you post the structure of the table, here?

that way, what I'll do is make my own db, and work out the insert with the colunms and stuff
0
 
driven13Author Commented:
Still is giving me the same error; I think it is bcuz of an unclosed bracket:

Microsoft VBScript compilation error '800a0401'

Expected end of statement

/admin/upload/uploadreturn.asp, line 25

Con.execute("INSERT INTO ln_return (id,client_id,firstname,lastname,address,city,state,zip,date) VALUES ('"&rs.Fields.Item(0).Value)&"')")
--------------------------------------------------------------------------------------------------------------------------------------------------------------^

All fields are "text" except "id" and "client_id".  "id" is the unique-key field. the "date" field is of type "date/time".

The database is called "deceased".

Let me know if you need anything else to create the db on your end.

--D.
0
 
kevp75Commented:
should be all set.  I'll make one up and get back to you in a few
0
 
kevp75Commented:
first things first, I notice you have a field in your db called date, you'll want to change this to something else, becuase that is a reserved word.  something like client_date would work.

I am setting up my version of the db now, once i have it finished I will post the code for it
0
 
driven13Author Commented:
Oops sorry....I meant to write "pubdate_indv"....

Also, any thoughts on the "extra bracket" issue I mentioned above?
0
 
kevp75Commented:
not exactly sure, quite yet, I'm working out an issue with one of the loops, my file has 10 rows, with the 8 fields.  Well I got it to do the insert, except it is putting in 8 copies of each row.

I'll have it figured out in a few

:-0
0
 
kevp75Commented:
BINGO!

got it.  I'll post the code in a few minutes  (just want to clean it up for ya a bit)
0
 
kevp75Commented:
k.  try this out:
just make sure you have included the uploader class, and make the changes I specified
(you'll have to change the path to your access database, and the path to filetoupload.txt)


<%
Server.ScriptTimeout = 3600
Dim Upload, Folder
Set Upload = New clsUpload
Folder = server.mappath("/")'You can change this to where ever you want to upload the file to
                            'if you do not user server.mappath, get rid of the &"\"& at Upload("file").SaveAs
fPath = Upload("file").FileName
if fPath = "" OR ISNULL(fPath) then
      response.write "<div align='center' style='color:RED;font-weight:bold;'>The fields marked * are required!</div>"      
      response.end
else
Upload("file").SaveAs Folder & "\" & fPath
dbConn = "Driver={Microsoft Access Driver (*.mdb)}; DBQ=c:\physicalpathtoyouraccessDB.mdb;"'change the path to your database
set conn=createobject("adodb.connection")
      conn.open dbConn
            sconnstring= "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=d:\pathtoyourtextfile\;Extended Properties=""text;HDR=No;FMT=Delimited""" 'Make sure to change the path to your Excel file
            Set objConn = CreateObject("ADODB.Connection")
                  objConn.Open sConnString
                        set rs=objConn.execute("SELECT * FROM [filetoupload.txt]")'Rename this to your text file
                              rs.MoveFirst
                              While Not rs.EOF
                                                                                               sqlToInsert = "INSERT INTO deceased (client_id, firstname, lastname, address, city, state, zip, pubdate_indv) VALUES ("&rs.Fields.Item(0).Value&", '"&rs.Fields.Item(1).Value&"', '"&rs.Fields.Item(2).Value&"', '"&rs.Fields.Item(3).Value&"', '"&rs.Fields.Item(4).Value&"', '"&rs.Fields.Item(5).Value&"', '"&rs.Fields.Item(6).Value&"', '"&rs.Fields.Item(7).Value&"')"
                                    conn.execute(sqlToInsert)
                                                                                                response.write("Data inserted!")
                                                                                                response.write sqlToInsert'just comment out this line so it doesn't display the data posted
                              rs.MoveNext
                              Wend
                        set rs=nothing
                  objConn.close
            set objConn=nothing
      conn.close
set conn=nothing
end if
%>
0
 
driven13Author Commented:
before I transfer this to my server I have got a quick question:

The database name is "deceased" and the table name is "ln_return"

I am not sure if this is the case with the above code.  Could you please verify?
0
 
kevp75Commented:
no it's not.  In the above code I have the table name as deceased.

change INSERT INTO deceased   to INSERT INTO ln_return
0
 
driven13Author Commented:
yup!!!!

Beautiful job.  Thank you so much for your patience and excellent help.

Before I let you go could I ask you about what happens if there is a comma in one of the fields, e.g. the "address" field.  Will this bomb then?

Thanx again.

--D.
0
 
driven13Author Commented:
Understood...

Thanx a million,

--D.
0
 
driven13Author Commented:
kevp75:

Sorry but I just thought of something that I forgot to ask you before.

When I am inserting the records from the csv file, what if the first record/row in the csv file are the names of the fields and I do not want this to be inserted into the table?

Thank you in advance,

--D.
0
 
kevp75Commented:
best way would be to open the text file and get rid of that top line.

You could add another For...Next statement like the one that is there, right under the rs.movefirst line.  However, unless you change it everytime, it will recognize the top row of every file you upload to be the column row
0
 
driven13Author Commented:
Thanx kevp75.  I appreciate your time.

--D.
0
 
kevp75Commented:
no prob.

This is one of those ones, that I can be proud of, it was fun setting this up and seeing it work  :)
0
 
kevp75Commented:
i will see if I can come up with something to recognize if the first row is the column names or not, but it may be a waste of time.

I would stick with just removing it for now
0
 
driven13Author Commented:
Yep...it is definitely something to be proud of...I am sure a lot of people are going to use your code when they browse to this page.

Let me know if you come up with something brilliant to solve the above issue.

--D.
0
 
driven13Author Commented:
kevp75:

I know that I have closed this question but I had a quick clarification:

In the code that you wrote why is there NO single quotes around the first value (&rs.Fields.Item(0).Value&) while all the other values have it, in this piece of code?:

                         While Not rs.EOF
                                  sqlToInsert = "INSERT INTO ln_return (id, client_id, deceased, firstname, lastname, address, city, state, zip, pubdate_indv) VALUES ("&rs.Fields.Item(0).Value&", '"&rs.Fields.Item(1).Value&"', '"&rs.Fields.Item(2).Value&"', '"&rs.Fields.Item(3).Value&"', '"&rs.Fields.Item(4).Value&"', '"&rs.Fields.Item(5).Value&"', '"&rs.Fields.Item(6).Value&"', '"&rs.Fields.Item(7).Value&"', '"&rs.Fields.Item(8).Value&"', '"&rs.Fields.Item(9).Value&"')"
                              conn.execute(sqlToInsert)
           response.write("Data inserted!")
            'response.write sqlToInsert'just comment out this line so it doesn't display the data posted
                         rs.MoveNext
                         Wend


Both the first and the second fields are number fields.

Thank you,

--D.
0
 
kevp75Commented:
it's used in case the rest of the fileds aren't numbers.
0
 
driven13Author Commented:
I'm sorry m'man.  Still don't understand it...[:0(

Are you saying that the single quotes are for the fields of type "numbers"?  Why is it important?

I apologize for being slow, but I am trying to learn.

--D.
0
 
kevp75Commented:
no problem

nah single quotes are for text, date fields.   Basically for any field that could contain something other than a number.  Like in a date field you have the / seperator so you would use a single quotes....etc...
0
 
driven13Author Commented:
got it!!!  Thank you.

So, since the first two fields, id and client_id, are always of type "numbers", I can take the single quotes off the second value (&rs.Fields.Item(1).Value&) too, right?
0
 
kevp75Commented:
sure can.  I just had them there in case it wasn't a number
0
 
driven13Author Commented:
Indebted to you kevp75.

--D.
0
 
kevp75Commented:
np  :)

glad I could help out.  The more I do, the more I learn  :)
0
 
driven13Author Commented:
ok...so I need another lesson from you...still trying to get my arms around what you have coded...[:0)

Remember when you gave me the code to display the inserted items?  I have included that in the page too and it is working great.

My question is:  How do I get the real columns names to display?  All the headings now show F1, F2, F3, etc....

Here is the code:

 Response.Write("<TABLE border='1' cellpadding='2' cellspacing='0' width='100%'><TR style='background:#ccc'>")
               For X = 0 To rs.Fields.Count - 1
                    Response.Write("<TD>" & rs.Fields.Item(X).Name & "</TD>")'This will get your column names
               Next
               Response.Write("</TR>")
               rs.MoveFirst'Move to the first record....
               While Not rs.EOF
                    Response.Write("<TR>")
                    For X = 0 To rs.Fields.Count - 1
                         Response.write("<TD>" & rs.Fields.Item(X).Value)'This will get the data held in each row, in each column....a.k.a. the recordset
                    Next
                    rs.MoveNext
                    Response.Write("</TR>")
               Wend
               Response.Write("</TABLE>")

Thanx again, m'man.

--D.
0
 
kevp75Commented:
That's the downfall of not using the schema.ini file.  In schema.ini you set the column names yourself.

You could do it here too, problem is you would have to get rid of your first loop, and hardcode the titles in the appropriate columns

so instead of this:
For X = 0 To rs.Fields.Count - 1
                    Response.Write("<TD>" & rs.Fields.Item(X).Name & "</TD>")'This will get your column names
               Next


you would have to do:
Response.Write("<TD>Column 1</TD>") 'This will get your column names
0
 
driven13Author Commented:
kevp75:

I was out of town so I could not reply earlier.

I understand what you are saying.

As always, thank you very much for your expertise and help.

Regards,

--D.
0
 
kevp75Commented:
no problem  :)
0
 
driven13Author Commented:
kevp75, I have another twist to my story.  I have opened another thread here:

http://www.experts-exchange.com/Web/Web_Languages/ASP/Q_21813706.html

Could you please help me out?

Regards,

--D.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.