Import from Excel and clean up data

[nick941741]

Hi, I've got a large spreadsheet that gets delivered every month with a customers mobile phone bill contained within it, there are several reports that need to be run from this and also several problems that need to addressed before the data can be exported/imported to access.

The headings contain the names of Date and Time for 1, the dialled numbers are also missing the prefix of 01 or 07 and these need updating as the client wishes to know the top 20 dialled numbers, something that can't be done until all the numbers are in the correct format.

My main question is this, should I clean the data in excel with a macro first, or wait till the data is in Access then clean with a macro or even better clean the data on import?

Any help much appreciated.

Nick

 

[DavidAtWork]

Far better to import the spreadsheet first, then clean the data as it's less hassle to do it in Access
David

 

[nick941741]

Sorry guys, thanks for the replies, I got side tracked with another project. I will no doubt be asking for more help real soon.

Nick

 

[gemma-the-husky]

the least massaging (ideally none) you do to an import file, the better, i think.

if users start changing spreadsheets, it gets harder.

now - do you REALLY have a spreadsheet, or did the phone co send a csv file, because it's much easier to deal with proper files directly in access. (except for xml files, which are hard to use)

if a csv, then don't even open it in excel, or at least don't save it back to the original csv. you want to import the raw data csv to access every time

 

[nick941741]

Hi Dave, yes of course the file is a csv. I'm still wondering how to go about making my changes though, whether to do it via a VB script once the data is in.

This is basically the process I need to run.

Import raw data

• Change field names
• ​
  Change Date - Change from Date> date_of_call
• ​
  Change Time - Change from Time > time_of call
• Remove unwanted data - remove all unwanted call types not needed for report
• Create User table
• Create calls table
• Clean up Phone Numbers - Change format of phone number so that all align
• Run Queries to produce results and export to excel

 

[gemma-the-husky]

import to a temporary "import" table

the column names don't matter

run a query to tidy the data - you may need some functions for this

validate the data in any way you need

when its accepted, use an append query(s) to store it all in the "real" data table(s)

 

[nick941741]

Nice one, thanks Dave.

The only reason I was changing the names of the fields was because they come in as Date, Time and someone mentioned about them being reserved names within access.

Nick

 

[gemma-the-husky]

the reserved words thing is not the end of the world.

there is a possibility of confusion, and you may need to use [date] etc, in square brackets, but it will still work. If they REALLY didnt want you to use some words, they would prevent you doing so.

absentmindedly, i created a field called "type" the other day, and it still works.

I thought you meant you had strangely named fields. The thing is, the idea is to treat the table as a temporary table, and move it into the real table in due course.

as long as the data can move into the temporary table, you shouldn't have an issue.

it may be better to pre-design the temp table, then clear it out, and improt into the pre-exisitng table. This is useful because you can preset indexes, and you can have extra columns.

 

[nick941741]

Thanks Dave, I'm going to have a bash at this at the weekend, so no doubt there will be a few more posts within the forums as I get stuck.

Much appreciated.

Nick