csv not being read properly

Question

Dear Friends,

 I have a small csv.

When it contains all text it is being read by oledb.

But if it contains numbers it is not being read properly.

So if my csv is like this:

OLD_CUST, NEW_CUST
G,         H
I,    J
M,    N
65464653,  644646636
98765465,  988924212
G,    6546546

H.J,N are not being read.

1)How to tackle the above problem?

2) I have tried with ACE but with same result.

3) How to restrict the number of rows to be read eg 5000 when reading using OledbAdapter?

Thanks for your efforts.

---

Today's knowledge is too little for tomorrow's job

Answer 1

post your code.

---

Thanks

A place for MSDN users to socialize 

Living on Earth may be expensive, but did you know that it includes a free trip around the sun? Isn't that worth it?

Answer 2

Hi,

Here it is.

FileInfo

 

file = new FileInfo(flname);

 

string csvconnstr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\"" + file.DirectoryName + "\";Extended Properties=" + "\"text;HDR=Yes;FMT=Delimited(,)\";";

 

string csvconnstr1 = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=\"" + file.DirectoryName + "\";Extended Properties=" + "\"Excel11.0;HDR=Yes;FMT=Delimited\";";

 

OleDbConnection oledbconn = new OleDbConnection(csvconnstr1);

 

int res = 0;

 

DataTable tbl = new DataTable();

 

OleDbDataReader read;

 

 

string onlyname = Path.GetFileName(flname);

 

try

{

 

using (oledbconn)

{

 

using (OleDbCommand cmd = new OleDbCommand(string.Format

(

"SELECT * FROM [{0}]", file.Name), oledbconn))

{

 

using (OleDbDataAdapter adp = new OleDbDataAdapter(cmd))

{

tbl =

new DataTable("CSVTable");

adp.Fill(tbl);

 

//foreach (DataRow row in tbl.Rows)

 

//{

 

// Process the current row...

 

//}

}

 

//read = cmd.ExecuteReader();

}

 

}

res = 1;

}

 

catch (Exception ex)

{

logmsgf[1] = ex.StackTrace;

 

//ff.WriteToLog(iso, "CSVDBUpload/log/CSVDBUpload_log.txt", logmsgf);

res = ex.GetHashCode();

}

---

Today's knowledge is too little for tomorrow's job

Answer 3

Hello,

Sorry for late reply, I was busy with exams.

You need to create Schema.ini file in the same directory as exe file is located. This Schema.ini file will contain information about your data.

Here's sample ini file which I used for testing, works fine. Thing is, it was needed to tell Jet provider that second column is to be treated as Text.

Also [data.txt] at top is particular file name for which Schema is defined. OldC and NewC are columnNames I chose.

MaxScanRows is the buffer size to read number of lines at a time.

You'll find more information about Schema.ini in documentation : Link

 

Schema.ini file :

[data.txt]
Format=CSVDelimited
ColNameHeader=True
MaxScanRows=50
Col1=OldC Text
Col2=NewC Text

---

Thanks

A place for MSDN users to socialize  

Living on Earth may be expensive, but did you know that it includes a free trip around the sun? Isn't that worth it?

Answer 4

Hello,

Sorry for late reply, I was busy with exams.

You need to create Schema.ini file in the same directory as exe file is located. This Schema.ini file will contain information about your data.

Here's sample ini file which I used for testing, works fine. Thing is, it was needed to tell Jet provider that second column is to be treated as Text.

Also [data.txt] at top is particular file name for which Schema is defined. OldC and NewC are columnNames I chose.

MaxScanRows is the buffer size to read number of lines at a time.

You'll find more information about Schema.ini in documentation : Link

 

Schema.ini file :

[data.txt]
Format=CSVDelimited
ColNameHeader=True
MaxScanRows=50
Col1=OldC Text
Col2=NewC Text

---

Thanks

A place for MSDN users to socialize  

Living on Earth may be expensive, but did you know that it includes a free trip around the sun? Isn't that worth it?

I have already used schema.ini but with no effect.

You r telling to keep in the same directory as the exe. - R u sure abt this?

I had kept it in the same directory as my winforms,config files.

What is the purpose of MaxScanRows?

Is it optional?

Thanks for your reply.

---

Today's knowledge is too little for tomorrow's job

Answer 5

yup! I had tried with schema.ini before posting reply and it worked perfect for me.

Schema.ini was in same directory as my exe was. I tried in both /bin/debug and /bin/release modes.

and I mis-judged the MaxScanRows functionality in first post. Its a setting that tells how many rows should be scan to determine column type. Sorry for that.

 

 

---

Thanks

A place for MSDN users to socialize 

Living on Earth may be expensive, but did you know that it includes a free trip around the sun? Isn't that worth it?

Answer 6

Hi,

My csv is like this:

[Datafile1.csv]

ColNameHeader=True

Format=Delimited(,)

DateTimeFormat=dd-MM-yyyy

Col1=OLD_CUST Text Width 8

Col2=NEW_CUST Text Width 8

It didn't work even after placing it in bin/debug directory.

 

Then I changed it to:

[datafile1.txt]

Format=CSVDelimited

ColNameHeader=True

DateTimeFormat=dd-MM-yyyy

MaxScanRows=50

Col1=OLD_CUST Text Width 8

Col2=NEW_CUST Text Width 8

It still doesn't work.

I think the problem is with the provider i.e oledb.Is there a better provider which can overcome such issues.

---

Today's knowledge is too little for tomorrow's job

Answer 7

Schema.ini is for Jet provider not sure if applies to Ace.

Switch to Jet provider connection string and it should work.

---

Thanks

A place for MSDN users to socialize 

Living on Earth may be expensive, but did you know that it includes a free trip around the sun? Isn't that worth it?