How to import data from CSV using SQL Management Studio

Owned by Mike Sheen
Last updated: Apr 20, 2023 by Julie HanlonVersion comment
4 min read

Jiwa has built-in CSV imports for common imports (products, customers, et cetera), however sometimes there is a need to perform more complex importing and massaging and joining of data. This guide provides instructions on how to use SQL Management studio to import data to a temporary table (or tables).  Once the CSV data is imported to SQL tables, some simple insert queries can then be used to update or insert into Jiwa tables.

Prerequisites

This guide requires SQL Server Management Studio to be installed



Step-by-step guide

  1. Open SQL Server Management Studio

  2. Right - click the database to import to and choose Tasks > Import Data...

3.Click Next on the welcome dialog

4. Select "Flat File Source" as the data source, and press the Browse... button to locate the file.  If the first row is the header row, then check the Column names in the first data row checkbox.

Match your column formats

It's a good idea to check your column delimiters here so that match your intended source table.

E.g. Don't accept VARCHAR(20) as the default when your source column is VARCHAR(10).  You'll get 'string or binary would be truncated' error in future steps.



5. Click Next

6. Select the Destination as .Net Framework Data Provider for SqlServer, provide the SQL password and User ID, the Data Source (SQL Server name) and Initial Catalog (SQL Database), then press Next.

7. Press Next to the Mappings dialog

8. Press Next to the Review Data Type Mapping

9. Press Next on the Run Package Dialog

10. Verify all rows imported and Press the Close button.

11. Verify the table contains the data by executing a query - SELECT * from <tablename>



Now that the data is in a SQL table in the database, you can massage the table contents, and then run UPDATE or INSERT queries to populate Jiwa tables.



For Example:

In the above steps we imported CSV data into a table named Leda, which contains data to be imported to the Debtor group links table (CN_GroupLink) - which is seen on the Groups tab of Debtor Maintenance.  The following query will do this, matching the debtor group in Jiwa with the Group in the Leda table, and the Jiwa Staff member with the Staff Member from the Leda table.

Example Query
INSERT INTO CN_GroupLink(LinkID, GroupID, ProspectID, StaffID, DefaultItem, LastSavedDateTime) SELECT LEFT(REPLACE(NewID(), '-', ''), 20), (SELECT TOP 1 CN_Group.GroupID FROM CN_Group WHERE CN_Group.Description = Leda.[Group]), (SELECT TOP 1 CN_Main.ProspectID FROM CN_Main WHERE CN_Main.DebtorID = (SELECT TOP 1 DB_Main.DebtorID FROM DB_Main WHERE DB_Main.AccountNo = Leda.[Account No])), (SELECT TOP 1 HR_Staff.StaffID FROM HR_Staff WHERE HR_Staff.FName + ' ' + HR_Staff.SName = Leda.[Staff Member]), 1, GETDATE() FROM Leda

The file we imported was simply called Leda.csv, so SQL Management Studio created a table named "Leda" with the columns named the same as the header row, and populated the table with the data from the CSV file.  The above query inserts into the Jiwa CN_GroupLink table the required values from the new Leda table, with the necessary mapping / linking of foreign key values.

Once finished, the table should be dropped to keep the database schema free from unneeded tables:

Drop table
DROP TABLE Leda