Need help on importing an Excel Sheet into Access 2000

[Edward S]

I budget for a Project in an Excel sheet as illustrated below. The
months below are usually a 2 year period i.e. 24 months, though it
could be over 24 months depending upon a Project. I then need to
input this in an Access database, where I do a comparison with the
Actual cost. The table “TblBudget ” in Access is made of 4
fields, namely: (1) CostElement (2) CostCenter (3) Month (4) Amount$.
At the moment this method is very cumbersome. I have to manually
input the data one by one rather than a direct input. The reason
being as my spreadsheet is in a tabulated format. It is more
horizontal and I need to import it in a vertical format, I think
you'll understand what I mean!. Is there an easier way to handle
this. Any help in this regard is greatly appreciate
Regards
Edward

The Excel file goes like this with the following fields:
CostElement CostCenter Jan-03 Feb-03 Mar-3 Apr-03 ....... Dec-04
422100 R3551 $2,000 $3,000 $0 $3,500 $4,200
422103 R3700 $2,260 $3,300 $0 $4,670 $3,500
456700 R3551 $2,270 $3,500 $300 $5,230 $3,500
456705 R3551 $2,300 $2,300 $300 $4,590 $4,500
456708 T3305 $2,400 $4,500 $550 $3,690 $4,500
456800 R3551 $2,260 $0 $450 $0 $3,500
457890 T3305 $2,500 $0 $350 $6,790 $4,500
457895 R3700 $2,000 $2,300 $350 $5,590 $4,500
457900 R3551 $2,700 $3,650 $60 $5,000 $4,500
457905 R3700 $2,650 $5,700 $330 $5,000 $4,500
..
...
..
650200 T3305 $1,000 $3,300 $300 $3,590 $4,500

[Steve Jorgensen]

Re: Need help on importing an Excel Sheet into Access 2000

Actually, you have both vertical and horizontal dimensions in your input.
Essentially you have pivoted data that you need to unpivot. The new SQL
Server will have an unpivot command, but until/unless you're going to get
that, you'll need to write or obtain a tool to do it.

The tool I know of that does this in Access is the Unpivot tool from
http://www.cleandatasystems.com/. i have not used this product, so I can't
vouch for its quality or lack thereof, but I presume it at least works, and
it's got to be easier than writing your own from scratch.

On 5 Feb 2004 03:20:16 -0800, soaresec@qatar. net.qa (Edward S) wrote:
[color=blue]
>I budget for a Project in an Excel sheet as illustrated below. The
>months below are usually a 2 year period i.e. 24 months, though it
>could be over 24 months depending upon a Project. I then need to
>input this in an Access database, where I do a comparison with the
>Actual cost. The table “TblBudget ” in Access is made of 4
>fields, namely: (1) CostElement (2) CostCenter (3) Month (4) Amount$.
>At the moment this method is very cumbersome. I have to manually
>input the data one by one rather than a direct input. The reason
>being as my spreadsheet is in a tabulated format. It is more
>horizontal and I need to import it in a vertical format, I think
>you'll understand what I mean!. Is there an easier way to handle
>this. Any help in this regard is greatly appreciate
>Regards
>Edward
>
>The Excel file goes like this with the following fields:
>CostElement CostCenter Jan-03 Feb-03 Mar-3 Apr-03 ....... Dec-04
>422100 R3551 $2,000 $3,000 $0 $3,500 $4,200
>422103 R3700 $2,260 $3,300 $0 $4,670 $3,500
>456700 R3551 $2,270 $3,500 $300 $5,230 $3,500
>456705 R3551 $2,300 $2,300 $300 $4,590 $4,500
>456708 T3305 $2,400 $4,500 $550 $3,690 $4,500
>456800 R3551 $2,260 $0 $450 $0 $3,500
>457890 T3305 $2,500 $0 $350 $6,790 $4,500
>457895 R3700 $2,000 $2,300 $350 $5,590 $4,500
>457900 R3551 $2,700 $3,650 $60 $5,000 $4,500
>457905 R3700 $2,650 $5,700 $330 $5,000 $4,500
>.
>..
>.
>650200 T3305 $1,000 $3,300 $300 $3,590 $4,500[/color]

[Fletcher Arnold]

Re: Need help on importing an Excel Sheet into Access 2000

"Edward S" <soaresec@qatar .net.qa> wrote in message
news:57703a81.0 402050320.13fd4 6b0@posting.goo gle.com...[color=blue]
> I budget for a Project in an Excel sheet as illustrated below. The
> months below are usually a 2 year period i.e. 24 months, though it
> could be over 24 months depending upon a Project. I then need to
> input this in an Access database, where I do a comparison with the
> Actual cost. The table “TblBudget ” in Access is made of 4
> fields, namely: (1) CostElement (2) CostCenter (3) Month (4) Amount$.
> At the moment this method is very cumbersome. I have to manually
> input the data one by one rather than a direct input. The reason
> being as my spreadsheet is in a tabulated format. It is more
> horizontal and I need to import it in a vertical format, I think
> you'll understand what I mean!. Is there an easier way to handle
> this. Any help in this regard is greatly appreciate
> Regards
> Edward
>
> The Excel file goes like this with the following fields:
> CostElement CostCenter Jan-03 Feb-03 Mar-3 Apr-03 ....... Dec-04
> 422100 R3551 $2,000 $3,000 $0 $3,500 $4,200
> 422103 R3700 $2,260 $3,300 $0 $4,670 $3,500
> 456700 R3551 $2,270 $3,500 $300 $5,230 $3,500
> 456705 R3551 $2,300 $2,300 $300 $4,590 $4,500
> 456708 T3305 $2,400 $4,500 $550 $3,690 $4,500
> 456800 R3551 $2,260 $0 $450 $0 $3,500
> 457890 T3305 $2,500 $0 $350 $6,790 $4,500
> 457895 R3700 $2,000 $2,300 $350 $5,590 $4,500
> 457900 R3551 $2,700 $3,650 $60 $5,000 $4,500
> 457905 R3700 $2,650 $5,700 $330 $5,000 $4,500
> .
> ..
> .
> 650200 T3305 $1,000 $3,300 $300 $3,590 $4,500[/color]




I can't see an easy solution without involving a bit of code. However, that
bit of code would not take an experienced programmer very long at all -
probably 5 minutes to sketch the basic loop and then a while to add
refinements such as error-checking etc. Are you looking for someone to give
you a start with the code or were you hoping for a non-VBA solution?

Fletcher

[Edward S]

Re: Need help on importing an Excel Sheet into Access 2000

"Fletcher Arnold" <fletch@home.co m> wrote in message news:<bvtaqf$9t l$1@sparta.btin ternet.com>...[color=blue]
> "Edward S" <soaresec@qatar .net.qa> wrote in message
> news:57703a81.0 402050320.13fd4 6b0@posting.goo gle.com...[color=green]
> > I budget for a Project in an Excel sheet as illustrated below. The
> > months below are usually a 2 year period i.e. 24 months, though it
> > could be over 24 months depending upon a Project. I then need to
> > input this in an Access database, where I do a comparison with the
> > Actual cost. The table “TblBudget ” in Access is made of 4
> > fields, namely: (1) CostElement (2) CostCenter (3) Month (4) Amount$.
> > At the moment this method is very cumbersome. I have to manually
> > input the data one by one rather than a direct input. The reason
> > being as my spreadsheet is in a tabulated format. It is more
> > horizontal and I need to import it in a vertical format, I think
> > you'll understand what I mean!. Is there an easier way to handle
> > this. Any help in this regard is greatly appreciate
> > Regards
> > Edward
> >
> > The Excel file goes like this with the following fields:
> > CostElement CostCenter Jan-03 Feb-03 Mar-3 Apr-03 ....... Dec-04
> > 422100 R3551 $2,000 $3,000 $0 $3,500 $4,200
> > 422103 R3700 $2,260 $3,300 $0 $4,670 $3,500
> > 456700 R3551 $2,270 $3,500 $300 $5,230 $3,500
> > 456705 R3551 $2,300 $2,300 $300 $4,590 $4,500
> > 456708 T3305 $2,400 $4,500 $550 $3,690 $4,500
> > 456800 R3551 $2,260 $0 $450 $0 $3,500
> > 457890 T3305 $2,500 $0 $350 $6,790 $4,500
> > 457895 R3700 $2,000 $2,300 $350 $5,590 $4,500
> > 457900 R3551 $2,700 $3,650 $60 $5,000 $4,500
> > 457905 R3700 $2,650 $5,700 $330 $5,000 $4,500
> > .
> > ..
> > .
> > 650200 T3305 $1,000 $3,300 $300 $3,590 $4,500[/color]
>
>
>
>
> I can't see an easy solution without involving a bit of code. However, that
> bit of code would not take an experienced programmer very long at all -
> probably 5 minutes to sketch the basic loop and then a while to add
> refinements such as error-checking etc. Are you looking for someone to give
> you a start with the code or were you hoping for a non-VBA solution?
>
> Fletcher[/color]

Fletcher,

As you said it can be done with a bit of code, if someone could give
that solution, I would truly appreciate it. It would save me hours
trying to key punch the stuff manually. Another point I forgot, was I
would ignore importing any zero values in the database.

Regards
Edward

[Fletcher Arnold]

Re: Need help on importing an Excel Sheet into Access 2000

"Edward S" <soaresec@qatar .net.qa> wrote in message
news:57703a81.0 402050807.218d8 3c7@posting.goo gle.com...[color=blue]
> "Fletcher Arnold" <fletch@home.co m> wrote in message[/color]
news:<bvtaqf$9t l$1@sparta.btin ternet.com>...[color=blue][color=green]
> > "Edward S" <soaresec@qatar .net.qa> wrote in message
> > news:57703a81.0 402050320.13fd4 6b0@posting.goo gle.com...[color=darkred]
> > > I budget for a Project in an Excel sheet as illustrated below. The
> > > months below are usually a 2 year period i.e. 24 months, though it
> > > could be over 24 months depending upon a Project. I then need to
> > > input this in an Access database, where I do a comparison with the
> > > Actual cost. The table “TblBudget ” in Access is made of 4
> > > fields, namely: (1) CostElement (2) CostCenter (3) Month (4) Amount$.
> > > At the moment this method is very cumbersome. I have to manually
> > > input the data one by one rather than a direct input. The reason
> > > being as my spreadsheet is in a tabulated format. It is more
> > > horizontal and I need to import it in a vertical format, I think
> > > you'll understand what I mean!. Is there an easier way to handle
> > > this. Any help in this regard is greatly appreciate
> > > Regards
> > > Edward
> > >
> > > The Excel file goes like this with the following fields:
> > > CostElement CostCenter Jan-03 Feb-03 Mar-3 Apr-03 ....... Dec-04
> > > 422100 R3551 $2,000 $3,000 $0 $3,500 $4,200
> > > 422103 R3700 $2,260 $3,300 $0 $4,670 $3,500
> > > 456700 R3551 $2,270 $3,500 $300 $5,230 $3,500
> > > 456705 R3551 $2,300 $2,300 $300 $4,590 $4,500
> > > 456708 T3305 $2,400 $4,500 $550 $3,690 $4,500
> > > 456800 R3551 $2,260 $0 $450 $0 $3,500
> > > 457890 T3305 $2,500 $0 $350 $6,790 $4,500
> > > 457895 R3700 $2,000 $2,300 $350 $5,590 $4,500
> > > 457900 R3551 $2,700 $3,650 $60 $5,000 $4,500
> > > 457905 R3700 $2,650 $5,700 $330 $5,000 $4,500
> > > .
> > > ..
> > > .
> > > 650200 T3305 $1,000 $3,300 $300 $3,590 $4,500[/color]
> >
> >
> >
> >
> > I can't see an easy solution without involving a bit of code. However,[/color][/color]
that[color=blue][color=green]
> > bit of code would not take an experienced programmer very long at all -
> > probably 5 minutes to sketch the basic loop and then a while to add
> > refinements such as error-checking etc. Are you looking for someone to[/color][/color]
give[color=blue][color=green]
> > you a start with the code or were you hoping for a non-VBA solution?
> >
> > Fletcher[/color]
>
> Fletcher,
>
> As you said it can be done with a bit of code, if someone could give
> that solution, I would truly appreciate it. It would save me hours
> trying to key punch the stuff manually. Another point I forgot, was I
> would ignore importing any zero values in the database.
>
> Regards
> Edward[/color]



A couple of important points first. I avoid using reserved words for field
names (month, day, name, etc) and would avoid $ signs at the end especially
when dealing with Excel where the dollar sign can mean something specific.
Therefore my code assumes tblBudget = CostElement, CostCenter, CostMonth,
CostAmount so I have changed your last 2 field names form Month and Amount$.

The code involves a table which is linked to your Excel spreadsheet. This
can be created by File>Get External Data>Link Tables and selecting your
Excel sheet and naming it tblExcel.

**NB**
I have assumed the values you posted 'Jan-03', 'Feb-03' etc are text values
in the spreadsheet and that a date can be calculated by adding "01-" to the
front of this string and then converting it to a date. This will not work
unless these are proper text values.


I have not included the logic to ignore importing any zero values in the
database, but hopefully you can see where to change that. Anyway here is
the code behind the import button's OnClick Event:


Private Sub cmdImport_Click ()

On Error GoTo Err_Handler

Dim wks As DAO.Workspace
Dim dbs As DAO.Database
Dim rstRead As DAO.Recordset
Dim rstWrite As DAO.Recordset
Dim fld As DAO.Field

Dim lngFields As Long
Dim lngCount As Long

Dim strCostElement As String
Dim strCostCenter As String
Dim dteDate As Date
Dim curValue As Currency

Set wks = DBEngine.Worksp aces(0)

Set dbs = CurrentDb

Set rstRead = dbs.OpenRecords et("tblExcel", dbOpenForwardOn ly)

lngFields = rstRead.Fields. Count

Set rstWrite = dbs.OpenRecords et("tblBudget" , dbOpenDynaset,
dbAppendOnly)

wks.BeginTrans

While Not rstRead.EOF

strCostElement = Trim(Nz(rstRead .Fields(0).Valu e, ""))

strCostCenter = Trim(Nz(rstRead .Fields(1).Valu e, ""))

For lngCount = 2 To lngFields - 1

dteDate = CDate("01-" & rstRead.Fields( lngCount).Name)

curValue = CCur(Nz(rstRead .Fields(lngCoun t).Value, 0))

rstWrite.AddNew

rstWrite!CostEl ement = strCostElement

rstWrite!CostCe nter = strCostCenter

rstWrite!CostMo nth = dteDate

rstWrite!CostAm ount = curValue

rstWrite.Update

Next lngCount

rstRead.MoveNex t

Wend

If MsgBox("Commit updates to table?", vbInformation Or vbYesNoCancel, _
"Import Routine") = vbYes Then
wks.CommitTrans
Else
wks.Rollback
End If

Exit_Handler:

On Error Resume Next

If Not rstWrite Is Nothing Then
rstWrite.Close
Set rstWrite = Nothing
End If

If Not rstRead Is Nothing Then
rstRead.Close
Set rstRead = Nothing
End If

If Not dbs Is Nothing Then
Set dbs = Nothing
End If

If Not wks Is Nothing Then
Set wks = Nothing
End If

Exit Sub

Err_Handler:
MsgBox Err.Description , vbExclamation, "Error No: " & Err.Number
Resume Exit_Handler

End Sub

[Edward S]

Re: Need help on importing an Excel Sheet into Access 2000

Hi Fletcher,

Many thanks for your instant reply. I should have said it earlier, the
dates (CostMonth) are not text values they are really dates like
01-05-2003, 01-06-2003 ... 01-02-2009, in the date format "dd-mm-yyyy"
in the spreadsheet and the Access Database. The CostElement field is a
number field (Long integer), the CostCenter field is a Text Field and
the CostAmount field is a number field (Double) in the Access Database
Can you please help me make the necessary changes to the code. I
currently get an error message # 13, which says "Type mismatch"

I would appreciate your kind help

Edward



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Steve Jorgensen]

Re: Need help on importing an Excel Sheet into Access 2000

Well OK, that's true. I'm always envisioning general solutions when specific
wones will do. I'm working on not making things harder than they really need
to be, but it's a long road <g>.

On Fri, 06 Feb 2004 00:23:38 GMT, Chuck Grimsby
<c.grimsby@worl dnet.att.net.in valid> wrote:
[color=blue]
>
>With *that* layout, and since it's in a Excel spreadsheet, nothing
>like that is really required, Steve.
>
>Just linking to the table and 24 nice little queries UNIONed together
>should import the data quite nicely... Course, considering it's
>comming from Excel (not to mention 24 queries UNIONed together) you'd
>probably want to start it up just as you head out the door for lunch!
>
>
>On Thu, 05 Feb 2004 11:32:30 GMT, Steve Jorgensen
><nospam@nospam .nospam> wrote:
>[color=green]
>>Actually, you have both vertical and horizontal dimensions in your input.
>>Essentially you have pivoted data that you need to unpivot. The new SQL
>>Server will have an unpivot command, but until/unless you're going to get
>>that, you'll need to write or obtain a tool to do it.
>>
>>The tool I know of that does this in Access is the Unpivot tool from
>>http://www.cleandatasystems.com/. i have not used this product, so I can't
>>vouch for its quality or lack thereof, but I presume it at least works, and
>>it's got to be easier than writing your own from scratch.
>>
>>On 5 Feb 2004 03:20:16 -0800, soaresec@qatar. net.qa (Edward S) wrote:
>>[color=darkred]
>>>I budget for a Project in an Excel sheet as illustrated below. The
>>>months below are usually a 2 year period i.e. 24 months, though it
>>>could be over 24 months depending upon a Project. I then need to
>>>input this in an Access database, where I do a comparison with the
>>>Actual cost. The table “TblBudget ” in Access is made of 4
>>>fields, namely: (1) CostElement (2) CostCenter (3) Month (4) Amount$.
>>>At the moment this method is very cumbersome. I have to manually
>>>input the data one by one rather than a direct input. The reason
>>>being as my spreadsheet is in a tabulated format. It is more
>>>horizontal and I need to import it in a vertical format, I think
>>>you'll understand what I mean!. Is there an easier way to handle
>>>this. Any help in this regard is greatly appreciate
>>>Regards
>>>Edward
>>>
>>>The Excel file goes like this with the following fields:
>>>CostElemen t CostCenter Jan-03 Feb-03 Mar-3 Apr-03 ....... Dec-04
>>>422100 R3551 $2,000 $3,000 $0 $3,500 $4,200
>>>422103 R3700 $2,260 $3,300 $0 $4,670 $3,500
>>>456700 R3551 $2,270 $3,500 $300 $5,230 $3,500
>>>456705 R3551 $2,300 $2,300 $300 $4,590 $4,500
>>>456708 T3305 $2,400 $4,500 $550 $3,690 $4,500
>>>456800 R3551 $2,260 $0 $450 $0 $3,500
>>>457890 T3305 $2,500 $0 $350 $6,790 $4,500
>>>457895 R3700 $2,000 $2,300 $350 $5,590 $4,500
>>>457900 R3551 $2,700 $3,650 $60 $5,000 $4,500
>>>457905 R3700 $2,650 $5,700 $330 $5,000 $4,500
>>>.
>>>..
>>>.
>>>650200 T3305 $1,000 $3,300 $300 $3,590 $4,500[/color][/color][/color]

[Fletcher Arnold]

Re: Need help on importing an Excel Sheet into Access 2000

"Edward S" <soaresec@devde x.com> wrote in message
news:40233423$0 $201$75868355@n ews.frii.net...[color=blue]
> Hi Fletcher,
>
> Many thanks for your instant reply. I should have said it earlier, the
> dates (CostMonth) are not text values they are really dates like
> 01-05-2003, 01-06-2003 ... 01-02-2009, in the date format "dd-mm-yyyy"
> in the spreadsheet and the Access Database. The CostElement field is a
> number field (Long integer), the CostCenter field is a Text Field and
> the CostAmount field is a number field (Double) in the Access Database
> Can you please help me make the necessary changes to the code. I
> currently get an error message # 13, which says "Type mismatch"
>
> I would appreciate your kind help
>
> Edward[/color]


I assume the error comes here:
dteDate = CDate("01-" & rstRead.Fields( lngCount).Name)
Which you can check by setting a breakpoint and stepping through the code
line by line.

As I mentioned, your Excel row headers Jan-03 Feb-03 do need to be text. If
the top line contains actual dates, the linked table would have illegal
field names (I guess because a decimal point is involved), so to avoid this,
Acess replaces the field names with valid ones such as F1, F2, etc. Check
this by looking at your linked table. Therefore this would effectively
remove the date information which you need for the import.
The quick answer is to change it, and make sure that these are real text
values held in Excel.

If CostElement is a long integer, a few lines need changing:

Private Sub cmdImport_Click ()

On Error GoTo Err_Handler

Dim wks As DAO.Workspace
Dim dbs As DAO.Database
Dim rstRead As DAO.Recordset
Dim rstWrite As DAO.Recordset
Dim fld As DAO.Field

Dim lngFields As Long
Dim lngCount As Long

Dim lngCostElement As Long
Dim strCostCenter As String
Dim dteDate As Date
Dim curValue As Currency

Set wks = DBEngine.Worksp aces(0)

Set dbs = CurrentDb

Set rstRead = dbs.OpenRecords et("tblExcel", dbOpenForwardOn ly)

lngFields = rstRead.Fields. Count

Set rstWrite = dbs.OpenRecords et("tblBudget" , dbOpenDynaset,
dbAppendOnly)

wks.BeginTrans

While Not rstRead.EOF

lngCostElement = Trim(Nz(rstRead .Fields(0).Valu e, ""))

strCostCenter = Trim(Nz(rstRead .Fields(1).Valu e, ""))

For lngCount = 2 To lngFields - 1

dteDate = CDate("01-" & rstRead.Fields( lngCount).Name)

curValue = CCur(Nz(rstRead .Fields(lngCoun t).Value, 0))

rstWrite.AddNew

rstWrite!CostEl ement = lngCostElement

rstWrite!CostCe nter = strCostCenter

rstWrite!CostMo nth = dteDate

rstWrite!CostAm ount = curValue

rstWrite.Update

Next lngCount

rstRead.MoveNex t

Wend

If MsgBox("Commit updates to table?", vbInformation Or vbYesNoCancel, _
"Import Routine") = vbYes Then
wks.CommitTrans
Else
wks.Rollback
End If

Exit_Handler:

On Error Resume Next

If Not rstWrite Is Nothing Then
rstWrite.Close
Set rstWrite = Nothing
End If

If Not rstRead Is Nothing Then
rstRead.Close
Set rstRead = Nothing
End If

If Not dbs Is Nothing Then
Set dbs = Nothing
End If

If Not wks Is Nothing Then
Set wks = Nothing
End If

Exit Sub

Err_Handler:
MsgBox Err.Description , vbExclamation, "Error No: " & Err.Number
Resume Exit_Handler

End Sub

[Edward S]

Re: Need help on importing an Excel Sheet into Access 2000


Hi Fletcher,

I want to thank you so much, It has worked for me. I am planning to put
this as part of the main form.

Truly grateful

Regards
Edward


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Fletcher Arnold]

Re: Need help on importing an Excel Sheet into Access 2000

"Edward S" <soaresec@devde x.com> wrote in message
news:40237832$0 $200$75868355@n ews.frii.net...[color=blue]
>
> Hi Fletcher,
>
> I want to thank you so much, It has worked for me. I am planning to put
> this as part of the main form.
>
> Truly grateful
>
> Regards
> Edward[/color]


You're welcome, Edward. "Thanks I've got it working now" is a hundred times
better than "Thanks in advance" where you never know if your reply has even
been read.

Fletcher