title: Loading files into Teradata
body: <p>Daily, I currently have to: </p>
  
  <ol>
  <li>download 50 files, 40 are in .csv (comma separated values) format, and 10 in .txt. </li>
  <li>In each of the .csv files, there are 5 columns (call them B, A, D, C, E), and a differernt 5 columns in the .txt files (call them G, H, I, J, K). </li>
  <li>I then have to manually open in Excel and rearange the .csv columns into (A, B, C, D, E) for each of the 40 files. The .txt file are already in the required format.</li>
  <li>I then load these all into 15 teradata tables, using BTEQ on unix</li>
  </ol>
  
  <p>There are approx 3000 rows of data</p>
  
  <p>I have, with the help of stackoverflow and my trial and error, created VBA which:</p>
  
  <ol>
  <li>Downloads all 50 files, </li>
  <li>rearranges columns correctly for the csv files, </li>
  <li>then on a new sheet that contains "INSERT INTO DBNAME.TABLENAME VALUES (A, B, C, D, E, F)" for the .csv files and "INSERT INTO DBNAME.TABLENAME VALUES (G, H, I, J, K)" for the .txt files, swaps A to K for actual values from all 50 files</li>
  <li>Create ODBC connection to teradata to loop through all 3000 created statements.</li>
  </ol>
  
  <p>It works, but it's painfully slow because it loops through 3000 or so rows. Because the teradata database is accessed by a large number of people at all times, everytime an error occurrs, my insert statements have to go to the back of the queue, adding to the time taken to complete the procedure.</p>
  
  <p>Is it possible to create VBA that will batch load the 50 files rather than my current method to create 3000 insert statements and loop?
  I have read about a process that imports into Teradata by using 'Jet Provider', but I know very little about this and my efforts so far have failed.</p>
  
  <p>Anyone know about this method or any other quicker method? I want to avoid BTEQ, MLOAD and can't FLOAD (because tables I load into are not empty).
  Thanks everyone.</p>