Sometimes you want to take the contents of an Excel .xlsx or .xls file and store it in a relational database using SQL. You can do this easily using Easy Data Transform:
- Install Easy Data Transform on your PC or Mac, if you haven’t done so already. It will only take a minute. There is a fully functional free trial and you don’t have to give us your email or sign up to anything.
- Start Easy Data Transform. Make sure the Auto Run button is pressed in.
- Drag the Excel .xlsx or .xls file you wish to convert onto Easy Data Transform. If there are multiple tabs in the Excel file, choose the one you want to convert. A pink input item will be added.
- Ensuring the pink input item is selected, click the Substitute button in the Left pane (you may need to scroll down the Left pane to see it). If the button isn’t showing, check show advanced in the Left pane.
- A blue ‘Substitute’ transform item is added and selected.
- In the Right pane add the SQL command in Substitution script, leaving gaps where the values from the columns in the CSV files will go. Then insert the column values into the text using the Insert variable drop-down list. Set New column name to ‘SQL’.
- Click Evaluate when you are done. An ‘SQL’ column is added at the end of the table.
- Set the table to Show all rows, click on the ‘SQL’ column to select it and copy it to the clipboard using Ctrl+C (Windows)/Cmd+C (Mac).
- You can now paste the SQL where you need it.
If you have a large number of files to convert you can do it using the batch processing feature.
Substitute is one of the 66 transforms in Easy Data Transform you can use to quickly and easily create a wide range of data transformations.
Windows Download
Mac Download