Last post May 21, 2018 02:53 AM by Nan Yu
May 18, 2018 08:52 AM|aazizasp|LINK
I have an idea and I want to know if it is possible to implement. I have a small SQL table and an .XLSX file. I want to create an asp page with fileupload and button controls. Once the user browse for the file and click upload button, the button click event
should convert the xlsx file to a comma delimited text file and then writes it to the sql table.
Any ideas how I can approach this?
May 18, 2018 09:15 AM|PatriceSc|LINK
It's a bit unclear if you want to write a comma delimited file to your database or if you'll basically import each value in the proper table/row/column...
Anyway you have basically two ways to read Excel files :
- using a database driver that expose the Excel file as tables. It can cause issues (Excel is not a db so the driver checks the first few rows to guess the type but then could later encounter a value that doesn't match the guessed type). It is always IMO a
bit messy and needs tweaking
- my personal preference would be likely to use
https://msdn.microsoft.com/en-us/library/office/bb448854.aspx which is a bit more complex but allows to get full control about how values are taken from the Excel file
May 21, 2018 02:53 AM|Nan Yu|LINK
It is not complex if your sql table already has the column
definitions . If not , you may try some tool like https://sqlizer.io/#/ , that tool helps converting tables from XLSX, CSV or JSON into SQL insert scripts complete with table definitions. You can use Sqlizer
API to make the conversion in your application . Then using the SQL Server Management Objects (SMO) to execute the script .