Wrox Programmer Forums
Go Back   Wrox Programmer Forums > ASP.NET and ASP > ASP 3 Classic ASP Active Server Pages 3.0 > ASP Pro Code Clinic
|
ASP Pro Code Clinic As of Oct 5, 2005, this forum is now locked. No posts have been deleted. Please use "Classic ASP Professional" at: http://p2p.wrox.com/forum.asp?FORUM_ID=56 for discussions similar to the old ASP Pro Code Clinic or one of the other many remaining ASP and ASP.NET forums here.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the ASP Pro Code Clinic section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old October 9th, 2004, 01:45 AM
Authorized User
 
Join Date: Sep 2004
Posts: 17
Thanks: 0
Thanked 0 Times in 0 Posts
Default Importing from CSV File to SQL DB, Strange problem

I'm importing from a csv file into an sql DB. Everything imports perfectly except for the following strange event:

The two fields are as follows: prodCode, prodDesc

The prodCode field contains product codes (duh), but this is where the problem is:

as an example I have the following codes in the csv file:

012345
045678
078910
147263
109384BOX10
23443sBOX10

My problem is that if I do a loop and response.write prodCode, it does not print the two last codes. It does print their descriptions but not the codes.

However if I have the following code combinations it prints them fine and does not ignore the first two:

012345
045678
078910PKT50
147263PKT12
109384BOX10
23443sBOX10

any help would be appreciated

Ryan

 
Old October 9th, 2004, 02:47 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default

Can you post your code?

_________________________
- Vijay G
Strive for Perfection
 
Old October 9th, 2004, 03:14 AM
Authorized User
 
Join Date: Sep 2004
Posts: 17
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Sure

<%

objConn.BeginTrans

    bulkCSVFileName = request("bulkCSVFileName")
    path = Server.MapPath("uploads/csv")

    set fs = CreateObject("Scripting.FileSystemObject")
    set folder = fs.GetFolder(path)

    path = fs.GetFolder(path)

    dbConnFormat = "Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=" & path & ";Extensions=asc,csv,tab,txt;Persist Security Info=False"

    Set oConn = Server.CreateObject("ADODB.Connection")

    strSQL = "SELECT prodCode, prodDesc FROM " & bulkCSVFileName & " "
    oConn.Open dbConnFormat
    objRS.Open strSQL, oConn

    while not objRS.eof
        response.write objRS("prodCode") & ", " & objRS("prodDesc") & "<br>"
    objRS.movenext
    wend
    objRS.Close
    oConn.Close

    'clean up
    Set oConn = Nothing
    Set objRS = Nothing

objConn.CommitTrans

%>

 
Old October 9th, 2004, 03:32 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default

Can you check these posts, and construct the "BULK INSERT " within your asp code and execute that from ASP to sql, that is much easier.

http://p2p.wrox.com/topic.asp?TOPIC_ID=15995

http://p2p.wrox.com/topic.asp?TOPIC_ID=18894

Hope that helps.
Cheers!

_________________________
- Vijay G
Strive for Perfection
 
Old October 9th, 2004, 03:41 AM
Authorized User
 
Join Date: Sep 2004
Posts: 17
Thanks: 0
Thanked 0 Times in 0 Posts
Default

THanks I'll give it a try and let you know how it goes.

 
Old October 9th, 2004, 04:12 AM
Authorized User
 
Join Date: Sep 2004
Posts: 17
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hey Vijay

I have tried the bulk insert. The one problem Im finding is that not all the fields in the CSV file are in the database because they are not needed in the csv file. I also need to update a field in the Database called "lastUpdated" with the date of the update.

Is there a way I can specify the fields needed to be imported?

thanks
Ryan

 
Old October 10th, 2004, 12:36 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default

Can you explain on the structure of CSV file. How is the data stored on csv?

_________________________
- Vijay G
Strive for Perfection
 
Old October 11th, 2004, 02:39 AM
Authorized User
 
Join Date: Sep 2004
Posts: 17
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hope this is what you mean?

prodCode,prodDesc,prodUnit
442009,ADH GLUE STICK 9gr TREELINE,EACH



 
Old October 24th, 2004, 09:55 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default

Hi there,

Sorry for the delayed reply.

When the CSV structure doesn't seem to be similar to that of the Table structure, this method wouldn't be helpful to you. so, you got to look for other ways of doing this.
Code:
<% 
Option Explicit 

'Code to display data from the current text-based logfile 
Dim objFSO, oInStream, sLine, RowArray, bulkCSVFileName, Path

'Define the constants used by the FSO 
Const Forreading = 1 

'Create an instance of the FSO 
Set objFSO = CreateObject("Scripting.fileSystemObject") 

'Check the file exists 
bulkCSVFileName = request("bulkCSVFileName")
path = "uploads/csv"

If objFSO.fileExists( Server.MapPath ( path & "/" & bulkCSVFileName ) ) Then 
    'Open a file for reading 
    Set oInStream = objFSO.OpenTextfile( Server.MapPath( path & "/" & bulkCSVFileName ), Forreading, False ) 

    'Go past the first line that contains Column header. Else comment the next line of code.
    sLine = oInStream.readLine 

    Do Until oInStream.AtEndOfStream 
        sLine = oInStream.readLine 
        RowArray = Split( sLine, "," ) 
        Response.Write "<b>COL1</b>: " & RowArray(0) 
        Response.Write "<b>Col2</b>: " & RowArray(1) 
        Response.Write "<b>Col3</b>: " & RowArray(2) 
        Response.Write "<br>"
        ' Your update code goes here...
    Loop 
    oInStream.Close 
    Set oInStream = Nothing 
Else 
    Response.Write "file not found!" 
End If 
Set objFSO = Nothing 
%>
Try this. And write update code within the DO UNTIL... LOOP once after every line is read and split into columns and use the values to update the table values.

Hope that helps.
Cheers!

_________________________
- Vijay G
Strive for Perfection





Similar Threads
Thread Thread Starter Forum Replies Last Post
Importing data from a CSV file? kr33 SQL Server 2000 26 January 3rd, 2007 02:38 AM
Importing CSV file into SQL from Web daniel SQL Server 2005 6 December 15th, 2006 09:48 AM
Importing csv file to SQL Server Using VB.Net ninel General .NET 0 August 8th, 2006 02:11 PM
Importing a CSV file into SQL rsmuts ASP.NET 1.0 and 1.1 Professional 2 July 24th, 2006 10:14 AM
Importing data from CSV file g_vamsi_krish ASP.NET 1.0 and 1.1 Basics 2 May 23rd, 2006 01:29 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.