Importing new customer table into existing Quickbooks application

Posted by jefkit on August 19, 2009, 10:29 am

Please log in for more thread options

I'm fairly new to Quickbooks, but pretty experienced with Excel and
relational databases. I'm helping clean up/scrub customer data and,
because there's so much clean up to do, I figured if I did all my
editing in Excel, I could import the Excel data and overwrite the
existing Quickbooks customer data.

This Quickbooks application is currently in use for 2009 business. I
don't have a test environment to play with. If I do this excel data
scrubbing and I Import into Quickbooks, will I kill/screw up/ destroy
the current year's open invoices, estimates or books & records in any
way? The owner of the business I'm helping doesn't have a clue.
Thanks to anyone who can help.

Posted by Laura on August 19, 2009, 11:22 am

Please log in for more thread options

> I'm fairly new to Quickbooks, but pretty experienced with Excel and

> relational databases. I'm helping clean up/scrub customer data and,

> because there's so much clean up to do, I figured if I did all my

> editing in Excel, I could import the Excel data and overwrite the

> existing Quickbooks customer data.

> This Quickbooks application is currently in use for 2009 business. I

> don't have a test environment to play with. If I do this excel data

> scrubbing and I Import into Quickbooks, will I kill/screw up/ destroy

> the current year's open invoices, estimates or books & records in any

> way? The owner of the business I'm helping doesn't have a clue.

> Thanks to anyone who can help.

What exactly are you importing--Customer lists, invoices, payments? What
method are you using to import this information--QB utility, iif file or a
third-party tool?

Importing customers lists will only ADD customers if they don't already
exist. For existing customers it will over write the info contained in the
excel file. It really can't be used for clean up purposes.

As for "test" environment, put QB on another computer or make a backup of
the existing file and change the file/company name and use that file as your
test environment.

Posted by jefkit on August 19, 2009, 12:20 pm

Please log in for more thread options

> > I'm fairly new toQuickbooks, but pretty experienced with Excel and

> > relational databases. =A0I'm helping clean up/scrub customer data and,

> > because there's so much clean up to do, I figured if I did all my

> > editing in Excel, I could import the Excel data and overwrite the

> > existingQuickbookscustomer data.

> > ThisQuickbooksapplication is currently in use for 2009 business. =A0I

> > don't have a test environment to play with. =A0If I do this excel data

> > scrubbing and I Import intoQuickbooks, will I kill/screw up/ destroy

> > the current year's open invoices, estimates or books & records in any

> > way? =A0The owner of the business I'm helping doesn't have a clue.

> > Thanks to anyone who can help.

> What exactly are you importing--Customer lists, invoices, payments? What

> method are you using to import this information--QB utility, iif file or =

> third-party tool?

> Importing customers lists will only ADD customers if they don't already

> exist. For existing customers it will over write the info contained in th=

> excel file. It really can't be used for clean up purposes.

> As for "test" environment, put QB on another computer or make a backup of

> the existing file and change the file/company name and use that file as y=

our

> test environment

The existing customer data in QB is a mess....wrong addresses, no
phone numbers, duplicate customers, some Bill To addresses are last
name first and others are first name first. I'm trying to figure out
the easiest way to do a massive clean up without going through 1 by 1
in the customer Center to fix the data

Oh, I'll use the QB Import Function and I'll either just import an
excel spreadsheet or I'll save it as a csv file, whichever works best.

If I have to go through and inactivate all the bad data/customers 1 by
1, I'll do it.

Data integrity is SOOOOO key!

Posted by Laura on August 19, 2009, 1:03 pm

Please log in for more thread options

Okay. This should not be too difficult but I am not sure what the value
added you get from doing this data clean up especially for non active
customers. For current customers I ususally just clean them up when I do an
invoice. I'm not one to create billable hours just because someone feels the
customer database is a mess but that's just me.

Run the Customer list report but modify it to include the columns that need
revisions (to keep things simple). Dump it to excel. Get rid of any records
in the format Customer:Job as they can not be imported-only customers can be
imported. Make your changes in excel. Get rid of any records that you do not
need to revise. For addresses, make sure you include the pieces of street,
city, state, zip as individual fields rather than 1 total field. Bill to
fields are stored as one string but address is not.

Then select File>>Utilities>>Import>>Excel Files and select the Advanced
Import feature. This allows you to select which fields you wish to import
and their order.

And of course, make a backup of your file before you do any importing. That
way if something goes wrong you can start over again. I would do a handful
of records at a time to keep things managable.

The existing customer data in QB is a mess....wrong addresses, no
phone numbers, duplicate customers, some Bill To addresses are last
name first and others are first name first. I'm trying to figure out
the easiest way to do a massive clean up without going through 1 by 1
in the customer Center to fix the data

Oh, I'll use the QB Import Function and I'll either just import an
excel spreadsheet or I'll save it as a csv file, whichever works best.

If I have to go through and inactivate all the bad data/customers 1 by
1, I'll do it.

Data integrity is SOOOOO key!

Posted by Mark Bole on August 19, 2009, 11:40 am

Please log in for more thread options

jefkit wrote:

> I'm fairly new to Quickbooks, but pretty experienced with Excel and

> relational databases. I'm helping clean up/scrub customer data and,

> because there's so much clean up to do, I figured if I did all my

> editing in Excel, I could import the Excel data and overwrite the

> existing Quickbooks customer data.

How did you determine that it would be faster or easier to "scrub"
Quickbooks data outside of Quickbooks? It sounds more like all you have
in your toolbox is a hammer, therefore you approach every problem as if
it were a nail.

> This Quickbooks application is currently in use for 2009 business. I

> don't have a test environment to play with.

Create one.

If I do this excel data

> scrubbing and I Import into Quickbooks, will I kill/screw up/ destroy

> the current year's open invoices, estimates or books & records in any

> way?

Possibly; it all depends on what you do and how you do it.

Try the following Intuit support web site articles: KB ID# 1005472 and
KB ID# 1003348

-Mark Bole