1

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 ?

Improve this question
2
  • 1
    The page you links has code that should be executed as TSQL against a SQL Server database. I do not think you can use that code using Access and ADODB. I would imagine the correct way would be to import CSV to Access; manipulate/transform data in Access; populate the data to SQL Server in a staging table; fire sproc in SQL Server to ingest data. More details would be needed to help you with your problem. Commented Jan 15, 2014 at 23:01
  • Thanks, I have added some more details. The main problem is that the files are too big to be imported into Access, some are larger than the 2Gb limitation of Access. Commented Jan 15, 2014 at 23:27

1 Answer 1

Reset to default
2

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

This is a great idea ! I'll setup this with the db admin.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.