Wrox Programmer Forums
|
Classic ASP Components Discussions specific to components in ASP 3.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Classic ASP Components 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 June 28th, 2006, 08:34 AM
Authorized User
 
Join Date: Jan 2005
Posts: 30
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via Yahoo to anandham
Default Excel to Database

We are inserting data from Server side excel into database using Jet Engine. We are fetching all the records from excel using RecodSet.open "Select * from [Sheet1$]" everything is fine here except the order is getting changed in the database. Is there any way to handle that as we wanted in the same order as it is in Excel.

Thanks

Prem Anandh P.
Ambition Never comes to an End
__________________
Prem Anandh P.
Ambition Never comes to an End
 
Old September 14th, 2006, 08:36 PM
Authorized User
 
Join Date: May 2006
Posts: 31
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi mate,

Have been working on the same thing the last day or so.. I take the Excel and create a Text file from that [using pipe "|" as the delimiter], then bulk insert from the Text file [replaceIsNull is a function I wrote to handle empty/null fields].. You can keep the column order into the Text file and then the Bulk Insert handles all columns in the right order:

Quote:
quote:
    Set objConn = Server.CreateObject("ADODB.Connection")
    objConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=C:\EXCELFILE.xls;" & _
        "Extended Properties=""Excel 8.0;HDR=Yes;"";"
    Set objRS = objConn.Execute("Select * From [Sheet1$]")
    Set oFSO2 = CreateObject("Scripting.FileSystemObject")
    Set oTxt = oFSO2.CreateTextFile("C:\NEWTEXTFILE.txt",True)
    sTxt = ""
    Do While Not objRS.EOF
        For f = 1 To objRS.Fields.Count-1
                sTxt = sTxt & Left(replaceIsNull(objRS.Fields(f).value, "#"), 255)
                If f < objRS.Fields.Count-1 Then
                    sTxt = sTxt & "|"
                Else
                    sTxt = sTxt & vbCrLf
                End If
        Next
        oTxt.Write Replace(sTxt, ">> ", "")
        sTxt = ""
    objRS.MoveNext
    Loop
    oTxt.Write sTxt
    oTxt.Close

    Set oCMD = CreateObject("ADODB.Command")
    Set oCMD.ActiveConnection = oConn
    With oCMD
        .CommandType = adCmdStoredProc
        .CommandText = "spBulkInsert"
        .Execute
    End With

Set oCMD = Nothing
Set oFSO2 = Nothing
Set oTxt = Nothing
Set oFile = Nothing
Set oHTTP = Nothing
Set objConn = Nothing
spBulkInsert is this:

 
Quote:
quote:
Quote:
CREATE Procedure spBulkInsert

    AS

    set nocount on

    /* CREATE TEMP TABLE */

        Create Table #tmpK_SDN(
            tmpKSDN_Column1 VarChar(255),
            tmpKSDN_Column2 VarChar(255),
            tmpKSDN_Column3 VarChar(255),
            tmpKSDN_Column4 VarChar(255),
            tmpKSDN_Column5 VarChar(255),
            tmpKSDN_Column6 VarChar(255),
            tmpKSDN_Column7 VarChar(255),
            tmpKSDN_Column8 VarChar(12),
            tmpKSDN_Column9 VarChar(255),
            tmpKSDN_Column10 VarChar(255),
            tmpKSDN_Column11 VarChar(255),
            tmpKSDN_Column12 VarChar(1000),
            tmpKSDN_Column13 VarChar(255),
            tmpKSDN_Column14 VarChar(255),
            tmpKSDN_Column15 VarChar(255),
            tmpKSDN_Column16 VarChar(255),
            tmpKSDN_Column17 VarChar(255),
            tmpKSDN_Column18 VarChar(255),
            tmpKSDN_Column19 VarChar(255),
            tmpKSDN_Column20 VarChar(255),
            tmpKSDN_Column21 VarChar(255),
            tmpKSDN_Column22 VarChar(255),
            tmpKSDN_Column23 VarChar(1000),
            tmpKSDN_Column24 VarChar(255),
            tmpKSDN_Column25 VarChar(255),
            tmpKSDN_Column26 VarChar(255),
            tmpKSDN_Column27 VarChar(12),
            tmpKSDN_Column28 VarChar(12),
            tmpKSDN_Column29 VarChar(4)
        )

        Bulk Insert #tmpK_SDN
        From 'C:\NEWTEXTFILE.txt'
           With
        (
            DATAFILETYPE = 'char',
            FIELDTERMINATOR = '|',
            FIRSTROW = 2,
            KEEPNULLS,
            ROWTERMINATOR = '\n'
        )

/* Now, sort into a proper table, sorting order as you want */

        Insert Into K_SDN(KS_Type,
                    KS_Title,
                    KS_FirstName,
                    KS_LastName,
                    KS_AKAFirstName,
                    KS_AKALastName,
                    KS_Address1,
                    KS_Address2,
                    KS_Address3,
                    KS_City,
                    KS_StateProvince,
                    KS_PostCode,
                    KS_Country,
                    KS_Entered,
                    KS_Source)
        Select tmpKSDN_Column24, --Type
                    Null, --Title
                    tmpKSDN_Column2, --FN
                    tmpKSDN_Column2, --LN
                    Null, --AKAFN
                    Null, -- AKALN
                    tmpKSDN_Column7, --A1
                    tmpKSDN_Column6, --A2
                    Null, --A3
                    tmpKSDN_Column5, --City
                    NULL, --State
                    Null, -- PC
                    tmpKSDN_Column6, --Country
                    GETUTCDATE(),
                    'DFAT'
        From #tmpK_SDN

/* DROP TEMP TABLE */

        Drop Table #tmpK_SDN
Hasta Luego..
KingRoon

DogFightClothing. No dogs. No fighting.
http://www.dogfightclothing.com





Similar Threads
Thread Thread Starter Forum Replies Last Post
import from excel to sql database manigandan C# 2 March 5th, 2010 05:34 AM
ASP - Excel to Database cancer10 Classic ASP Databases 4 March 18th, 2007 11:07 PM
Excel To Access Database KennethMungwira Excel VBA 3 November 16th, 2006 06:49 AM
Uploading an Excel to Database using ASP anandham Classic ASP Databases 1 February 23rd, 2005 04:29 AM
Excel to mysql database y619915 VB Databases Basics 1 September 26th, 2003 09:48 PM





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