Excel to Database

anandham · June 28th, 2006, 08:34 AM

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

kingroon · September 14th, 2006, 08:36 PM

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