import multiple excel files with FILTERED Excel spreasheet into an access table (1 Viewer)

WuJu

Registered User.
Local time
Today, 01:21
Joined
Sep 15, 2010
Messages
18
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
 
Last edited:

Mr. B

"Doctor Access"
Local time
Today, 03:21
Joined
May 20, 2009
Messages
1,932
The simplest solution would be to import the data from the Excel file to a temporary table. Then have am append query that will extract the data (rows and columns) you need to the table that you would then use in your application. Once you have used the append query to append the desired records to your table, then you simple have another delete query that will empty the temporary table. All you have to do is then repeat this process until you have process all of the Excel files.
 

WuJu

Registered User.
Local time
Today, 01:21
Joined
Sep 15, 2010
Messages
18
hello Mr. B

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 okay for your method since excel has first row with good index such as ID, month, year, ....

For my second project, I don't have good index row in excel format.
Someone built without good index format. There is no common format, so has to use code to find some string at column line and grasp that row.

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

So if you know how to code in visual basic to filter with string then grasp that row, please help me out.

regards,


second project's excel format.
WJ



Power Price
today 10/18/2011
power
forward North South
period A B C D E F G H
onpk offpk onpk off pk ....
Day Ah
Week Ah
Bal month

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

WuJu

Registered User.
Local time
Today, 01:21
Joined
Sep 15, 2010
Messages
18
Hello,

I redirect the question, so that a little more clear.

I want to know how link to the Excel spreadsheet and then create an append query that selects columns from the linked Excel spreadsheet and appends them to the columns you select in the Access table?

I have about 750 excel files(each file has one worksheet) in a directory to work on.

e.g. in English

1. link(import) a file at C:\Dir1\file1.xlsx (for example worksheet has 10 coulmns and 1000 2. rows and first column is ID)
3.Append selected rows and columns (if 1st column (ID) = AA or BB or CC, then copy (Append data at column 7 and 9)
4. loop until all 750 files are linked (imported)


Do you think we can do this with visual basic?

regards,

WJ
 

Mr. B

"Doctor Access"
Local time
Today, 03:21
Joined
May 20, 2009
Messages
1,932
The short answer is "Yes". Everything you are needing to do can be automated using VBA code.

I do not have the time to write it because the will be quite a bit of development and testing time required to make it work correctly, but is is very much something that can be done.
 

Users who are viewing this thread

Top Bottom