Results 1 to 8 of 8
  1. #1
    Join Date
    Sep 2005
    Posts
    5

    Unhappy Unanswered: Multiple Import into Access with Autofill

    Hello Everyone,

    I am currently trying to Import several excel *csv files that are all stored in one main directory but each week produces a new folder inside of the main directory, So i need to be able to have it scan all subdirectories as well. I need to automate the import process so that i dont have to do this at one at a time for 400 files a week. I also need to, if at all possible, have the VBA code automatically fill in the first column with the entire filename and the create date. file name is standard 8 characters. IBI*****.txt

    I know very little about using VBA code. I have just started and i really need to free some time to read a book about it but if anyone can help I would be extremely grateful for saving me from mindless imports and data entry

  2. #2
    Join Date
    Sep 2005
    Posts
    5
    Hey i have finally gotten the vba code i was workin on to do the import of all files... can i edit this code to do subdirectories as well and if not does anyone know how to autofill the create date and filename in a column for quite a few thousand lines?

    heres the code im using for the autoimport

    Private Sub ImportAllFiles()
    Dim strfile As String

    ChDir ("C:/my files/")
    strfile = Dir("C:/my files/*.csv")
    Do While Len(strfile) > 0
    DoCmd.TransferText acImportDelim, "Import Spec", "Table Name", "C:/my files/" & strfile, True
    strfile = Dir
    Loop

    Any Help is appreciated thanx
    End Sub

  3. #3
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    strFile already knows the filename
    TableName presumably has a column "thisColumn" (text) to take the filename.

    as a precaution, before you start looping thru the files put an illegal filename in any existing empty thisColumn...
    strSQL = "UPDATE TableName SET thisColumn = 'SOMETHING ILLEGAL' WHERE thisColumn = '';"
    currentdb.execute strSQL

    ...and then inside your loop immediately after your line:
    DoCmd.TransferText acImportDelim, "Import Spec", "Table Name", "C:/my files/" & strfile, True
    put:
    strSQL = "UPDATE TableName SET thisColumn = '" & strFile & "' WHERE thisColumn = '';"
    currentdb.execute strSQL

    ...and then when you are done looping, maybe clear out the something stupid:
    strSQL = "UPDATE TableName SET thisColumn = '' WHERE thisColumn = 'SOMETHING ILLEGAL';"
    currentdb.execute

    watch out for the mixture of single and double quotes in the above.

    if you want to strip out the path from strFile and just collect the name, take a look at instrrev() in help: locate the first \ (=last cos of the rev) and then use mid$()

    a quick thrash from memory:
    strFile = mid$(strFile, instrrev(strFile, "\") + 1, len(strFile - 5 - instrrev(strFile, "\")
    ...but i could be out by a character here or there - experiment.

    izy

    PS, replace both instances of SOMETHING ILLEGAL by something that is truly an illegal filename (i can't be bothered to look that up right now)
    currently using SS 2008R2

  4. #4
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    oh! i see you want the .CSV as well.

    so it is simpler:
    strFile = mid$(strFile, instrrev(strFile, "\") + 1)

    simple enough to go in the strSQL
    strSQL = "UPDATE TableName SET thisColumn = '" & strFile & "' WHERE thisColumn = '';"
    becomes
    strSQL = "UPDATE TableName SET thisColumn = '" & mid$(strFile, instrrev(strFile, "\") + 1) & "' WHERE thisColumn = '';"

    izy
    currently using SS 2008R2

  5. #5
    Join Date
    Sep 2005
    Posts
    5
    Quote Originally Posted by izyrider
    oh! i see you want the .CSV as well.

    so it is simpler:
    strFile = mid$(strFile, instrrev(strFile, "\") + 1)

    simple enough to go in the strSQL
    strSQL = "UPDATE TableName SET thisColumn = '" & strFile & "' WHERE thisColumn = '';"
    becomes
    strSQL = "UPDATE TableName SET thisColumn = '" & mid$(strFile, instrrev(strFile, "\") + 1) & "' WHERE thisColumn = '';"

    izy
    thank you for your help. I may be putting this in the wrong place because it still isnt putting in info the column but where do i put the strFile = mid$(strFile, instrrev(strFile, "\") + 1)? does it replace the linei have or should i put it between strSQL and Docmd.TransferText?
    Last edited by CardinalBrandon; 09-21-05 at 15:16.

  6. #6
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    in full...



    Private Sub ImportAllFiles()
    Dim strfile As String
    dim strSQL as string

    ChDir ("C:/my files/")
    strfile = Dir("C:/my files/*.csv")
    Do While Len(strfile) > 0
    DoCmd.TransferText acImportDelim, "Import Spec", "Table Name", "C:/my files/" & strfile, True
    strSQL = "UPDATE TableName SET thisColumn = '" & mid$(strFile, instrrev(strFile, "\") + 1) & "' WHERE thisColumn = '';"
    currentdb.execute strSQL

    strfile = Dir
    Loop
    End Sub

    ...assuming that table "TableName" includes a field "thisColumn" which is left holding an empty string '' after each transfertext

    the handling of empty "thisColumn" before the loop and re-emptying originally empty "thisColumn" after the loop is a luxury

    izy
    currently using SS 2008R2

  7. #7
    Join Date
    Sep 2005
    Posts
    5

    thanks

    thank you for all your help. I appreciate it greatly but i am getting a syntax error in update statement after pluggin in the formulas... When i click to debug its highlighting the "CurrentDb.Execute strSQL" . This should be something that i should be able to fix over the next few days or over the weekend myself. I Just need to read more into this. Thanks again though i cant express how grateful I am

  8. #8
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    currentdb.execute strSQL works in any access environment as far as i know.

    maybe there's a prob with the single/double quotes in the SQL?
    replacing double-quote with € and single-quote with £ the SQL is
    €UPDATE TableName SET thisColumn = £€ & mid$(strFile, instrrev(strFile, €\€) + 1) & €£ WHERE thisColumn = ££;€

    as a reality check, try something similar on a copy table somewhere. delete a few entries in thisField so you have some empty ones. then
    dim strSQL as string
    strSQL = "UPDATE myCopyTable SET myfield = 'foo' WHERE myField = '';
    currentdb.execute

    put a msgbox in your loop before the currentdb.execute
    msgbox mid$(strFile, instrrev(strFile, "\"),, "That's my proposed filename!"

    also maybe there is a NULL/empty issue.
    experiment with
    strSQL = "UPDATE myCopyTable SET myfield = 'foo' WHERE ((myField = '') or (myfield IS NULL));

    mess around with it - it will work sooner or later.

    izy
    currently using SS 2008R2

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •