#1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2003
    Location
    Ontario
    Posts
    3
    Rep Power
    0

    Unhappy Importing Excel to Access / SQL


    I want to import a number of excel files (almost 300 files actually) into access to create one large access table. Since this would be too tedious to do manually I am trying to create a SQL select query (using "insert into" commands) to automate this exercise. However my code is not working. Can anyone give me some help with this. Here is the code:

    Select;
    INSERT INTO ALL (LOCATION[, LANDSCAPE[, RELOCATION[, NUMBER OF ROADS[,TRAFFIC INTENSITY[, DISTANCE[, AREA[, TAG[, RETURN[, CAPTURE[, SPECIES]]]]]]]]]]) [IN C:/TRY.MDB]
    SELECT [C:/TRYDATA.XLS.]LOCATION[, LANDSCAPE[, RELOCATION[, NUMBER OF ROADS[, TRAFFIC INTENSITY[, DISTANCE[, AREA[, TAG[, RETURN[, CAPTURE[, SPECIES]]]]]]]]]]
    FROM [EXCEL 2000; HDR=NO; IMEX=2;
    DATABASE=C:/TRYDATA.XLS; TABLE=A1:K284]

    all = the access table where I would like all the excel data to be added
    trydata.xls = the excel file that I am trying to add to the all table

    Sorry. I'm fairly new at SQL so I may have some glaring code errors.

    Thanks
  2. #2
  3. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2003
    Location
    Manchester, UK
    Posts
    23
    Rep Power
    0
    To get a better handle on it why don't you try to use an Excel macro and SQL*XL. Your code would look something like this:

    Dim sql as String
    Dim file as String
    Dim xls as Workbook

    connect_through_sqlxl
    For each file in get_xls_list
    Set xls = Workbooks.Open(file)

    SQLXL.InsertRecordset Table:="mytable", Columns:="col1,col2", DataRange:=Range("$A$2:$B$300"), PromptOnError:=False, SortToStatus:=True, CommitEachRow:=True, Orientation:=1, Silent:=True, Feedback:=True

    SQLXL.Database.Commit

    xls.Close
    Next file
    disconnect_through_sqlxl

IMN logo majestic logo threadwatch logo seochat tools logo