uploading multiple csv files to SQL server in c#

Question

I'm a little new to coding and I am especially new to c# and visual studios. I am working on a project that I am hoping to be able to implement. Currently, I am using FileHelpers to read in a csv file and parse through and write the columns and values to the console. However, I need it to do something more complex. Each night a new csv file will be in a folder on my computer which I am doing through FTP. For my code right now, I am calling the file that I want to read in manually using: engine.ReadFile(@"c:\Users\guest\Documents\CSV\test.csv")

This was fine to begin with, but now my goal is to read in every file in that folder and send to a SQL Server. (It will still only be one file because my goal is to move the file to a different folder after it is read). How would I be able to read through and use all of the files. Would I just simply stop the directory at the folder? (i.e. @"c:\Users\guest\Documents\CSV). Also, it's not just one folder, I want to be able to read the files in one at a time from three different folders. Finally, is there anyway to implement the moving of the file after being read to a different folder specifically for files that have already been read. Thank you

Answer 1

I'm not really sure what you mean by I want to be able to read the files in one at a time from three different folders but here is a solution which will loop through the csv files of one folder, give you the option to read through each file and do what you need to do, then move the file to a new folder.

private void ProcessFilesCSVFiles(string copyPath, string destinationPath)
{
    // first check if path exists
    if (!Directory.Exists(copyPath))
        // doesn't exist then exit, can't copy from something that doesn't exist
        return;
    var copyPathDirectory = new DirectoryInfo(copyPath);
    // using the SearchOption.AllDirectories will search sub directories
    var copyPathCSVFiles = copyPathDirectory.GetFiles("*.csv", SearchOption.AllDirectories);
    for(var i = 0; i < copyPathCSVFiles.Length; i++)
    {
        // get the file
        var csvFile = copyPathCSVFiles[i];
        // read the csv file line by line
        using (StreamReader sr = csvFile.OpenText())
        {
            string line = "";
            while ((line = sr.ReadLine()) != null)
            {
                // use split to read the individual columns
                // will fail if the text field has a comma in it
                var split = line.Split(',');
                Console.WriteLine(line);
            }
        }
        // do other sql mojo here if needed

        // move the files over to another place
        var destinationFilePath = Path.Combine(destinationPath, csvFile.Name);
        if (File.Exists(destinationFilePath))
        {
            File.Delete(destinationFilePath);
        }
        csvFile.MoveTo(destinationFilePath);
    }
}

Which would be called using something like this:

ProcessFilesCSVFiles(@"C:\data\copypath", @"C:\data\destinationpath");

Answer 2

Take a look at this related question.

You'll want to make some changes, such as changing the file extension to ".csv" and altering the destination file path.

foreach (var file in d.GetFiles("*.csv"))
{
    //Load CSV into SQL using existing code
    Directory.Move(file.FullName, filepath + "\\TextFiles\\" + file.Name);
}