Import information into customer table from Excel (RMS 2.0)

MS Access is a convenient tool to join and manipulate data from two sources like that.

Hi David, In order to process the information, you will need to make a small module from vb6 or vb.net(recommended) to read customer records from excel file and then update the database that way.

If you need help email me : efology at yahoo.com Thanks

"David B" wrote:

I don't know how to have your update or insert query read an excel spreadsheet, maybe someone more experienced with SQL will reply. But you could consider creating a temp table (or a temporary table you would flush after you run your queries) that can serve as the source of your updates. Use the BULK INSERT command to populate that temp table (save the file as tab delimited).

I had to do something kindasorta similiar some months ago - where I had to populate the voucher table for previously sold gift cards - maybe this can work for you - here's the code I used:

create table giftcardtemp (id int, cardnumber varchar(30), balance money)

BULK INSERT databasename.dbo.giftcardtemp

FROM 'c:\giftcardbal.txt'

WITH

( FIELDTERMINATOR = '\t', ROWTERMINATOR = '\n'

In your scenario, create a table that matches your excel spreadsheet column structure and datatypes. The temp table column structure must match the column structure of the source file, as the bulk insert command populates the columns as it reads them.

Run SELECT queries first joining both the temp table and customer tables to see what will get updated or inserted. Remember to backup the db and do this after hours if possible "just in case".

H> I have some information that I would like to script to update into RMS 2.0's

I also use Access to import info from Excel all the time (vendor price updates). I have a database I use that is linked to all the commonly used tables in RMS, like Items, Supplier list, alias, etc. I format the spreadsheet in Excel so it has meaningful column names, then import it into an Access table. From there it is very easy to write a query to do what you need to update/append RMS, even if you aren't too good with SQL ;)

Marc