Upload CSVs to Staging Environment

In this step, we will upload data in the form of CSV files to a Staging environment. The application will assist in identifying any potential errors on the CSV files as we upload them.

Instructions

Log In to the Web Application

  1. Open Internet Explorer and browse to the client's Data Integration Utility web app location

    • If Accessing From Local Server: http://localhost/IPStaging

    • If Accessing From Another Computer: http://SERVERNAME/IPStaging

  2. At the login screen, enter the credentials which have been setup:

    • Username: [provided username]

    • Password: [provided password]

  3. You will be taken to the Main screen.

 

Upload a CSV File

 

The Main screen displays a Staging Summary that shows a list of all of the table names, the latest status, and the latest status date. 

 

 

Note:  There may be one or more additional tables names that appear based on a customized implementation.

 

  1. At the bottom of the Main screen, there is a section titled "Upload CSV."

  2. Select a destination table from the drop-down list.

  3. Select an upload option by making a selection in the "Select an Option" dropdown.

    • If accessing for the first time, the only option available is "New Data"

    • If not accessing for the first time, the following options are available:

    • Append: Inserts the CSV file's contents to the destination table. If there is existing data, it will be added after.

    • Overwrite: Deletes all contents in the destination table and uploads the CSV file's contents in its place

    • Merge: The application will check to see which rows in the CSV exist as records in the destination table. If the CSV row does not exist, it is inserted. Otherwise, it is skipped.

Note: The application will always perform an overwrite for Stage_GL_Data. This is due to the fact that GL data inherently is a much larger data set than the other tables.

  1. Select a file accessible by the local machine by clicking Browse, locating the file, and double clicking on it.

  2. Click on Upload CSV.

  3. Repeat process for each individual file as necessary.

 

Once the load files are successfully updated, the next step to complete is General Data Integration.

 

Note: For larger files that are uploaded as a background process, you may need to check the Monitor Summary screen for an update. See the Monitor Summary section for details.

 

Stage Table Statuses

You may notice that next to each staging table, there is a status defined for CSV uploads. These statuses drive the Upload options you can select. The following table defines each status.

 

Status

Explanation

CSV Needed:

The initial state of a table. No data has been loaded. The only Upload option available is "New Data."

CSV Uploaded:

The state of the a table after CSV Needed. This is also the status after uploading data to Stage_GL_Data. Upload options with this status are Append, Merge, or Overwrite.

CSV Overwritten:

Existing data was deleted from the table and replaced with the data file associated with this status. Upload options with this status are Append, Merge, or Overwrite.

CSV Appended:

Data from the file related to this status was added on top of any existing data on the table. Upload options with this status are Append, Merge, or Overwrite.

CSV Merged:

The app compared data from the file related to this status from existing records on the table. Records that do not exist were added. Upload options with this status are Append, Merge, or Overwrite.

CSV Upload In Progress:

Only for Stage_GL_Data. The Monitor Service has picked up the file and is sending it to Stage_GL_Data.

CSV Upload Failed:

Only for Stage_GL_Data. The Monitor Service attempted to send the file's contents to Stage_GL_Data but failed. Expand and check System Messages to see what failed.

 

Avoiding CSV Errors

Following the file format guidelines will ensure that CSV errors do not occur. Aside from basic data structure, the following "quick guidelines" will ensure an error-free load:

  1. Follow the structure of the destination table accurately.

Note:  If a destination table has 5 columns but the last 2 columns have no data (and are nullable), define them appropriately

    • Examples (assuming a table of 5 columns):

    • Incorrect: "1001","Accounts Payable","Accounts Payable"

    • Correct Formats:

    • Option 1: "1001","Accounts Payable","Accounts Payable","",""

    • Option 2: "1001","Accounts Payable","Accounts Payable"

  1. As a rule of thumb, always qualify each column using double quotes ("). If a double quote character exists as valid data in a column, replace it with a single quote ('Johnny').

Have more questions? Submit a request

0 Comments

Please sign in to leave a comment.
Powered by Zendesk