#1
  1. No Profile Picture
    Registered User
    ASP Explorer (0 - 99 posts)

    Join Date
    Nov 2012
    Posts
    5
    Rep Power
    0

    Import .csv file into database (sql server) with classic ASP


    Hi there, i have a .csv file cointaning my data to transfer into mi database..

    is there any way to do it with classic ASP?

    I mean, open the file .csv with my asp script and copy row by row into my database!!!

    Thanks for your help!!
  2. #2
  3. Contributing User
    ASP Skiller (1500 - 1999 posts)

    Join Date
    Mar 2005
    Location
    Columbus, OH
    Posts
    1,548
    Rep Power
    278
    Sure, you can do it.
    You need
    1. upload file into your server in temp folder
    2. validate file structure
    3. open database connection
    4. read file and add data into database
    GK
    __________________________________________________ _____
    if you found this post is useful click Give Rep button (bottom side on this reply ) and agree
  4. #3
  5. No Profile Picture
    Registered User
    ASP Explorer (0 - 99 posts)

    Join Date
    Nov 2012
    Posts
    5
    Rep Power
    0
    Hi, tahnks for you answer!!
    1. upload file into your server in temp folder = DONE
    2. validate file structure= DONE
    3. open database connection= DONE

    I really need the code for :
    4. read file and add data into database

    This is my code untill now :

    'upload fichier CSV ################################################## ###################
    ' Variables
    ' *********
    Dim mySmartUpload
    Dim file
    Dim oConn
    Dim strSQL
    Dim oRs
    Dim intCount
    intCount=0

    ' Object creation
    ' ***************
    Set mySmartUpload = Server.CreateObject("aspSmartUpload.SmartUpload")
    mySmartUpload.MaxFileSize = 2000000
    mySmartUpload.AllowedFilesList = "csv"
    mySmartUpload.DeniedFilesList = "exe,bat,php,asp,xls,txt"
    ' Gestion des erreurs

    ' Upload
    ' ******
    mySmartUpload.Upload

    ' Select each file
    ' ****************
    For each file In mySmartUpload.Files




    ' Only if the file exist
    ' **********************
    If not file.IsMissing Then

    Set FSO = Server.CreateObject("Scripting.FileSystemObject")

    'intCount = mySmartUpload.Save(server.MapPath("..\")&"\databas es\")

    nomfichier = "csv" & request.Cookies("centre_numero") & ".csv"
    file.SaveAs(server.MapPath("..\")&"\databases\" & nomfichier)

    intCount = intCount + 1
    End If
    Next



    'LECTURE CSV ################################################## ###########################
    'Chemin du fichier à importer
    path = Server.MapPath("../") & "/databases/"

    'Connection
    Set connCSV = CreateObject("ADODB.Connection")
    connCSV.Open "Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=" _
    & path & ";Extensions=asc,csv,tab,txt;HDR=NO;Persist Security Info=False"

    'recordset
    Set rsTest= Server.CreateObject("ADODB.Recordset")
    nomfichier = "csv" & request.Cookies("centre_numero") & ".csv"

    rsTest.Open "Select * From " & nomfichier, connCSV, adOpenStatic, adLockReadOnly, adCmdText


    ' OUVERTURE BASE SQL############################################### ########################
    Set BaseLink=Server.CreateObject("ADODB.Connection")
    connstring = "driver={SQL Server};" & "Server=etc....
    BaseLink.Open connstring

    varSQL = "SELECT * FROM client where client_numero<0"
    Set BaseSet= Server.CreateObject("ADODB.Recordset")
    BaseSet.Open varSQL, BaseLink, 3, 3


    My problem is now...

    How can i copy my data of rsTest into BaseSet??

    Thanks for you help
  6. #4
  7. Contributing User
    ASP Skiller (1500 - 1999 posts)

    Join Date
    Mar 2005
    Location
    Columbus, OH
    Posts
    1,548
    Rep Power
    278
    So, you already have recordset rsTest with your file data and BaseSet for adding files to database in this case all what you need

    do while nor rsTest.EOF
    BaseSet.AddNew
    BaseSet("field1")=rsTest("field1")
    BaseSet("field2")=rsTest("field2")
    BaseSet("field3")=rsTest("field3")
    BaseSet.Update
    rsTest.MoveNext
    Loop

    or if you change BaseSet.Open varSQL, BaseLink, 3, 3 to BaseSet.Open varSQL, BaseLink, 1, 4
    you can have

    do while nor rsTest.EOF
    BaseSet.AddNew
    BaseSet("field1")=rsTest("field1")
    BaseSet("field2")=rsTest("field2")
    BaseSet("field3")=rsTest("field3")
    rsTest.MoveNext
    Loop
    BaseSet.UpdateBatch
  8. #5
  9. No Profile Picture
    Registered User
    ASP Explorer (0 - 99 posts)

    Join Date
    Nov 2012
    Posts
    5
    Rep Power
    0
    Hi there!! thank again for you help.. but still i can't make it working!!!!

    For my table i have no problem in calling the fields for example :
    BaseSet("client_name")
    BaseSet("client_surname")
    BaseSet("client_city")
    etc....

    But for the file .csv... how can i kno the name of the fields ???
    BaseSet("client_name") = rsTest("????")
    BaseSet("client_surname") = rsTest("????")
    BaseSet("client_city") = rsTest("????")
    etc....

    Thanks again.. i really can't sort it out...
  10. #6
  11. Contributing User
    ASP Skiller (1500 - 1999 posts)

    Join Date
    Mar 2005
    Location
    Columbus, OH
    Posts
    1,548
    Rep Power
    278
    use something like
    BaseSet("client_name") = rsTest.fields(0)
    BaseSet("client_surname") = rsTest.Fields(1)
    BaseSet("client_city") = rsTest.Fields(2)
  12. #7
  13. No Profile Picture
    Registered User
    ASP Explorer (0 - 99 posts)

    Join Date
    Nov 2012
    Posts
    5
    Rep Power
    0
    I just ttryied and it does not work.....

    this is the errror :
    ADODB.Recordset erreur '800a0cc1'

    Impossible de trouver l'objet dans la collection correspondant au nom ou à la référence ordinale demandé.

    /client_importer_csv.asp, ligne 94


    Line 94 is the following :
    if rsTest.fields(9)<>"" then


    why is not working???

    i'm lost with this part... sigh sob
  14. #8
  15. Contributing User
    ASP Skiller (1500 - 1999 posts)

    Join Date
    Mar 2005
    Location
    Columbus, OH
    Posts
    1,548
    Rep Power
    278
    do you have 10 columns in csv file?
    rsTest.fields(9) means you are looking on column number 10...
  16. #9
  17. No Profile Picture
    Registered User
    ASP Explorer (0 - 99 posts)

    Join Date
    Nov 2012
    Posts
    5
    Rep Power
    0
    Yes mate; i do have 15 columns..

    I really don't understand why it goes in error..

    Do you have any working code to show me?

    Thanks again for all your time!!!

Similar Threads

  1. Import CSV file into database
    By frasersales in forum ASP Development
    Replies: 2
    Last Post: May 10th, 2012, 08:08 AM
  2. SQL Server automated file import
    By g.galla in forum Microsoft SQL Server
    Replies: 0
    Last Post: July 1st, 2009, 01:30 AM
  3. Upload file to SQL database using classic ASP
    By Clare in forum Microsoft SQL Server
    Replies: 3
    Last Post: June 22nd, 2009, 05:59 PM
  4. SQL Server Man. Express File Import
    By DorothyMacha in forum Microsoft SQL Server
    Replies: 0
    Last Post: March 12th, 2007, 11:27 AM
  5. Replies: 0
    Last Post: January 30th, 2007, 03:01 AM

IMN logo majestic logo threadwatch logo seochat tools logo