How do I Import Multiple Excel Files' Data Using Filters

[WU JU]

Hi.

I have thousands of excel files in one directory. I want to build up the Access table from excel files.
Each excel file has one worksheet, but I don't need every column and low of the worksheet.

I am trying to import a FILTERED Excel worksheet of every excel files in that directory into an access table using visual basic code.


I was able to build up (someone helped me) visual basic code to import multiple excel files to one single Access table without FILTERING.

Someone can say that I can just import without filtering and do query in Access.
The problem is that each excel worksheet has too many row and columns that I don't need it, and I need to import thousands of excel files to one access table. Eventually I will face Access table size problem.


I need to improve my visual basic code to import only selected rows and columns that I want to import into Access table.

my worksheet is in the following format

ID Month Year Day OPEN HIGH LOW ...VOL..... SETTLE TradeDate.
OD 7 2011 15 ................... 546 7/15/2011
OD 8 2011 18 ..................658 7/15/2011
..
FD 7 2011 15 .................... 987 7/15/2011
..
..
SM 7 2011 15 .................... 632 7/15/2011
...
KW 7 2011 15 .................... 838 7/15/2011
....

All I need to import is to find "FD" and "SM" in the 1st column (ID) and get the rest of the row (or even better one is to get only ID, Month, Year, Day, Settle, and Tradedate data in that row)

Anyone could help me to filter the data in worksheet and import into Access table?
(I use Access 2007 version)

thank you very much.


FYI, the following is the visual basic code that WORKS for importing multiple excel files to one single Access table without FILTERING.

Sub Import_multiple_excel_files()
 
Const strPath As String = "C:\Price Data\NYMEX\2011_test" 'Directory Path
Dim strFile As String 'Filename
Dim strFileList() As String 'File Array
Dim intFile As Integer 'File Number
 
'Loop through the folder & build file list
'strFile = Dir(strPath & "*.csv")
strFile = Dir(strPath & "*.xlsx")
 
While strFile <> ""
'add files to the list
intFile = intFile + 1
ReDim Preserve strFileList(1 To intFile)
strFileList(intFile) = strFile
strFile = Dir()
Wend
'see if any files were found
If intFile = 0 Then
MsgBox "No files found"
Exit Sub
End If
'cycle through the list of files & import to Access
'creating a new table called importedLMP_t
For intFile = 1 To UBound(strFileList)
DoCmd.TransferText acImportDelimi, , _
"importedNYMEXinAccess", strPath & strFileList(intFile), True
'Check out the TransferText/TransferSpreadsheet options in the Access
'Visual Basic Help file for a full description & list of
'optional settings
Next
MsgBox UBound(strFileList) & " Files were Imported"
End Sub

[NeoPa]

Have you considered a linked table that links to an Excel workbook and whose link you change each time a new workbook is required. That way you can copy the data across to your proper table using an APPEND query based on the linked Excel table and filtered to include only "FD" & "SM" type records. You can specify whichever columns you want/need in that scenario too.

[jimatqsi]

I agree with NeoPa. I think I would assume a constant name for the input Excel file rather than make a new link for every Excel sheet.

To set this up, copy one of your Excel files to file name ExcelFile.xls. Then Create an ODBC link in your .mdb to that file. Then, replace line 27/28 of your code with this:

    ' copy each new .xls to the common name of ExcelFile.xls
    FileCopy strPath & strFileList(intFile), strPath & "ExcelFile.xls"
    For Each tbl In CurrentDb.TableDefs
        If tbl.Name = "ExcelFile" Then
            tbl.RefreshLink  ' renew the ODBC link
            Exit For
        End If
    Next

Now you can open table ExcelFile with whatever filter you want and append the data to importedNYMEXinAccess or some other table before you loop around to import the next Excel file.

Make sure you exclude ExcelFile.xls from your processing if you are copying it to the same folder as all of your original Excel files.

Jim

[jimatqsi]

I believe that For/Next loop could be replaced with

CurrentDb.TableDefs("ExcelFile").RefreshLink

assuming the name of your link (not the name of the excel file) is ExcelFile.

Jim

[WU JU]

hello Jim and NeoPa

Thank you for the reply.

I have two projects that I need to import multiple excel files with FILTERED Excel spreasheet into an access table.

One I described yesterday is easier one.

For my second project, I don't have good index row in excel format (not like yesterday's example).
Excel files are created without good index format.

I attached my second project's excel format. As you see, there is no 1st row that determine the index things like ID, month, ...
I guess I have to look for string "Jan", "Feb", ...."Dec" from the first column, then grasp the rows that has "Jan", "Feb", ....or "Dec" at the first column. I also have thousands of files in one directory, so I rather automate instead of modify each to get right format for importing.

Do you think even this case I can use link method to do that?

Originally I thought that I create one kind of visual basic to use both case with only minor changes. So finding string like '"FD" or "SM" (for first project), OR "Jan" "Feb",...or "Dec" (for second project), and filter and import to Access is useful method to fulfill both projects.



So if you know how to code in visual basic to filter with string and import OR you think link method works both projects, or you have better idea of any to fulfill both of my projects, then please help me out.

Regards,

WJ



FYI, second project's excel format is below.
As you see the below, it is not well formatted. Someone just built to show many information, but not in built-in data format. (there is no index row to determine Day Ah, Week AH, Bal Month, Nov, Dec, ..) In fact "Forward", "Period" are kind of index for "north" and "south", and "A" "B" "C" "D", but they are in first column with Day Ah, Week AH, Bal Month,Nov, Dec, ...
Worst of all, every file has different orders of months. Some file start with Jan thru Dec, other file start with Nov thru Oct, and so on in 1st column. Also each file has deferment blank between Period and Day Ah.
Plus first couple rows have some information that are not directly useful in data point of view.


Power Price
today 10/18/2011

North South
forward A B C D period onpk offpk onpk offpk onpk offpk onpk offpk


Day Ah 38 41 32 45 43 33 55 46
Week Ah 32 45 43 33 55 46 33 24
Bal month 33 55 46 33 24 43 43 32

Nov-11 38 41 32 45 43 33 55 46
Dec-11 .....
Jan-12 .....
....

[NeoPa]

I cannot say that working with such files will work well, but I would imagine that linking them gives you as good a chance of working with them as any other.

How easy it is after that really depends on the format you find in there and how well it is able to be manipulated or recognised.

[WU JU]

Thanks.
Then I will try to use link method.

** Snip **

Thanks NeoPa and Jim for your help.

WJ

[NeoPa]

I've removed the new question from your post as asking new questions is not allowed within an existing thread.

Before posting your new question in a separate thread give consideration to what you may ask and what you may not (There are prominent threads available to guide you on this). We cannot allow simple questions wher you provide the spec for a job. You need to ask specific questions to help you with the work you're doing.

[WU JU]

Hello Jim,

I tried your code to create an ODBC link in my .accdb to ExcelFile.xlsx file. I was in "Select Data Source" step, then chose "File Datra Source", then "Look in" to the dircetory that all the excel files exist, then there is no furture step that I could go. Do I need to choose Machine Data Sourse instead? or how do I link my .mdb to ExcelFile.xlsx file?

My access is in .accdb not .mdb, is it going to be problem?

I appreciate your help.

WJ

[NeoPa]

JimatQSI:
Then Create an ODBC link in your .mdb to that file.

Jim's code doesn't create the link for you. It assumes you already have one created (manually) as instructed in the post.

The code ensures that the newly copied file is enabled via the link.

NB. Don't forget he suggested replacement code in a later post which is far more efficent and neat than the original. You should be using that version rather than the original.

[WU JU]

Hello NeoPa,

I mean I tried create an ODBC link in my .accdb to ExcelFile.xlsx file before I use Jim's code.
I went to "External Data" my Access toolbar, clicked ODBC Database. I was in "Select Data Source" step, then chose "File Datra Source", then "Look in" to the dircetory that all the excel files exist, then there is no furture step that I could go. I also clicked to Machine Data Sourse instead, but no clue I have. So could you please show me how to link my .accdb to ExcelFile.xlsx file? I am pretty new to Access and VB, so I need more detail information.

WJ

[NeoPa]

If I say you need to be linking to a Microsoft Excel file (*.xls, *.xlsx) instead of an ODBC Databases file () then I expect you can take it from there quite easily.

[jimatqsi]

Wu Ju,
I apologize, I made a mistake by referencing an ODBC connection. If you choose the Excel file type in the first step then just browse to the file you want. It's a little more simple than ODBC connections.

Jim

[NeoPa]

LoL Jim.

I didn't even notice that when I read your post. I should have picked up on it earlier. I suppose thinking of things without actually having it in front of you does allow for some mistakes to creep in.