Insert Into help

Results 1 to 3 of 3

Thread: Insert Into help

  1. #1
    Jason Guest

    Default Insert Into help

    Hi.<BR>Using Access2000. I want to import the contents of a comma seperated text field into my db uing ASP(caue i need to import about 9000 files). Each text file has column headings that match the column names in the db table and each line reads like so:<BR>12/24/2000,43,12,43,1000,5<BR>I need each of those values to imported into the correct places in the table.<BR><BR>Heres what Ive got so far:<BR>&#060;%<BR>dim objFSO, objFile<BR>set objFSO = CreateObject("Scripting.FileSystemObject")<BR>set objFile = objFSO.GetFile("C:Program FilesWeb DesignHistorical QuotesReady for Access1.csv")<BR>%&#062;<BR><BR>&#060;%<BR>dim objFileTextStream<BR>set objFileTextStream = objFile.OpenAsTextStream<BR><BR>dim strLine<BR>do while objFileTextStream.AtEndOfStream &#060;&#062; True<BR>strLine = Server.HTMLEncode (objFileTextStream.ReadLine)<BR>response.write strLine<BR>response.write "<BR>"<BR>Loop<BR><BR>%&#062;<BR>that prints the contents line by line (which I dont need, but was for testing purposes)<BR><BR>Can I do a simple insert into like so?<BR>&#060;%<BR>dim ors<BR>set ors = Server.CreateObject("ADODB.Connection")<BR>ors.ope n "DSN=main;"<BR>strSQL = "Insert INTO Prices (Date,Open,High,Low,Volume,StockID) Values (&strLine&)"<BR>ors.Execute strSQL<BR><BR>The syntax for the insert is probably off, but can this way be done?<BR><BR>Any advise would be sweet.<BR><BR>

  2. #2
    Join Date
    Dec 1969

    Default just change the insert a lil bit, and you can do i

    strSQL = "Insert INTO Prices (Date,Open,High,Low,Volume,StockID) Values ("&strLine&")"<BR><BR>make sure you have "#"&#039s for dates w/ access and "&#039"&#039s for text fields...for dates your strLine should be "...,&#039#12/04/00#&#039,..." for access or else it won&#039t work...just a thought...

  3. #3
    Jason Guest

    Default One more bit of help please?

    Hey,<BR>Thanks for the help. It worked fine except with the date portion. <BR>strSQL = "Insert INTO Prices (Date,High,Low,Close,Open,Volume,StockID) Values ("&strLine&")" <BR>doesnt insert anything. If I remove the date portion above and the date entry in the text file, it works fine.<BR><BR>I changed the text file so that the strLine reads &#039#1/1/2000#&#039,24,21,24,23,1000,2. Also tried #1/1/2000#,...<BR>but that didnt work either.<BR><BR>Any suggestions?<BR>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts