CODEWITHSUNDEEP
sqlsql-serverexcelmerge
Justin Burgard
Justin Burgard

Reputation: 470

How do I convert Excel data into a SQL insert?

I need to upload several thousand addresses to a web portal. I do not have access to the site itself and must do so with the following INSERT statement: 

INSERT INTO Address (AddressTypeID, Address1,City, State, Zip, Phone, PersonName, DisplayName, IsDefault, StatusID, UserID) 
VALUES ('1', 'SQL Herro st', 'SLC', '42', '84087', '8015957258', 'Bruce Wayne', 'Bruces address', 'False', '1', '3019')

I have the addresses in Excel but I don't really have any idea how to iterate this code a thousand times while importing the data.

Upvotes: 14

Views: 52619

Answers (4)

mng
mng

Reputation: 7

In Excel you can use this formula in any column ="("&A2&","&B2&","&E2&","&F2&","&G2&"),"

then copy the format by "CTRL" + scroll down to get all the data with same format. With date data, you can come to this site for more information: [a link]https://www.excel-exercise.com/title-with-date-excel/

Upvotes: 1

Peter Thompson
Peter Thompson

Reputation: 11

Previously I have created excel VBA macros which automatically generate the sql statements from the inoput sheet, provided the input sheet is formatted correctly (basically excel headers match sql columns)

Upvotes: 0

Mike D.
Mike D.

Reputation: 4104

SQL Server Management Studio will allow you to import data from Excel files. What this actually does is create a temporary SSIS package to run your import.

  1. Right click the database you want to import the data into.
  2. Check your column settings and make sure all your data types are correct.
  3. In the menu Select "Tasks" then find "Import data"
  4. In the data source drop-down you can select "Microsoft Excel"
  5. Set your file path and then click next
  6. Select the SQL Native client as your data source and it should default to the server and DB that you right-clicked
  7. You can import to an existing table or create a new one and configure the mappings.
  8. Run the process and check for any errors.

Now, some caveats. In order to import from an Excel file you need the Microsoft.ACE.OLEDB driver installed. I've also had issues with data type translations and other such things from Excel.

When I need to do an Excel import I will usually convert it to a CSV as that is much simpler. It's as easy as saving the document as a CSV in the Excel application. Then just change the first step above to flat file import.

It is a good idea when doing any import to really double check the column settings on the import. For CSV files at least you can have the import guess at the data types by parsing some of the rows. This usually does a good job but I often have to clean it up a little bit. Like changing floats to decimals and setting larger lengths on strings to avoid any truncation errors.

Upvotes: 1

davejal
davejal

Reputation: 6133

You have different options depending on which server you are using, but with your current info you could do the following.

  1. click on the first empty column in the first row i.e. if data is in row 2 and all columns go from a to k, then you select L2

  2. Paste the following code

    ="INSERT INTO Address (AddressTypeID, Address1,City, State, Zip, Phone, PersonName, DisplayName, IsDefault, StatusID, UserID) VALUES '"&A2&"', '"&B2&"', '"&C2&"', '"&D2&"', '"&E2&"', '"&F2&"', '"&G2&"', '"&H2&"', '"&I2&"', '"&J2&"', '"&K2&"'"

  3. Check to see if the query is correct, it's calculated with the fields from a2-k2)

  4. copy -> paste special values only to the next column, so you keep your code, but you can select all rows with the calculated results to your sql query tool

enter image description here

Upvotes: 22

Related Questions