YoungStamos YoungStamos - 2 months ago 4x
C# Question

Looping through CSV files in a directory and inserting them into Access

I have a directory that contains a large number of CSV files. I want to loop through the directory and insert each file into a specific table based on their file name.

I am able to successfully loop through the files but I am having some trouble inserting the data into the Access database. I believe I am very close though. Any feedback would be appreciated.

This is my code:

string folderPath = Path.Combine(Path.GetTempPath(), "Reports");
string folderPath2 = Path.GetTempPath() + @"\Reports\";
var dir = new DirectoryInfo(folderPath);

foreach (var fileInfo in dir.EnumerateFiles("crashes_*.csv"))
OleDbCommand cmdBulk = new OleDbCommand(@"INSERT INTO tbl_crashes " +
@"SELECT * FROM [Text;FMT=Delimited;HDR=Yes;ACCDB=Yes;Database=" + folderPath2 + "].[" + Directory.GetFiles(folderPath2, "crash*.csv") + "]", MyConn);


This is the error that I am receiving:

Invalid bracketing of name 'System.String['.


Troubleshooting 101: When "something goes wrong", break the problem down to isolate the cause.

In this case, the error

Invalid bracketing of name 'System.String['.

is a pretty strong hint that there is something wrong with the CommandText string that we glued together, so let's look at the relevant bits of the string itself.

string folderpath2 = @"C:\__tmp\";
string str = "stuff [" + folderpath2 + "].[" + Directory.GetFiles(folderpath2, "zzzTest*.csv") + "] more_stuff";


stuff [C:\__tmp\].[System.String[]] more_stuff

so we can see that the string concatenation does an implicit ToString on the result of the GetFiles method call, returning "System.String[]". That is, GetFiles returns an array of strings and the implicit ToString shows us what the result is, not what it contains.

So that explains the error: The extra square brackets got the OleDb provider upset.

The solution, however, is to just use the Name property of the FileInfo object that the foreach loop is providing for us

string folderpath2 = @"C:\__tmp\";
var di = new DirectoryInfo(folderpath2);
foreach (FileInfo fi in di.EnumerateFiles("zzzTest*.csv"))
    string str = "stuff [" + folderpath2 + "].[" + fi.Name + "] more_stuff";


stuff [C:\__tmp\].[zzzTest1.csv] more_stuff
stuff [C:\__tmp\].[zzzTest2.csv] more_stuff

which is more like what we want.