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

[grafic.web]

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!!

[gk53]

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

[grafic.web]

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

[gk53]

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

[grafic.web]

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...

[gk53]

use something like
BaseSet("client_name") = rsTest.fields(0)
BaseSet("client_surname") = rsTest.Fields(1)
BaseSet("client_city") = rsTest.Fields(2)

[grafic.web]

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

[gk53]

do you have 10 columns in csv file?
rsTest.fields(9) means you are looking on column number 10...

[grafic.web]

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!!!