Import a CSV file with Access frontend into SQL Server

Question

Background:

In my company we have many CSV files which have to be imported into an SQL Server. The CSV files contain multidimensional market simulations, which are stored in an EAV form (2 columns and 10^6 to 10^10 rows). Their size is variable, but it is not unusual that it is more than 500Mb.

Until now, theses files were imported by an database administrator via SSMS into SQL Server.

Every importation should get an ImportationID and a Timestamp. This is time consuming and error prone for the database administrator who does this manually.

Thus, an Access front end is created to allow every user to import easily the CSV file into the server, after making a selection with a Listbox. Now, I am faced to the problem to import the CSV file through the Access interface.

Problem:

Here are the possible options which I have considered but which aren't possible :

• Pass some T-SQL command to the SQL Server, as listed here (not allowed by Access)
• Import the CSV line by line with a VBA loop (takes too long for 10^6 to 10^10 rows)
• Import the CSV file in the Access database and then export the table to the SQL Server (2Gb size limitation of Access makes it impossible)

Is there any other option to perform this task, using Access ?

Answer 1

One possible solution is as follows. Your Access frontend has a form that accepts three values: file name/location; ImportationID; Timestamp. After the user enters this data, the 'Go' or 'Submit' button fires a stored procedure on the SQL Server database that accepts these 3 variables.

The stored procedure will issue a BulkInsert (or other of the commands you linked to) to get the CSV into the database, and then manipulate the data and transform it according to your business rules (sets ImportationID and Timestamp correctly, for example).

This is something that a database developer (or maybe a database admin) should be able to set up, and any validation or security constraints can be enforced on the database.