Importing from CSV File to SQL DB, Strange problem

Ryanvdm · October 9th, 2004, 01:45 AM

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

happygv · October 9th, 2004, 02:47 AM

Can you post your code?

_________________________
- Vijay G
Strive for Perfection

Ryanvdm · October 9th, 2004, 03:14 AM

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

%>

happygv · October 9th, 2004, 03:32 AM

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

Ryanvdm · October 9th, 2004, 03:41 AM

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

Ryanvdm · October 9th, 2004, 04:12 AM

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

happygv · October 10th, 2004, 12:36 AM

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

_________________________
- Vijay G
Strive for Perfection

Ryanvdm · October 11th, 2004, 02:39 AM

Hope this is what you mean?

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

happygv · October 24th, 2004, 09:55 AM

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