Import textfiles into linked SQL Server tables in Access

1
votes

I have an Access (2010) database (front-end) with linked SQL Server-tables (back-end). And I need to import text files into these tables. These text files are very large (some have more than 200.000 records, and approx. 20 fields)

The problem is that I can't import the tex tfiles directly in the SQL tables. Some files contain empty lines at the start, and some other lines that I don't want to import in the tables. So here's what I did in my Access database:

1) I created a link to the text files.

2) I also have a link to the SQL Server tables

3a) I created an Append-query that copies the records from the linked text file to the linked SQLServer table.

3b) I created a VBA-code that opens both tables, and copies the records from the text file in the SQL Server-table, record for record. (I tried it in different ways: with DAO and ADODB).

[Step 3a and 3b are two different ways how I tried to import the data. I use one of them, not both. I prefer option 3b, because I can run a counter in statusbar to see how many records needs to be imported at any moment; I can see how far he is.]

The problem is that it takes a lot of time to run it... and I mean a LOT of time: 3 hours for a file with 70.000 records and 20 fields! When I do the same with an Access-table (from TXT to Access), it's much faster.

I have 15 tables like this (with even more records), and I need to make these imports every day. I run this procedure automatically every night (between 20:00 and 6:00).

Is there an easier way to do this? What is the best way to do this?

1
sql-serverms-accessimporttext-files
Is using SSIS an option? – Joris Van Regemortel
Have you tried importing the data from a text file into a local Access table and then using a single INSERT INTO LinkedTable SELECT * FROM LocalTable to copy the data to the SQL Server table? – Gord Thompson
You can always accept the answer, if it answered your question :) – Joris Van Regemortel

1 Answers

2
votes

This feels like a good case for SSIS to me.

You can create a data flow from a flat file (as the data source) to a SQL DB (as the destination).

You can add some validation or selection steps in between.

You can easily find tutorials like this one online.

Alternatively, you can do what Gord mentioned, and import the data from a text file into a local Access table and then using a single INSERT INTO LinkedTable SELECT * FROM LocalTable to copy the data to the SQL Server table.