Stuart McLachlan
stuart at lexacorp.com.pg
Sat Jul 17 19:15:09 CDT 2004
On 17 Jul 2004 at 17:17, Susan Harkins wrote: > I'm wondering if any of you routinely see data type conflicts when importing > Excel data into Access? I spent an afternoon with a client a few weeks back > cleaning up Excel data for a clean import into Access -- we were totally > successful. We went from losing hundreds of records to capturing every > single one -- with just a few simple changes in their routine. > > The one potential problem I thought of that we didn't encounter was a > conflict in data types -- have any of you experienced this? My guess is > you'd simply convert using the Import Wizard, but I'd like to hear your > horror stories and solutions. >I know imports are often automated and that complicates things. > > The client's experience was strictly a one-time effort. Once they got it in > Access and we normalized the data, they were done with that part for good. > They were trying to create a searchable/reportable archive of some very old > data and used Excel as the go-between -- but then couldn't get the Excel > data clean enough to import. The truth is, it was very easy to solve -- they > just didn't know the right tricks. So, now they want it all documented, but > I personally, haven't run into any data type conflicts while importing from > Excel, but I'd like to note any potential problems if they're out there. > Mostly I see problems when clients open delimited text files in Excel and then save them again. That can really screw up formatting of the files for a subsequent import into Access. ie doing what you were doing - "used Excel as a go-between" In fact there's an interesting article in The Register about this problem http://www.theregister.co.uk/2004/07/16/excel_vanishing_dna/ The other problem is indeed Datatype conversions. The Excel Import Wizard does NOT allow you to define datatypes. You are forced to accept what the Wizard thinks is the appropriate type. Friday I spent hours working on data for a client who is a printshop. They had received a load of delimited text files of data to produce Annual statements for contributors to a super fund. They had tried to merge theses files using Excel. They initially called me in to look at the data and help them with some addressing and formatting problems and grouping sorting problems with the merged data. Initially I tried to import there resulting Excel file into Access. One column contained numbers (percentage of a superannuation death benefit which went to each beneficiary.) The first x records had numbers like 100, 50, 25 in them so the Excel import wizard decided it was an integer field and truncated the 33.3s when they finally occurred further down. Another field was text which could have a date or some other comment in it. Because the first few had dates, the wizard decided that that was it and subsequently threw out all the text comments. I eventually did what I've done on several occasions in the past. Saved the spreadsheet as a delimited text file and import that. The text import wizard DOES let you define the datatype of each column and even better, you can bypass the wizard and write your own routine to modify the data as it is imported if you really need to. I then found one other problem. When I looked at the number of records imported from Excel there were 65535 of them. That number immediately aroused my suspicions (Hope no-one here has to ask why <g>) and on checking, sure enough there were actually well over 70,000 contributors in the original data files. I ended up importing the raw data files and merging them in Access. It took me about ten minutes to do the raw text files import then merge, clean and export as required for their print formatting software (a program called Paris), but it cost them several hours of my time to identify and sort out the problems caused by them using Excel on the data in the first place. -- Lexacorp Ltd http://www.lexacorp.com.pg Information Technology Consultancy, Software Development,System Support.