Fog Creek Software
Discussion Board

The excel to access migration headache

Hi everyone,
If anyone can help me with this, it would be so so appreciated.
I have over 800 excel sheets that I want to import into an access database.
Here is a sample of one of them...
(File has been checked and is virus free)

Basically I just want to import all fields
from A-7 to 0-7,,,,as well as the two fields called Ref.DWG and DocuNo at the top right.(interestingly, they both seem to take up multiple columns but one cell). 
Anyway, is such an importation possible?. Your help on this would be very much appreciated, as it has had my mind in a twist  for the past 2 weeks. And I'm quite new to this data migration phenomenon. Thank you in advance.

Monday, May 12, 2003

Normally you just install the Access add-in and then go to data|convert to Access

This doesn't work in this case.

You best bet is probably to ask on Woody's Office Portal. The forums there are pretty good.

Stephen Jones
Monday, May 12, 2003

Might be able to write a VB app, connect to excel via odbc read the data then open your database and insert the data, plus what conversion is necessary inbetween reading and writing the data.

Monday, May 12, 2003

The first thing I can say is that you probably will get better answers in one of the many newsgroups for ms-access. (they are very active).

The second thing is that yes, it is not hard at all to import the data. I am sure in half an hour I could have a working import routine.

Having now said that you can import the data, the real question becomes into what type of data file you want to import to?

For example, you mention that you only need some columns. What about stuff like the project name etc, is that to be ignored? Further, you don’t want each Excel doc to be imported into a separate table, do you? That just moves the whole problem from Excel to access! You don’t want to waste your efforts in JUST a import, but take advantage of this time to correct the designs.

So, stuff like project name should be a separate table. No need to repeat the data over and over like Excel usually does. Some data normalizing should take place when you import, as this will save you much grief, and also cost down the road as this Excel sheet blossoms further into a application.

You kind of need more then just a import. (the import part is the easy part!).

Off the top of my head, my “air” code would do the following:

  Dim ExcelApp        As New Excel.Application
  Dim ExcelDoc        As Excel.Workbook
  Dim ExportTable      As Range
  Dim strExcelDoc      As String
  Dim intRow          As Integer
  Dim intCol          As Integer
  strExcelDoc = "c:\pml.xls"
  Set ExcelDoc = ExcelApp.Workbooks.Open(strExcelDoc)
  ' find number of entries in data table, build range
  intCol = 1
  intRow = 10
      If ExcelDoc.ActiveSheet.Cells(intRow, intCol) <> "" Then
        intRow = intRow + 1
        Exit Do
      End If
  intRow = intRow - 1
  Set ExportTable = ExcelDoc.ActiveSheet.Range("a10", "N" & intRow)
  ' Export to access table code goes here..
  For intRow = 1 To ExportTable.Rows.Count
      For intCol = 1 To ExportTable.Columns.Count
        Debug.Print ExportTable.Cells(intRow, intCol)
      Next intCol
  Next intRow
  ExcelDoc.Close False
  Set ExcelDoc = Nothing

The above lets you create a “range” object. Range objects are REAL important in Excel VB code.

The code to add each row to ms-access via a reocrdset is only 5 or 10 more lines, and you are done.

The whole thing is quite easy, but if you are not comfortable with VB, and not comfortable with the Excel, then you need a good book! The above is good shell to start with. I did not test the above, but I did type it in to the ms-access editor to at lest "help" me for the right syntax (inteli-sense is nice!).

Albert D. Kallal
Edmonton, Alberta Canada

Albert D. Kallal
Monday, May 12, 2003

Save each Excel spreadsheet as a CSV file; you can write VBA to open them and convert them en masse, I expect.  Then, in Access, import one of the CSV spreadsheets, and in the process write an Import Spec.  Then use VBA to import each spreadsheet into a bare bones table and to process it as needed into a final table or tables.

That's how I would do it.

Monday, May 12, 2003

I recently had a similar migration problem and I found SQLServer' Data Transformation Services (DTS) very useful.

And no, I'm not mistaken, I did read the question.

I thought of SQLServer DTS services because you can use them to get data from Excel directly to Access (no SqlServer databases in between). In fact you can use DTS between every OLEDB or ODBC database.

From the SQLServer enterprise manager console, select the 'New Package' command from the 'Data Transformation Services' folder (which is at the same level of the Databases folder)

On the designer window, drag and drop a MS Excel connection fron the Connection toolbar, configure it pointing to the excel file. Then drop a 'Microsoft Access' connection and configre it with the settings of your destination database.

Then add a 'Transform Data Task' from the Task toolbar. It will prompt for the source and destination. Just click the two connections you dropped earlier.

Click the data transformation arrow, select the source table (excel worksheet) and destination table (sql table), and then the important part: the transformations.

On the transformation page you can add one or more transformation. A transformation can be as simple as 'Copy fields' (which you should use for all columns that can be directly copied to the database fields), but can be complety customized on a column by column base with VBScript. For instance, you could add a transformation that takes two source cells from the excel sheet, trim spaces and concatenate their values to obtain the value for the destination column.

It takes a little to get used to how to configure this process, but after doing so, you can import wth a single clik all your data.

I have done this for a single excel file though. I don't know what to do about having several excel source files.

Check google, or this page for further info:

Of course, if you don't have SQLServer this is not an option.

Monday, May 12, 2003

Don't have my windows box handy, so couldn't  look at your spreadsheet itself, but just so you know, from MS Access, you can open a spreadsheet as a table directly -- this is not the same thing as actually importing the data into an access table; this gives you an access table 'front end' to the underlying excel data source. It's kind of like using Tie::Hash or Tie::Cfg in perl -- you interact with the hash, but underneath it's a text file or a dbm file.

I forget the specific option in ms access, but believe it's something like "get external data". Somewhere along the clicks and dialog boxes, there's an option to 'link' to an external data source (your excel spreadsheet in this case), and you can do read/writes to the spreadsheet, treating it as a regular db table in ms access.

Of course, other posters are quite correct -- you probably want to revise the underlying structure of your data while you're at it, but if my memory's holding out (don't know -- had a birthday recently, so my available memory pool has decremented yet again ;-), then this at least gets you access to your data from within access pretty easily, then do whatever data transforms you need from within access to get your data in a set of relational tables.

Even if you don't use this feature this time around, it's a good thing to keep in mind - access can also link directly to text files, other access db tables (from other mdb files), as well as excel files. It's handy sometimes.

good luck,

Monday, May 12, 2003

Wow, the replies are amazing, and so fast as well. Thankyou everyone. Unfortunately, I'm not familiar with vba code, and  its really distressing me the fact that I dont know it, esp in the short period of time I have. This is my first project in a graduate position. And if I don't make this work. Then I don't want to imagine the consequence.
The whole purpose is to consolidate the 800 excel sheets. Because once its in the access database (if it ever gets there), I have to calculate the sum of the 'weight' according to the 'size' which happens to be the embedded in the 'description' text. You see how nightmarish this can be?. I guess if I can just import the fields, then I can sort out the rest. But Albert, you do have a point, with regards to the design aspect being correct in the first place, something I shouldn't overlook. Well, any more suggestions are more then welcomed. Thankyou again.

Tuesday, May 13, 2003

Mark, how is your Java?  I've written systems to import and export up to ten thousand sheets that have been specced (badly) by other people without problems using Apache Jakarta POI.

Tuesday, May 13, 2003

Don't know Java at all Konrad. I'm going through vba tutorials on the net to see what I can do.

Tuesday, May 13, 2003

Hum, no I don’t have any more suggestions here.

Also, don’t let my points about normalizing or fixing the data worry you too much. That is just my nagging consulting disclaimer nature kicking in.

In the real world, we don’t always have that luxury of fixing, or re-structuring the data.  Since I am quite fluent in working with office, then to me the quick job of importing the data is not my the real problem.  To me it would be normalizing the data etc.

However, in your case, your problem right now is the transfer of data.

What we want to do right now, and do it fast is simply get the data into access. Those ‘nice” things will have to be dealt with at a later time.

Now, lets just explore a few points and suggestions made here.

Exporting to CSV, or some intermediate format.

I don’t recommend this approach.

The problem here is if we COULD go File->Get External data->import Excel, then we would be home free. If we could do that, then linking to sheets would also be possible. Further, if you could do that, then you could also export to csv and import!

Anyone who has used Excel, or ms-access realize that getting data between the two is very easy. (after all, they both are part of the office suite).

However, your data is not quite in a nice format, and there is some extra stuff. So, if the data in the excel was a simple nice table then you would have little trouble at all!

Further, we need to automate this process. Excel is a object, and it is a programmable object. Bill Gates famous article in Byte Magazine more then 10 years ago when he talked about some day in the distance future that all of office will be a whole programmable “thing” that any developer can use.

Well, that future is here right now!

Why resort to exporting the data to somewhere else when we have a programmable com object that has the data in a nice table format for us?  Further, we can get that object to return the table inside the sheet as we want!

It does not get any better!

In almost all cases if you can interact with data via com as opposed to ODBC, or intermediate formats…com will wins all the time.

We should not really worry about the internal data structure when we have a ready made com object to work with!

My above code example code uses this fact, and thus eliminates virtually all of the problems involved in exporting the excel data to some other middle format.

My posted code grabs the table data and returns a range object. That range object is as if you highlighted the data with your mouse and went “cut”.

If you look close, that for/next loop works on the range object and actually outputs your current data table!

We are 90% done with just the lame hunk of code I threw out.

It sounds to me like the real problem here is that you need someone who know the office suite well enough to automate this process. 

The above code has what you need.

For the data in the table, is there a limit as to how many rows? Or is the same size for each sheet? My above example loops until it hits a blank, but perhaps there is some other way?

The above assumption seems quite fine. I am tempted to take the above code, paste it into ms-access and email it to you. The problem is here that I don’t think you know what to do with the mdb file I will send you!

You can just grab the above code and run with it. If you wish, I will take that code, paste it into ms-access and email you a working example.

Albert D. Kallal              (Microsoft MVP - Access)
Edmonton, Alberta Canada

Albert D. Kallal
Tuesday, May 13, 2003

(Microsoft MVP - Access)  <- Congratulations Albert !!!

Just me (Sir to you)
Tuesday, May 13, 2003

At a glance, when I look at you spreadsheet I see 2 tables that you need to create in access.  A project table and a materials table that hold the materials used in that project.

One project can be made of many different materials.

So in Access Create:

1. A table named 'tblProject' with one column named for each field in A2-N6.

2. A table named 'tblMaterials' with one column named after each column in the row A7-O7.  Include a foreign key in this table from tblProject to establish the relationship.

3. Use Alberts code above to perform the operation. 

My 2 cents.  I know it can be frustrating if you're not familiar with the tools you are using.  I would personally use VB 6.0 and ADO.  If I catch a minute I'll see if I can write some code for it.

Dave B.
Tuesday, May 13, 2003

>(Microsoft MVP - Access)  <- Congratulations Albert !!!

Thank you very much.

I *just* received that award. The MVP program from MS is really a very nice award to receive.  The perks such as a full year MSDN (universial)  subscription is rather nice. That means I get virtually all developer tools form MS for one year. That includes all versions of windows from xp-home right on up to the server editions!

I get to play with ton of software. I don’t know how I am going to get any work done now…but it does feel like Christmas for me!

The MVP award also does require me to sign a NDA with MS, but the perks are well worth this.

Albert D. Kallal
Edmonton, Alberta Canada

Albert D. Kallal
Tuesday, May 13, 2003

Here's what I came up with using VB 6.0, Access 2k, Excel 2k and Alberts code above.  Run the project from within VB; open the spreadsheet you want to convert; click convert; look at the database!  ( Code is kinda sloppy and so are the database data types cause I did it really fast. Also assumes all spreadsheets are like the sample you posted. )  Anyway, hope it helps ) - -

Dave B.
Tuesday, May 13, 2003

Thankyou everybody, thankyou Albert,Ged, Dave and all,,,I've been quite overwhelmed by the helpfullness and promptness.
Thanks Ged for introducing me to the vba tutorial, I have learnt alot. And thankyou Dave for your program.
I would like to say officially that I can now import my excel sheets :)
The sacrifice to help me out has been deeply appreciated. That no words can suffice. THANKYOU EVERYONE!

Tuesday, May 13, 2003

*  Recent Topics

*  Fog Creek Home