Is it possible to create Import Spec for an Excel Spreadsheet?

Is it possible to create an ImportSpec for an excel file using Access
2003? I currently have an excel spreadsheet which is imported into my
DB on a daily basis consisting of several hundred transactionsl; of
late we began trading treasuries whose prices are entered as "xxx-
xx". I tried importing directly into a table with all field set as
text but at completion those records with treasury prices entered as
"xxx-xx" are stripped out. I've used ImportSpecs with .csv files
before and am unaware if the same is possible .xls files? Any
assistance with this is greatly appreciated. Thank you all in advance.

Re: Is it possible to create Import Spec for an Excel Spreadsheet?

Here is one way to import data from Excel into Access -- using Office
Automation programming. You will have to manually create a table in
Access that matches the columns in the Excel file(s) -- same number of
columns, same data types (this part could be a little bit tricky -
matching the columns to the datatypes). Then make a reference in the
Access Tools/References (from a code module) to the Microsoft Excel
Object Library.

Sub getExcelUsedRange()
Dim strPath As String, RetVal As Variant
Dim XL As New Excel.Application
Dim sht As Excel.Worksheet, rng As Excel.Range
Dim RS As DAO.Recordset, i As Integer, j As Integer

strPath = CurrentProject.Path
Set RS = CurrentDb.OpenRecordset("tbl_Excel")
XL.Workbooks.Open strPath & "\testExcel.xls"
Set sht = XL.Worksheets("Sheet2")
Set rng = sht.UsedRange
DoEvents
For i = 2 To rng.Rows.Count
RS.AddNew
For j = 1 To rng.Columns.Count
RS(j - 1) = rng(i, j)
Next
RS.Update
RetVal = SysCmd(acSysCmdSetStatus, i)
Next
RS.Close
XL.Quit
Set XL = Nothing
End Sub

Note: the Excel Range object (rng) begins its counts at 1, while the
DAO.Recordset object (RS) begins its counts at 0, so you have to
subtract 1 in the columns loop. The Retval thing will display the
record number in the Access Statusbar at the lower left corner of the
screen. AFter it is finished - you have to Quit Excel or else the Excel
exe will keep running in the background and you have to bring up
taskManager to end the Excel Process. XL.Quit prevents that from
happening.

Rich

*** Sent via Developersdex http://www.developersdex.com ***

Re: Is it possible to create Import Spec for an Excel Spreadsheet?

Thanks Rich,

I apologize but I think my description of the problem I was having was
a bit vague. Importing is not the issue. I have a module similar to
yours that brings imports the data from Excel into a pre-exisiting
table in Access. The problem is althrough all of the datatypes within
teh table in Access are set to TEXT to capture all incoming data the
EXCEL spreadsheet, in particular the PRICE column has a mix of both
NUMERIC and TEXT data. The TEXT data in this instance being the way
Treasury prices are represented (xxx-xx).

To continue, the import completes successfully including those records
with Treasury prices, excluding ONLY prices for entered as "xxx-xx".
My question is; Is there a way to bring in prices entered as "xxx-xx"
as well?

Re: Is it possible to create Import Spec for an Excel Spreadsheet?

Hi AccessGuy,

I have to work with data in a similar fashion provided by a syndicated
data provider. I dont get any real choice in the way the data is
represented structurally. The only way that I could successfully
achieve a sustainable solution was to create a simple ETL type process
to parse the data into a table the way that I wanted it to be.

First step was to simply capture the data 'raw' into a holding table
(include your own logic here for data cleaning and consistency
checking etc....)

The second step was to use a series of queries to extract the data I
wanted in the way that I wanted from the 'raw' table. I only used SQL
for this as I wanted it to be as maintainable as possible.

Lastly, I made a 'transformation' routine in vba that takes the query
results, parses them with any further alterations to the result data
that I want (such as changing name abbreviations to full names
etc...), and 'pushes' the parsed results into a final holding /
staging table. Again include your own logic for updating the final
table, such as edit an existing record or just append to the table
etc...

I dont know if this helps, but it is the only way I found to do the
job the exact way I want it to be done. I suppose that you could also
use some of the open source ETL tools to do a similar task such as
Apatar or Kettle (I think this is now called Pentaho Data
Integration).

I hope that this helps.

Cheers

The Frog

Re: Is it possible to create Import Spec for an Excel Spreadsheet?

Thank you both for you help. Rich thanks for the script, it's similar
to what I'd written. I made a copy of it as code snippets are always
handy. Frog, Thanks for the advice as well, I wasn't familiar with
Pentaho but again it gave me a more to look into for the future.
Anyway, I spoke with my business units only to discover they were
purposely converting the rawdata to Excel in an effort o make my live
easier (Wow!). It seems that the data comes down in .csv format by
default. Long story made short this resolved all of my problems.
Again thank you both so very much from a former, slightly grayer
programmer getting back into the game after about 7+ years of sitting
back and enjoying academia...

You'll probably be seeing me here quite often. Take you both.

Re: Is it possible to create Import Spec for an Excel Spreadsheet?

Greetings,

Yes, the datatype issue is a tricky one between Access and Excel.
Actually, what you (or whoever provides you with the Excel file) need to
do is to prep the data in Excel.

I have these analysts that hand me Excel files to import into the
backend DB (sql server in my case), and with the import tools, certain
rows, fields get excluded. What you need to do in your case, I will
guess, is to highlight the suspect columns and modify their datatype
from General to Text (or from Text to General). Basically, you have to
kick these columns around a bit so that the data is not excluded. I
call it "Prepping" the data. This is not uncommon. In worst case
scenarios, I have to create new columns of a specific datatype in a new
sheet, copy the suspect data to these columns, delete the old columns in
the original sheet, then re-add the columns back to the sheet. This may
be too much hassle if you only have a few hundred rows, but if you have
thousands of rows, then this is the workaround that I use.

Rich

*** Sent via Developersdex http://www.developersdex.com ***

Re: Is it possible to create Import Spec for an Excel Spreadsheet?

Anytime :-)

The Frog