Converting Non-Standard Excel Data into Access

[HIT Engineering]

I have two sets up excel data for every day over the last 5 years at multiple
locations. The access form I want to create will have 4 Fields, Location,
Date, Value 1, Value 2. The spreadsheet is in the following format. Each
month has its own tab named according with year. The first column contains
the location name. Columns 2,3 contain data for the 1st day of the month,
Value 1 Day 1, Value 2, Day 1, than the columns continue...column 4,5 contain
the 2nd day, 6,7 contain the 3rd day...

Any suggestions for the best way to create this Import?

 

[John Nurick]

I'd do this by writing VBA code to read the workbooks and generate and
execute a series of SQL queries, each importing one day's data.
Pseudocode (assuming your Access table is tblT with fields Location,
TheDate, Value1, Value2:

Dim j As Long
Dim dtD As Date
Dim strSQL As String
Dim dbD as DAO.Database

Set dbD = CurrentDB()
For Each Workbook
For Each Worksheet
For j = 2 to Worksheet.UsedRange.Columns.Count - 1 Step 2
dtD = DateSerial(values from sheet name and column number)
strSQL = [here you build an append query, see below]
dbD.Execute strSQL, dbFailOnError
Next j
Next 'Worksheet
Next 'Workbook

I'd use Automation to open each workbook so as to be able to iterate
through the worksheets and get the number of columns in each UsedRange,
although it is also possible to do this by means of cunning DAO queries
and recordset operations.

The query to be built for each pair of columns will look rather like
this:

INSERT INTO tblT
SELECT #mm/dd/yyyy# As Date,
F1 AS Location,
FXX AS Value1,
FYY AS Value2
FROM [Excel 8.0;HDR=No;database=C:\MyWorkbook.xls;].[ZZZ$]

where mm/dd/yyyy is the date, XX = j (the column for Value 1), YY = j+1
(the column for value 2), and ZZZ is the name of the worksheet.



On Sat, 2 Apr 2005 20:53:01 -0800, HIT Engineering <HIT

 

[HIT Engineering]

This has been helpful, I have a few followup questions though.

Upon analyzing the worksheet names i've realized i can not use them for
directly getting the month and year because of spelling errors over the
years, ie janaury04. These problems i can figure out once i have all the
data into access though. So I have rethough the access table into 6 fields,
Location, WorkBookRef, WorksheetRef, Day, Value1, Value2. Where WorkSheetRef
and WorkBookRef are the actual names of the worksheet the dataset came from.
Is it possible to get a list of all worksheets in a workbook, and then loop
through those? Than the Day value would correspond to the j value,
specifically j/2. I am not sure what you mean by "I'd use Automation to open
each workbook".

I am close to understanding a solution to this but I am not fully competant
in vba, so help with the coding is appreciated. Otherwise I'll use the guess
and check/look up code method until i get everything working.

I have 5 seperate files I will be using, I was hoping to loop through the 5
files by possibly putting the filenames in an array.

 

[HIT Engineering]

I've been working on this and I think I am close, here is the code I am using:

Function ExcelImportResiduals()
Dim XLapp As Object
Dim XLFile As String
Dim XLSheet As String
Dim XLRange As String
Dim TableName As String
Dim z As Integer
Dim SheetCount As Integer
Dim XX As Integer
Dim YY As Integer
Dim TheDay As Integer
Set XLapp = GetObject(, "Excel.Application")
XLapp.Visible = True 'Excel is visible!! or if False not
visible!!
XLFile = "t:\Desktop\d\Monmouth\03.xls" 'Your File
TableName = "ResidualData" 'Table to import into
XLRange = "!" 'Specifies the area to be imported,
if you just enter
XX = j
JJ = j + 1
TheDay = j / 2
Set XLwb = XLapp.Workbooks.Open(XLFile) 'Opens your file in Excel
SheetCount = XLapp.activeworkbook.Sheets.Count 'Gives you the total
number of Sheets
For z = 1 To SheetCount
XLSheet = XLapp.activeworkbook.Sheets(z).Name 'get name of sheet
number z
XLSheet = XLSheet & XLRange 'add range to
sheetname

For j = 2 To (XLapp.activeworkbook.Sheets(z).UsedRange.Columns.Count
- 1) Step 2
strSQL = "INSERT INTO" & TableName & "SELECT " & F1 & "As
Location, F" & XX & " As Value1, F" & YY & " As Value2, " & XLSheet & " As
MYear, " & TheDay & " As Day FROM [Excel 8.0;HDR=No;database=" & XLFile &
";].[" & XLSheet & "]"
dbD.Execute strSQL, dbFailOnError
Next j
Next z
MsgBox "Imported Successfully "
XLapp.Quit
Set XLapp = Nothing
Set XLwb = Nothing

End Function


My problem (at least one of them) is in my strSQL, I am not sure how to
write that so that value1 comes from excel column j and value 2 comes from
excel column j +1
Also the format of the strSQL is probably off.

Any help?

 

[John Nurick]

The easiest way to loop through each workbook in a folder and each
worksheet in the workbook is usually like this:

Dim strFileName As String
Dim strFolder As String
Dim oExcel As Excel.Application
Dim oBook As Excel.Workbook
Dim oSheet As Excel.Worksheet
Dim strBookName As String
Dim strSheetName As String

'Launch Excel
Set oExcel = CreateObject("Excel.Application")

strFolder = "C:\Folder\Subfolder\"
'get first file in folder
strFileName = Dir(strFolder & "*.xls")

Do While Len(strFileName) > 0
'Open workbook
Set oBook = oExcel.Workbooks.Open(strFolder & strFileName)
strBookName = oBook.Name
For Each oSheet in oBook.Worksheets
strSheetName = oSheet.Name
'build and execute query

Next
oBook.Close False 'close without saving
strFileName = Dir()
Loop

'Ensure that no other workbooks are left open
'(e.g. by add-ins)
Set oBook = Nothing
Do While oExcel.Workbooks.Count > 0
oExcel.Workbooks(1).Close False)
Loop
oExcel.Quit
Set oExcel = Nothing

 

[John Nurick]

You're getting close. BTW, the code you posted shouldn't compile: make
sure to include
Option Explicit
at the beginning of every code module.


With six columns in your destination table the actual SQL statements
will look like this. Note the apostrophes to delimit literal text values
such as the workbook and worksheet names:

INSERT INTO tblT
SELECT F1 AS Location,
'WWW' As WorkbookRef
'ZZZ' As WorksheetRef
DD As Day,
FXX AS Value1,
FYY AS Value2
FROM [Excel 8.0;HDR=No;database=C:\MyWorkbook.xls;].[ZZZ$]

in each case WWW will be replaced by the name of the workboook, ZZZ by
the name of the sheet, DD by the day of the month, and XX and YY by the
column numbers for that day.

The code to generate it will look like something like this (untested).
Note that you have to include spaces or linebreaks to separate the
"words":

strSQL = "INSERT INTO " & TableName & vbCrLf _
& "SELECT " & F1 & "AS Location, " & vbCrLf _
& "'" & XLwb.Name & "' AS WorkbookRef, " & vbCrLf _
& "'" & XLSheet & "' AS WorksheetRef, " & vbCrLf _
& CStr(j/2) & " AS Day, " & vbCrLf _
& "F" & CStr(j) & " As Value1, " & vbCrLf _
& "F" & CStr(j+1) & " As Value2 " & vbCrLf _
& "FROM [Excel 8.0;HDR=No;database=" & XLFile _
& ";].[" & XLSheet & "]"

I've been working on this and I think I am close, here is the code I am using:

Function ExcelImportResiduals()
Dim XLapp As Object
Dim XLFile As String
Dim XLSheet As String
Dim XLRange As String
Dim TableName As String
Dim z As Integer
Dim SheetCount As Integer
Dim XX As Integer
Dim YY As Integer
Dim TheDay As Integer
Set XLapp = GetObject(, "Excel.Application")
XLapp.Visible = True 'Excel is visible!! or if False not
visible!!
XLFile = "t:\Desktop\d\Monmouth\03.xls" 'Your File
TableName = "ResidualData" 'Table to import into
XLRange = "!" 'Specifies the area to be imported,
if you just enter
XX = j
JJ = j + 1
TheDay = j / 2
Set XLwb = XLapp.Workbooks.Open(XLFile) 'Opens your file in Excel
SheetCount = XLapp.activeworkbook.Sheets.Count 'Gives you the total
number of Sheets
For z = 1 To SheetCount
XLSheet = XLapp.activeworkbook.Sheets(z).Name 'get name of sheet
number z
XLSheet = XLSheet & XLRange 'add range to
sheetname

For j = 2 To (XLapp.activeworkbook.Sheets(z).UsedRange.Columns.Count
- 1) Step 2
strSQL = "INSERT INTO" & TableName & "SELECT " & F1 & "As
Location, F" & XX & " As Value1, F" & YY & " As Value2, " & XLSheet & " As
MYear, " & TheDay & " As Day FROM [Excel 8.0;HDR=No;database=" & XLFile &
";].[" & XLSheet & "]"
dbD.Execute strSQL, dbFailOnError
Next j
Next z
MsgBox "Imported Successfully "
XLapp.Quit
Set XLapp = Nothing
Set XLwb = Nothing

End Function


My problem (at least one of them) is in my strSQL, I am not sure how to
write that so that value1 comes from excel column j and value 2 comes from
excel column j +1
Also the format of the strSQL is probably off.

Any help?

 

[HIT Engineering]

Everything is set up, except I am having trouble with the sql statement

The error I get is Run-time error '3067'
Query input must contain at least one table or query.

an example of how the sql statment it recieves is as follows:

INSERT INTO Residual Data
SELECT F1 AS Location,
'Cl2 Residuals 2000-2003.xls' AS FileRef
'JAN 2000!' AS MYear,
'1' AS TheDay,
F2 AS Value1,
F3 AS Value2
FROM [Excel 8.0;HDR=No;database=t:\desktop\Databases\NoPH\3.xls;].[JAN 2000!]


Question 1: Is F1 and F2 what I want to describe each excel column or should
that be something else.

Question 2: The ! after JAN 2000! I am using as the range in the excel
sheet, the actual sheet name is JAN 2000, Is this correct?

 

[John Nurick]

I'd try a $ instead of ! after the sheet name; I can never remember
which is needed when.

Everything is set up, except I am having trouble with the sql statement

The error I get is Run-time error '3067'
Query input must contain at least one table or query.

an example of how the sql statment it recieves is as follows:

INSERT INTO Residual Data
SELECT F1 AS Location,
'Cl2 Residuals 2000-2003.xls' AS FileRef
'JAN 2000!' AS MYear,
'1' AS TheDay,
F2 AS Value1,
F3 AS Value2
FROM [Excel 8.0;HDR=No;database=t:\desktop\Databases\NoPH\3.xls;].[JAN 2000!]


Question 1: Is F1 and F2 what I want to describe each excel column or should
that be something else.

Question 2: The ! after JAN 2000! I am using as the range in the excel
sheet, the actual sheet name is JAN 2000, Is this correct?

 

[HIT Engineering]

I nowhave the sql working partially. I am able to import the location,
value1 and value 2, using this code: (The $ did help instead of !)

strsql = "INSERT INTO " & TableName & vbCrLf _
& "SELECT " & "F1 " & "AS Location, " & vbCrLf _
& "F" & XX & " AS Value1," & vbCrLf _
& "F" & YY & " AS Value2 " & vbCrLf _
& "FROM [Excel 8.0;HDR=No;database=" & strFolder & strFileName _
& ";].[" & XLSheet & "]"

The problem comes now when I try to attach my created values (book name,
sheet name, and column number). I want the value to be part of each record
in access. I dont know if using select would be correct because I am not
selecting anything from the excel table that I am giving it. I need to do a
combination of SELECT and VALUES. i've tried a few things, can't get it.

This is what was originally suggested: (included with the original code)

& "'" & strBookName & "' AS FileRef " & vbCrLf _
& "'" & XLSheet & "' AS MYear, " & vbCrLf _
& "'" & ColumnCount & "'" & " AS TheDay, " & vbCrLf _

If I run that I get
Query input must contain at least one table or query.
as before.

 

[John Nurick]

INSERT ... SELECT is what you need because you're selecting records from
the Excel sheet, plus using calculated fields to generate additiona
field values to go into those records. INSERT ... VALUES is only for
single-record append queries.

The code snippet at the end of your post makes me think you're getting
the commas wrong in the field list. Also, is ColumnCount a string or a
numeric data type? If numeric, lose the quote marks.

& "'" & strBookName & "' AS FileRef " & vbCrLf _
& "'" & XLSheet & "' AS MYear, " & vbCrLf _
& "'" & ColumnCount & "'" & " AS TheDay, " & vbCrLf _

If (for example) strBookName is "XXX", XLSheet is "2004" and ColumnCount
is 15, this would come out as

'XXX' AS FileRef
'2004' AS MyYear,
15 As TheDay,

i.e. missing a comma after FileRef. Similarly make sure there's no comma
after the last field in the list (i.e. before the FROM clause).

I nowhave the sql working partially. I am able to import the location,
value1 and value 2, using this code: (The $ did help instead of !)

strsql = "INSERT INTO " & TableName & vbCrLf _
& "SELECT " & "F1 " & "AS Location, " & vbCrLf _
& "F" & XX & " AS Value1," & vbCrLf _
& "F" & YY & " AS Value2 " & vbCrLf _
& "FROM [Excel 8.0;HDR=No;database=" & strFolder & strFileName _
& ";].[" & XLSheet & "]"

The problem comes now when I try to attach my created values (book name,
sheet name, and column number). I want the value to be part of each record
in access. I dont know if using select would be correct because I am not
selecting anything from the excel table that I am giving it. I need to do a
combination of SELECT and VALUES. i've tried a few things, can't get it.

This is what was originally suggested: (included with the original code)

& "'" & strBookName & "' AS FileRef " & vbCrLf _
& "'" & XLSheet & "' AS MYear, " & vbCrLf _
& "'" & ColumnCount & "'" & " AS TheDay, " & vbCrLf _

If I run that I get
Query input must contain at least one table or query.
as before.

 

[HIT Engineering]

Thanks John!
Working perfectly. I built a few quick things in to monitor progress, and I
am noticing this will take a very long time, and doesnt have to take nearly
as long.

You have an idea of the data in the excel sheets i believe. Basically, over
the last few years at hundreds of sites, I have values about every week and
some extras for retestings. So there are a lot of null values. value1 and
value 2 will always be filled in if anything is inputed. So if I could
include some sort of "select where value1 is not null" into the sql I could
cut down the run time a whole lot. Otherwise I'll just let it run tommorow
night, and then trim things up in access.

 

[John Nurick]

Try doing just that. Add a WHERE clause to the SQL statement after the
FROM clause, e.g.

.... WHERE Value1 IS NOT NULL
or
.... WHERE Len(Nz([Value1], "") > 0

The second catches not just nulls but also empty strings.