0

I'm trying to import a bunch of ach files and make a big sql table. An ach file is a text file with transaction information arranged in columns. The problem is that I need to add a date column. Currently the date is only contained in the file name and header. There are about 3000 files and each file is a different date.

I have basic knowledge of sql commands and how to query a database, but I just started learning about importing data for this project. The only tool I found is the program called "import and export data" as a part of sql server 2012. It allows me to import the text file and make it into a table.

The problem is that I have to import the text file and create the table. Then I add a column for the date, do

update table
set date='date'

then I can combine tables with an insert command. The do it again 3000 times.

Is there a better way?

user3634056
  • 51
  • 1
  • 8
  • You can definitely do that in SSIS. Take a look here for the first part: enumerating files in a folder and importing them: http://www.mssqltips.com/sqlservertip/2874/loop-through-flat-files-in-sql-server-integration-services/. Build that and post back for more help. Just one important question: do all the files have the same number of columns? – Nick.Mc May 13 '14 at 23:52

1 Answers1

0
  1. Write a program that can open all files in directory.
  2. Extend that program to parse ach files.
  3. Extend that program to get date from file name.
  4. Extend that program to write to database.

I'd say it's 3 hours of work.

Community
  • 1
  • 1
Matas Vaitkevicius
  • 58,075
  • 31
  • 238
  • 265
  • I appreciate your response, though I don't know anything about C#. How complex of a thing is it to learn? – user3634056 May 13 '14 at 22:09
  • @user3634056 I believe it will be faster to learn limited amount of C# that will allow to write this program, compared to manually processing 3k of files. – Matas Vaitkevicius May 13 '14 at 22:18
  • Is there a recommended website or book to look at to get started? – user3634056 May 13 '14 at 23:25
  • You can do all those things in SSIS also. – Nick.Mc May 13 '14 at 23:52
  • If it can be done in SSIS, do you mind saying how, or pointing me to a place where I could learn it? – user3634056 May 14 '14 at 00:13
  • @ElectricLlama and how exactly would you extract date from file name in SSIS? – Matas Vaitkevicius May 14 '14 at 06:35
  • @user3634056 start by downloading Visual Studio Express http://www.visualstudio.com/downloads/download-visual-studio-vs#d-express-web and SQL server http://www.microsoft.com/en-gb/download/details.aspx?id=29062 then create project in visual studio, do steps 1 - 4 and you should be done. – Matas Vaitkevicius May 14 '14 at 06:38
  • If you go through the link I sent (a comment against your question) you'll see that the filename is captured in an SSIS variable, which you can subsequently use in your data flow and add to a table. Why don't you go through the demo linked in my other comment and add some more specific questions. – Nick.Mc May 14 '14 at 11:06
  • @ElectricLlama my apologies. I believe your answer is more correct than mine, I will leave my answer in case user does not have access to non Express edition of SQL Server, but otherwise you are absolutely correct and I am not. I suggest you post it as answer. – Matas Vaitkevicius May 14 '14 at 11:25
  • Not at all, there are many solutions for a problem and by all means in some circumstances yours is definitely more practical than mine. SSIS definitely has its limitations. In the end it's the OP's choice. – Nick.Mc May 14 '14 at 11:27