0

When I try to read a .csv file. I can see some of the empty rows but I have data in the .cvs file.

But when I open and save it manually the .csv file and then I can able to read the file.

Those rows which are not working, I can see some double quotes in the some of the cells

Could you please someone tell me whats happening here?

My Code : 

OleDBConn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strFilePath + ";Extended Properties=\"Text;HDR=YES;IMEX=1;FMT=Delimited\"");
OleDBConn.Open();
OleCmd.Connection = OleDBConn;

OleCmd.CommandText = @"SELECT [SFDC Unique Opportunity ID Ref] as [ID],[Trace Account Number] as [ACCOUNT_TRACE_ACCOUNT_NUMBER__C],[NAME], 
[SFDC Unique Account ID ref] as [ACCOUNT_ID],[Account Name] as [ACCOUNT_NAME] ,[Contract Start Date] as [CONTRACT_START_DATE__C],
[Contract End Date] as [CONTRACT_END_DATE__C],[Pricing Method] as [PRICINGMETHOD__C],[Source] as [LEADSOURCE],
[Contracting Country] as [ACCOUNT_BILLINGCOUNTRY],[Region] as [ACCOUNT_REGION__C],[Currency] as [CURRENCYISOCODE],
[Closed Date/Decision Date] as [CLOSEDATE],[Owner] as [OWNER_ID],[Stage Name] as [STAGENAME],
[TRACE Opportunity GUID] as [TRACE_Opportunity_GUID],[Trace Master Deal] as [Trace_Master_Deal], [Current Stage] as Status, 
[Vertical/Sales Owner] as Vertical_Sales_Owner,[Manual Probability] as Manual_Probability FROM " + AppiFormattedFilename; //strsplit[0] + ".csv";//"TRACEOPPORTUNITIES_2017-03-21.csv"; 

OleCmd.CommandType = CommandType.Text;
OleAdptr.SelectCommand = OleCmd;

OleAdptr.Fill(dtDumpData);
boop_the_snoot
  • 3,209
  • 4
  • 33
  • 44
Ramesh Raja N M
  • 1
  • 1
  • 1
    You should try to read the CSV with CSVHelper instead https://joshclose.github.io/CsvHelper/ . There's more to CSVs than just commas. Escaped text can throw your query for a loop. – Matt Oct 06 '17 at 04:10
  • Why can you not use File.ReadAllLines or File.ReadLines for CSV reading. – Amit Kumar Singh Oct 06 '17 at 05:03
  • I don't see how we can help you without a [mcve] showing the CSV file. In the meantime you could try an alternate way of parsing such as one of the ones in [Reading CSV files using C#](https://stackoverflow.com/q/3507498) or [Parsing CSV files in C#, with header](https://stackoverflow.com/q/2081418). – dbc Oct 06 '17 at 05:50

0 Answers0