How would you setup DB to store this info?

Question

Hello,

I'm trying to store info about unique device ID's in the DB so it can be accessed later using Power BI etc.
Unique ID never changes everything else ie. name, mailing address, email, phone number etc. can change over the time.
Currently info is stored in Excel and was thinking saving it as CSV file would be the best to import into MS SQL (not sure)?
New data comes in every week and it could remain the same or change.
I'm on the latest version SQL and MSSMS if it helps.

The data shows as follows inside the Excel file:

unique_id name phone mailing_address email date code
12345678 John Doe 999-888-7777 123 main ST johnd@tiedtlaw email .com 12/2/2022 11:33:56 AM Steel

Any suggestion would be greatly appreciated!

Thanks,

Johnny

Answer 1

There are a number of ways you can load Excel files into SQL Server:

1. Save as CSV files and load with one of BCP (command-line tool), BULK INSERT (SQL statement), or OPENROWSET(BULK) (also SQL statement).
2. Write a program in your favourite language to store the data.
3. Use the Import/Export wizard.
4. Use a full-blown SSIS package.
5. Read the Excel files directly from SQL Server with help of the ACE provider.

Which is the depends on your skillset and which desire you have to automate the procedure. Since you will do this daily, #1 and #3 seem a little impractical.

My personal choice would be #2(*), but that is based on my skillset, and I know that I will get funny looks from everyone who knows SSIS (SQL Server Integration Services.) I think quite a few people go by #5, and I guess that when it works, it works. But I see so many posts on the forum where it does not, and the error message you get are extremely unhelpful.

No matter the method, you would read the data into a staging table, and then use the MERGE statement to update your actual tables with the data.

(*) Because it's a daily process. For an occasional import I would go with the first alternative. Again based on my skillset.

Answer 2

Thank you for taking time to reply!

It seems like the best and most efficient way is to save it from Excel Macro enabled file to CSV file and import it new files that way. I wanted to lower number of steps so I chose Microsoft Excel.

When I tried to import it using Microsoft Excel as a data source I got "The 'Microsoft.ACE.OLEDB.16.0' provider is not registered on the local machine. (System.Data)" I fixed it by downloading Microsoft Access Database Engine 2016 Redistributable
both accessdatabaseengine.exe and accessdatabaseengine_X64.exe from https://www.microsoft.com/en-us/download/details.aspx?id=54920 accessdatabaseengine.exe companied that 64bit version was installed and it could not install 32bit. I installed it from command prompt with accessdatabaseengine.exe /quiet.
After that it let me proceed. And another error "SSL Provider: The certificate chain was issued by an authority that is not trusted" and I solved it by selecting 2nd option and under properties checking "Trust server certificate". Hope it helps someone!

Back to my question. Is anything wrong with my approach with Excel File vs. CSV?

Answer 3

Hi @Johnny Good ,

Agree with Erland. Use the MERGE statement to update your actual tables with the data. You can also import directly.
Please refer to this blog: How to import data from an Excel file to a SQL Server database

Best regards,
Seeya

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 4

Hi @Johnny Good ,

From SQL Server documentation, CSV is the format that is accepted in most places:

https://learn.microsoft.com/en-us/sql/relational-databases/import-export/import-data-from-excel-to-sql?view=sql-server-ver16

Hope this helps!