0

Currently I am working on a project that requires me to read from a .csv file and import the data into a database. Its all working fine except for just one column. In the raw/.csv file this field can consist of both int/string. So while reading the file if the first column consists of an int then any of the strings that come after wont be read. But if the first value happens to be a string then it all works as intended. Is there a solution for this that I could implement into the current working solution I have.

string sql = @"SELECT * FROM [" + strFileName + "]";    
DataTable rawDataTable = new DataTable();

using (OleDbConnection connection = new OleDbConnection(connString))
using (OleDbCommand command = new OleDbCommand(sql, connection))
using (OleDbDataAdapter adapter = new OleDbDataAdapter(command))
{
    adapter.Fill(rawDataTable);
}

For example, in the specific column: In the following case, when read using the code mentioned above, the datatable would show the 100 leaving the next two rows blank and then would show 500.

Sample Column
-------------
100
SOME TEXT
SOME MORE TEXT
500

But if, the column happens to be like the following sample it would work properly by reading the ints as strings.

Sample Column
--------------
100
SOME TEXT
SOME MORE TEXT
500
Pranoy
  • 3
  • 4
  • worth trying: https://stackoverflow.com/a/24933442/578411 – rene Feb 15 '19 at 22:04
  • Possible duplicate of https://stackoverflow.com/questions/1688497/load-csv-into-oledb-and-force-all-inferred-datatypes-to-string?rq=1; – shubham Feb 16 '19 at 05:52
  • thanks @shubham I used a similar method to that mentioned in your response, I used the exact method that is mentioned in the answer by steve which works well for me. Thank you. Also regarding changing the IMEX and Registry key values, I do not have rights to access registry on this machine as its a company machine. So i had to go with the solution mentioned in the answer. – Pranoy Feb 19 '19 at 14:08

1 Answers1

0

If you are using the OleDb Text File driver, which it looks as though you are, you can use a Schema.ini file to tell it to scan all rows in the file to decide the type.

For example, if your connection string looks like this:

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\txtFilesFolder\;
Extended Properties="text;HDR=Yes;FMT=Delimited";

Then in c:\txtFilesFolder create a file called Schema.ini, and if your file name is myfile.csv, the content of the schema file would be:

[myfile.csv]
MaxScanRows=0

Personally this approach in general wouldn't be how I would parse text files, there are loads of good libraries to do so which will give you more control.

steve16351
  • 5,372
  • 2
  • 16
  • 29
  • Thank You!... This works perfectly. Yeah I considered using other libraries but I decided to not use external libraries and use the ones that comes included. – Pranoy Feb 19 '19 at 13:58