0

I am trying to import data from a .CSV file into SQL Server automatically. For example, if I have a set of .CSV files in a folder, I want the data from those .CSV files to be imported into SQL Server automatically, every time I add a new .CSV file to that folder.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
A Trivedi
  • 41
  • 1
  • 1
  • 6
  • Possible duplicate of [Automatically importing data into SQL Server from .CSV file](https://stackoverflow.com/questions/47271933/automatically-importing-data-into-sql-server-from-csv-file) – SMor Nov 14 '17 at 00:30
  • maybe this can help https://dba.stackexchange.com/questions/143215/read-csv-and-perform-sql-in-sql-server-management-studio – dllhell Nov 14 '17 at 07:52
  • May be [following](https://stackoverflow.com/q/61012585/6165594) can help (If you don't want to use `BULK NSERT` or don't have permissions for it). – Denis Apr 08 '20 at 13:27

2 Answers2

1

If you want it to be truly automatic, you will need to go beyond SQL. Otherwise, scheduling a load from the csv to your database would require a simple SSIS package. Then you can go and schedule a job using SQL Server Agent to import the data daily, weekly, hourly, etc.

Andrew
  • 373
  • 2
  • 8
  • Can you please explain in more detail how I can schedule a load from the CSV to my database? I have not used an SSIS package before so what is it that I need to do? Thank you in advance. – A Trivedi Nov 13 '17 at 21:38
  • 1
    SSIS isn't that bad. Grab a community edition of Visual Studio https://www.visualstudio.com/downloads/ and also make sure to download SQL Server Data Tools. You'll want to create an integration package. Setting up your connection will be the trickiest part but isn't bad. Your source will be a flat file. Your destination will be your SQL Server table. If your aren't the admin on the database, you might not be able to see the SQL Server Agent. Quick tutorial https://www.youtube.com/watch?v=b25r0jDseOQ – Andrew Nov 14 '17 at 20:05
-2

EDITED!! THIS IS FOR PHP

$filenames = glob($_SERVER['DOCUMENT_ROOT'] .'/filespath/*');
foreach($filenames as $filename){ // iterate files
    if(!file_exists($filename) || substr($filename,-3) == 'csv') continue;
    $file = fopen($filename, "r");
    $csv = array();
    while (($line = fgetcsv($file)) !== FALSE) {
      //$line is an array of the csv elements
      $csv[] = $line;
    }
    fclose($file);
    foreach ($csv as $key => $value) {
      //put your code here to work with $csv array.
    }
}

You can use this code to get what you want. You also need cron to automate your code. You can get it in your host cpanel.

Arman Danielyan
  • 17
  • 8
  • I am still confused on how to do this? So is the above code a script that I should run? – A Trivedi Nov 13 '17 at 23:35
  • First line is getting all files from your specified folder. After we have a loop for each file, If is checking is file exist or not, and it must have csv extension..If not then skip this file. if yes go to next line. Then creating $csv array to add there your file.csv lines. And for result you have $csv array. – Arman Danielyan Nov 14 '17 at 07:43