I have some information that I would like to script to update into RMS 2.0's customer table.
I have one part of the organization that provides information for certain customers that are to receive a discount. I would like to be able to run an update query that if there is a customer in the database and also in the .xls documentit updates a field in that customer entry. If there is a customer that's not in the table but in the .xls document I would like to have it add that customer and populate what information it can.
I think I can figure the query out on my own, but I don't know how to make it read from the excel file and pipe it into the customer table.
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
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:
1) CREATE TEMP TABLE FOR BULK INSERT OF CARD BALANCES
create table giftcardtemp (id int, cardnumber varchar(30), balance money)
2) RUN BULK INSERT COMMAND TO POPULATE TEMP TABLE
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 ;)
BeanSmart website is not affiliated with any of the manufacturers or service providers discussed here.
All logos and trade names are the property of their respective owners.