Explain how to get information from Excel over to the database

Question

Ellie · Accepted Answer

You're right, to get data out of MS Excel, especially if you're using something newer, e.g. Azure SQL database, you have to write the data from Excel to a CSV delimited (or whatever delimiter you prefer, e.g. tab delimited txt) flat file, then port the flat file to SQL using BULK insert. If you need to do any preprocessing or cleaning of data, use Python (I use SAS with PROC SQL) before porting to SQL Server.

You can also port directly from the flat file to SQL Server and SQL Database with ODBC for Windows 7 or OLE DB for Windows 10 (OLE DB is more versatile).

Anonymous · Answer

I don't know how deep they wanted me to go. I could have explained further. What I have done is take in the information through Python. In there, I cleaned the data. This could be done in many different ways. For one, for a relational database, the information needs to be in 3rd normal form. Then, break up the information based on the original database and link up the primary keys. Then, have Python talking to SQL. Have it where the SQL code pushed the information out to the database based on how we broke up the data. I have also done this through Pentaho, but it has been a bit. If I had more time, I could have looked over how I did it through there. It was finals and I didn't have much time to prepare. I forgot about turning the Excel file into a CSV. Anyway, I've done the above many times, it has just been a while.

USAA

USAA Interview Question

Interview Answers

Want the inside scoop on your own company?

Bowls

Followed companies

Job searches