Flat File Importer
Some deployments may also include a flat file customer import and update feature. This feature includes a tool that imports .csv files using defined column names. After being imported, this data is used to create new customer, meter, and location records in the system. If the information already exists, these records may be updated. Batch updates are not recommended on a production system.
Creating the flat file.
Creating a .csv file is very easy. It is very important to verify the source for the customer export is valid and current. Juice will be able to update records but using poorly mapped or out of date data is strongly discouraged. Once the data has been exported, it will be saved as different files by different systems. If you are able to see the information in a spreadsheet, Excel can create the .csv file for you. Once you have the spreadsheet loaded, click on File, Save As, and select Windows Comma Separated (.csv) as the format. If using a text editor or other tool, please make sure your .csv uses a carriage return and line feed between entries.
Where is the Flat File Importer link?
The flat file importer can be found in the Cogs menu. Go to the top menu bar, click the gear icon, then under Customer you will see the Flat File Importer link. Click on the link to access the tool.
Step 1
After clicking the link, you will see a screen like the one above, without the Example file section. Clicking on Upload will display the example file and the following text:
"This is a flexible format CSV file importer. It is intended to allow the creation and updating of customer accounts, locations and meters. The records that should be linked together must be on the same line. The exact information needed will vary by utility, and in some cases, by customer. Each file must be of the same format, but you can upload multiple formats as multiple files. Utiliflex staff will help you with format creation/selection based on your needs.
Key Fields:
The meters are always referenced by serial number it must be an exact match. Typically, meters are pre-loaded into the system and it only has to be referenced to be added to a location.
The customers table has two key fields:
- account - must be unique in the system.
- contract - often used as a secondary key, such as the customers account number in a legacy system. If this field is used, and not account a Juice account number will be created. Updates using the contract field as a key will only work of there is only one account with a matching contract field.
The locations table (primarily a meter socket location) has two key fields:
- locid - must be unique in the system.
- locname - often used as a secondary key, such as the locational identifier in a GIS system. If this field is used, and not locid a Juice locational identifier will be created.
Special fields: credit and debit are accepted fields when used for account creation, but not during updates. Any amount labelled credit will go in the customers wallet, and any amount labelled debit will create an arrears owed. These are typically used for transferring customers from a legacy CIS system.
Allowed field names in definitions
Put all records on same line to join them together.
customers: account, passwd, seclevel, maid, name, accttype, lastname, firstname, phone1, phone2, phone3, phone4, phonenotify, sms1, sms2, smsnotify, email1, email2, emailnotify, address1, address2, city, state, postalcode, countrycode, billaddress, billcity, billstate, billpostalcode, billcountrycode, group1, group2, group3, group4, group5, group6, othergroups, tz, comments, notifydays, bdom, sendpdf, contract, defaultlang, custstatus, lastbalance, created, lastlogin, fromip, velocity, lastmod, lastseenby, mgmtfee, autovendperc, autowalletfund, discountkwh, discountperc, minkwh, govnumtype, route, correlative, dailyinterest, specialstatus, specialagent, specialmessage, creditlimit, idtype, iddata, billpowerfactor, billkwhpeak, peaktariff, contractkwhpeak, invto, loclat, loclong, locroute, branchcode, unit,
meters: locid, account, id, serialnumber, name, com, tariff, subtariff, typeid, statustypeid, lastcommunication, servicestatus, gatewayid, gatewayname, gatewaystatus, sgc, server, initkwh, kvademandrating, vendable, meterlocation, miscdata, meterstate, disconnect, installer, units, factor, phases, ststi, stsphaseunbal, stspowermax, stskeyrev, stsexpnum, discoprofile, lastcomby, parentid, minkwh, discountkwh, discountperc, breaker, comments, created, lastmod, type, formfactor, wiring, currentperphase, podisco, scanedas, voltageperphase, frequency, att, lastactived, lastdeactived, lastdeactivednofee, lastread, sumforwardactive, usagediff, lastreadreq, meterstatus,
locations: locid, serialnumber, account, locname, locaddress1, locaddress2, loccity, locstate, loczip, loclat, loclong, locx, locy, substation, transformer, module, socket, route, correlative, svcbldgtype, svcaddress1, svcaddress2, svclat, svclong, svcx, svcy, landlordid, meterinstalled, securityseal, lastcontract, comments, buildingconstruction, created, lastmod, voltage, kva, tariff, subtariff, type, formfactor, wiring, currentperphase, feeder, unit, displayid, controlgroup1, controlgroup2, controlgroup3, expectedusage,"
Step 1 Success!
Once a file is loaded, Juice will display the file name and confirmation of lines read. You may now load a new file or update the system with imported records.
NOTE: File validation is minimal, we check the file exists, is parseable, and less than 4 MB in size.
Step 1 Error!
If the file is invalid or too large, the system will display an error. Please check the file size, if less than 4 MB, verify the file is saved to the correct location. If all else fails, open the file in a text editor (not a word processor or other type of application) and verify the contents.
Step 2
After loading a valid file, click on Create/Update. This will load the new customers into Juice or update existing customers with the new data.