Fabrice's weblog

Tools and Source

News

My .NET Toolbox
An error occured. See the script errors signaled by your web browser.
No tools selected yet
.NET tools by SharpToolbox.com

Read sample chapters or buy LINQ in Action now!
Our LINQ book is also available on AMAZON

.NET jobs

Emplois .NET

Tuneo

ASP.NET Hosting transatlantys

Contact

Me

Others

Selected content

Archives

Manipulating CSV files

It doesn't seem to be well known that you can easily access data in CSV files (Comma-Separated Values) using ADO.NET components. For example, you can use the OleDbConnection, OleDbCommand, and OleDbDataAdapter objects to fill a DataSet from a CSV file. You can also update the data. Also notice that it is possible to use Visual Studio's Server Explorer to visualize or edit CSV files.

All you need is to use the Jet engine thanks to a connection string like the following one:

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

Note: "HDR=Yes" indicates that the first row contains columnnames, not data (source: connectionstrings.com).

Once you have a connection to a directory, you can refer to files as tables as follows:

"SELECT * FROM data.txt"

Hint: Think about JOINs if you need them...

So, why not use ADO.NET's power to access CSV files instead of coding this all by yourself?

Update: this post seems very popular, so I guess it's useful that I add two links about the Schema.ini file for more information:

Update: interesting comment by David:

Recap: Reading columns with mixed data types (that is, with data that could appear to a stupid OleDB driver to be of two types), you must use registry modifications AND extended properties on the connection string.

For Excel, the connection string is:

string.Format(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};" + @" Extended Properties=""{1}""", pathName, "Excel 8.0;HDR=YES;IMEX=1");

In the registry, set:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel\ImportMixedTypes = Text

and

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel\TypeGuessRows = 0

For CSV, the connection string is:

string.Format(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};" + @" Extended Properties=""{1}""", path, "Text;HDR=YES;FMT=Delimited;IMEX=1");

In the registry, set:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Text\ImportMixedTypes = "Text"

and

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Text\MaxScanRows = "0"

Comments

Phil Scott said:

We still get data via CSV and this is what I've been doing. God bless connectionstrings.com
# October 1, 2003 10:25 AM

Fabrice said:

Hadn't seen your post Don!
You beat me on this one ;-)
# October 1, 2003 10:37 AM

Eric said:

A couple of drawbacks on reading CSV files...
1. The CSV file has to be on a disk that has Read/Write capabilities (i.e. not on a CD)
2. Trying to read >100,000 records kinda locks up the process (I have waited 10 minutes and it still was not finished). Under 100,000 records is pretty quick.

Eric
# October 1, 2003 11:06 AM

Christophe Lauer said:

Hi Fabrice,

Were you kidding, or you found a way to use joins between CSV files/tables? I just can't make joins work :-(

This short sample will be really useful for quick demos ;-) Thx!
# October 1, 2003 6:37 PM

Fabrice said:

Joins work for me.
Sample SQL:

SELECT A.FieldA1, B.BieldB1
FROM ([B#txt] B INNER JOIN [A#txt] A ON B.FieldB2 = A.FieldA2)

Christophe, did you try without using the JOIN syntax, simply with '='? Maybe it would work better, dunno.
# October 2, 2003 5:11 AM

Christophe Lauer said:

Stoooopid me! I had a "syntax error" (c)(tm) in my second CSV file ;-) Both JOIN and "=" syntax do work. Thx!
# October 2, 2003 6:31 AM

Fabrice said:

Voila, avec un article c'est tout de suite plus clair :-)
To be noted that since that post, I've used this technique for inserts as well and it works fine.
# November 11, 2003 11:13 AM

Serdar Kilic said:

I'm getting an OleDbException when trying to use this, the message source stating "Could not find installable ISAM."

has anyone got this working on W2k3 ?
# November 11, 2003 5:33 PM

Desp4Ans said:

Can one connect to CSV files on remote folders?
Please let me know
# November 19, 2003 6:57 AM

tfertal@khov.com said:

I am having trouble getting LIKE to work (e.g., "... WHERE field LIKE 'ABC*'") when trying to access data in a CSV. Have you run across this?
# January 5, 2004 5:42 PM

mohan said:

how its possible in Linux?
# January 22, 2004 7:45 AM

Mark said:

I've got reading down can somebody help me with updating the csv file.
# February 19, 2004 1:38 PM

Oran said:

When I tried to update, I got "Updating data in a linked table is not supported by this ISAM." When I did some searches, I found Microsoft saying, in a similar situation, that "ODBC drivers do not allow for the SQL statements UPDATE and DELETE."

Has anyone seen it work? It seems that CSVs can be read like a database, but not updated....
# February 25, 2004 6:15 PM

Fabrice said:

Oran, you can insert for sure, but maybe you cannot update or delete. I don't remember.
A quick test would be to try updating a CSV table with Visual Studio's Server Explorer. I think it worked last time I tried.
# February 25, 2004 6:24 PM

M Kumar said:

This works fine as long as my file name is simple...but once it has character like '-' it fails. How do I get around this problem?
# March 5, 2004 7:25 AM

M Kumar said:

I've got around the problem....I used MSDASQL.1 instead of Microsoft.Jet.OLEDB.4
and ADODB connection class instead of OLEDBConnection.
# March 8, 2004 6:42 AM

Tejus Sawjiani said:

Thanks so very much ... had to go through a lot of websites to get a CSV connection string, and yours was the first one that worked ;-)
# March 30, 2004 12:45 AM

Mohan Ekambaram said:

Hi,

I'm getting unspecified error in the 2nd line when opening the oledbconnection

conn = new OleDbConnection(strConnectString);
conn.Open();

Connection string is like
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\TxtFilesFolder\;Extended Properties='text;"
i've installed the latest mdac 2.8, updated the service pack to 8 for the Jet driver. Changed the permission in the folder. still i'm getting the same error.

Can some tell me how to fix the issue...


# April 15, 2004 5:06 PM

Johan Hendrik said:

Can somebody help me with this problem.
I have an CSV file with multiple columns. The strange part is that the dataset is filled with only the first column ?

I tried it with a excel file and all data was read from it into the dataset with the right columns ?

Thanks,


# April 22, 2004 10:35 AM

Eric said:

I'm have a problem where the driver is converting a small decimal to scientific notation. The actual value in the column is .000001 and its showing up as -1e-6

Is there anyway to force the driver to import as text? I've tried it both with csv and excel files.
# April 28, 2004 5:04 PM

Fabrice said:

Eric, did you try to play with the Schema.ini file to type the column as text?
# April 28, 2004 5:15 PM

Eric said:

I found the problem. I was converting everything to string. Once the datatable was filled, the driver made the column of type Decimal. If i pulled the value out as decimal, i am fine...........
# April 28, 2004 5:18 PM

Daniel said:

Hi, just wondering if this works with the ADO, in stead of the ADO.NET. Has anyone tried that with the ADO object? Thanks.
# May 3, 2004 9:39 PM

leshka said:

For importing CSV I must have write permissions for this operation, because I need to write schema where :( Is there some way to get it on read-only drive?
# May 6, 2004 10:25 AM

Moran Ben-David said:

I found that using this driver filenames like test.1083952525531.txt don't work. Anyone else find this?

I end up getting the following exception when i run my "select * from test.1083952525531.txt" command

.Data.OleDb.OleDbException: The Microsoft Jet database engine could not find the object 'test.1083952525531.txt'. Make sure the object exists and that you spell its name and the path name correctly.

when i change the file and select statement to testT1083952525531.txt.. it works..

strange
# May 7, 2004 2:06 PM

DJ said:

I have a problem converting the CSV file into the DataSet.
The problem is that if the first few records of a column in the CSV file are numeric, the datatype of the column in the DataSet automatically gets set as Int.
So lets say after 10 records of numeric, if the eleventh record is alpha numeric, that does not get filled in the data set at all.
# May 11, 2004 5:50 AM

Fabrice said:

"if the first few records of a column in the CSV file are numeric"

All your records should have the same columns and column types. The type is not supposed to change depending on the record. This is a requirement for every database, not just CSV files.
# May 11, 2004 6:18 PM

DJ said:

I used Oledb provider to convert the CSV file to DataSet. I did not define the columns & their types in the DataSet as there are hundreds of columns. As you correctly pointed out, the data type of all the records in the CSV file is text.
But as they get filled in the dataSet, the columntype gets defined as Int just because the first few records in the CSV file contain no alphanumeric characters(though they are text).
For example, the first 10 records' data look something like this. 1000,2000,3000,and so on and the eleventh record will have something like 3000-AV.
Hope I am clear in explaining the problem.
# May 12, 2004 6:43 AM

Fabrice said:

I get it. I think you have to use the Schema.ini file to specify that you want to access the columns as plain text.
# May 12, 2004 11:54 AM

Stefan said:

Hi Is there a way to define the schema when importing Excel files instead of CSV files?
# May 22, 2004 7:03 AM

Mikey said:

If you publish a web page to the intranet you cannot specify the drive letter. ie (c:/TextFolder). Does anyone know how to get round this?
# June 2, 2004 10:10 AM

Mikey said:

Let me clarify. The text data to be imported is on the web. How do you specify the path ?
# June 2, 2004 10:23 AM

JoeB said:

I have had trouble Deleting and Updating records in a CSV file. I can Insert OK. When I delete I get the exception "Deleting data in a linked table is not supported by this ISAM." For updates I get "Updating data in a linked table is not supported by this ISAM."
# June 4, 2004 5:49 PM

Yogi said:

"select * from file[1].csv" this query is not working. anyone tell me the solution for this.
# July 7, 2004 2:23 AM

Jim said:

Does anyone have a good solution to DJ's problem mentioned earlier in this post? I have run into the same problem, but I don't like the answer he was given. He specifically mentioned that he had hundreds of columns to work with which makes the schema.ini solution quite a bit of work. Any other ideas?
# July 13, 2004 11:36 AM

Jeff said:

has anyone seen setting HDR=NO yet the first row of the CSV is still treated as column headers?

# August 9, 2004 5:51 PM

Flora said:

This was extremely helpful and saved me lots and lots of headache.
10x :-)

P.S. everything works properly like in the first example….
# June 6, 2006 5:13 AM

Tom said:

Great way to read a CSV file! However, I ran into a small problem. The text engine always ignores the leading zeros in the CSV file. For example, 001234 will be read as 1234. How do I force it to leave the leading zeros?

Thanks for your help!
# June 14, 2006 9:47 PM

Tom said:

I found a rather simple workaround solution that may help someone. Using the Registry Editor to set:

ImportMixedTypes=Text

for this key:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Text

The default setting for ImportMixedTypes is Majority Type.
# June 15, 2006 9:26 AM

dan m said:

if the csv file has an erroneous quote or some other bit of data that the driver can't resolve I dont get any data returned for the rest of the line, fair enough but does anyone know if I can find out when this occurs (i.e. does the driver set some property or error status to indicate that it couldnt resolve the line, I cant find anything but would have thought it would be exposed...  :-(

# August 3, 2006 5:57 PM

Fabrice Marguerie said:

Maybe libraries like FileHelpers can help?

See http://filehelpers.sourceforge.net

# August 3, 2006 8:35 PM

dan m said:

Thanks Fabrice, I will have a look!  ;-)

# August 4, 2006 3:45 AM

Ronto said:

Has anyone had any issues with text case sensitivity?  My data files contain related records, however are different case or mixed case. (E.g. PETER, peter, Peter).  

Unfortunately, my SQL query is returning different results when I specify WHERE Name='PETER' vs. WHERE Name='peter'.  Is there anyway to configure the connection to ingore case and return all records regardless of case?  

# August 10, 2006 7:51 PM

Dejo said:

Great post. I have been able to pull a csv wholesale into a datatable. However I have not been able to select specific columns by name (I use HDR=YES). For instance I have headings 'phonenumber', 'cost', 'date' and some other columns, but only want to select those three. How do I do that by name, independent of the other they are in the file? Thanks.

# August 14, 2006 9:59 AM

Nick said:

M Kumar wrote: "This works fine as long as my file name is simple...but once it has character like '-' it fails. How do I get around this problem?" The same way you get around any special characters in table names. Enclose the table name (file name in this case) in []'s like this: "SELECT * FROM [Some-file.txt]"
# September 22, 2006 9:57 AM

Jeff said:

Fantastic post. As others have said, this has saved me hours of time and several hundred bucks potentially purchasing a third party component to parse csv files! Thanks again!
# October 1, 2006 11:02 PM

Rashmi said:

I am trying to fill a daaset from a .csv file. The connection string I am using:"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\\myFolder\\Processed\\; Extended Properties=text;";

Select command I am using :objAdapter.SelectCommand = new OleDbCommand("SELECT * FROM " + FileName + ".csv", objConn); and after this objAdapter.Fill(objDataset, inputFileName);.  

After filling DataSet contains same no of columns as in .csv file but headers contain absurd values and also all the data row columns are blank.

I tried adding HRD=Yes in connection string but that also didn't solve my problem.

Can somebody please help me out as soon as possible. Looking forward for help

# December 30, 2006 6:07 AM

Rashmi said:

My problem solved. This was because i was creating .csv in excel directly. When i changed the format and created the file using notepad and saved with extension .csv it works fine :)....

# December 31, 2006 1:42 AM

Niklas said:

Great post! But I only get 1 column per row, consisting of one string (example "A,,,1,62,,080") instead of each column getting its own value. Do you have any idea to what may cause this?

# February 6, 2007 2:24 PM

ShadowFil said:

Hello,

I would want to know if today, there a best way to load data from a CSV ?

Otherwise, I need to load a part of a CSV file in a DataTable, and I don't know how to do.

In my CVS file, I have in first one line for information,then one empty line, then n lines (n is never the same), then an empty line, then the table to load in the DataTable. But in the table, I don't want the first 4 columns.

After I have an empty line and another table I need to load.

So, How to do to do a "SELECT * FROM" to get only the first table or only the second table, without others lines which are not table ?

Thank you for your help.

# February 15, 2007 8:32 AM

Hans de Koster said:

When you've got a funny filename, enclose the filename in squre brackets:

"select * from [My.Funny_File.Name.csv]"

# June 12, 2007 3:20 AM

Maryam said:

hi every one

I've got this error when trying to open oledb connection in a asp.net webpage.: "Could not find installable ISAM."

It's my code:

OleDbConnection cn = new OleDbConnection();

           string csvLocation = Server.MapPath("ipcountry.csv");

           string cnSr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + csvLocation + ";Extended Properties='text';HDR='Yes';FMT='Delimited';";

           cn.ConnectionString = cnSr;

           string olestr;

           olestr = "select * from ipcountry.txt";

           OleDbCommand cmd = new OleDbCommand(olestr, cn);

           DataSet ds = new DataSet();

           OleDbDataAdapter Objadr = new OleDbDataAdapter(cmd);

           cn.Open();

           Objadr.Fill(ds, "T");

           cn.Close();

# July 3, 2007 4:27 AM

Jarrod said:

I am using this kind of connection with a SQLBulkCopy and running into problems. The select query seems to convert SSN's like "003-15-1984" to a DATETIME which is causing me huge headaches. I can't seem to use CONVERT(VARCHAR, SSN) in the select statement. Anyone have any ideas?

Thanks in advance

# July 12, 2007 9:37 AM

Jarrod said:

To Maryam,

I think I ran into your problem as well... The believe the way you have the extended properties written is wrong. Try changing from...

Extended Properties='text';HDR='Yes';FMT='Delimited'

To...

Extended Properties='text;HDR=Yes;FMT=Delimited'

You have the extended properties broken up by apostrophes. Hope this works for you.

Jarrod

# July 12, 2007 6:01 PM

Maryam said:

To Jarrod

Thanks alot. I will try it.but my programming OS is Windows 2003 Server. is it possible that oledb drivers on this OS lead to this problem?

# July 31, 2007 4:11 AM

Maryam said:

To Jarrod

I changed the Connection string as follow:

string cnStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + csvLocation + ";Extended Properties='text;HDR=Yes;FMT=Delimited';OLEDB:Max Buffer Size=256;";

But Unfortunately still I get the Same "Could not find installable ISAM." Error

# July 31, 2007 4:17 AM

Fabrice Marguerie said:

I've fixed the broken links

# August 1, 2007 8:55 PM

RayBez said:

Is there a limit to the file name length?  

I have some wierd file names that I need to work with and it seems that either the file name length or the naming convention is causing an exception of: "Syntax error in FROM clause."

# August 9, 2007 2:48 PM

Kay Castillo said:

I'm getting the csv to import just fine; however, my schema.ini isn't picking up the date format.  

The date appears as 2007-08-04 11:23:12.230.  

In the schema.ini file, I've included "DateTimeFormat=yyyy-mm-dd hh:nn:ss.fff" and Col3=RecDate date width 23

I've tried a dozen different ways to format the hours/minutes/seconds.  Regardless, it's returning a blank field.  Any help will be appreciated because I don't want to use a stream reader and parse all those fields.  Thanks!

# August 28, 2007 9:56 PM

Fabrice Marguerie said:

Here is the updated link from DonXML: weblogs.asp.net/.../24908.aspx

# November 24, 2007 8:37 AM

xxx said:

Can you please give the detailed solution to solve the DJ problem previuosly

# November 30, 2007 5:14 AM

jan said:

hi,

1- I got a task to read a sales.csv file and transfer all data to sql server 2005 table caled 'sales'

2- then read all records to datagrid with DELETE & UPDATE facility.

Does anybody knows the correct cod as Im new in .net.

I'll appricated an early reply.

Thanks in advance to all guys.

Jan

# January 18, 2008 11:41 AM

Francesco said:

Hi folks,

I have a problem during to read a file .csv:

My file is like :

field1;field2;field3

dog;ccff45;456

cat;45556;3445

bird;3333;dedede

The result is complete but i don't see the field2 for the first row, like:

field1;field2;field3

dog;;456

cat;45556;3445

bird;3333;dedede

Somebody can Help Me?

# January 28, 2008 5:58 PM

anyoneis said:

Recap: Reading columns with mixed data types (that is, with data that could appear to a stupid OleDB driver to be of two types), you must use registry modifications AND extended properties on the connection string.

For Excel, the connection string is:

string.Format(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};" + @" Extended Properties=""{1}""", pathName, "Excel 8.0;HDR=YES;IMEX=1");

In the registry, set:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel\ImportMixedTypes = Text

and

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel\TypeGuessRows = 0

For CSV, the connection string is:

string.Format(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};" + @" Extended Properties=""{1}""", path, "Text;HDR=YES;FMT=Delimited;IMEX=1");

In the registry, set:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Text\ImportMixedTypes = "Text"

and

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Text\MaxScanRows = "0"

David

# February 12, 2008 6:29 PM

Fabrice Marguerie said:

Thanks David. This should help a lot of people.

# February 13, 2008 3:47 AM

Charlie said:

Thats great, but what is the default path for the datasource.  My csv file is in source control, so I need a relative path.  DeploymentItem Attribute deploys the file from SC to cwd [Out], but Jet/ADO cannot find it.

# February 14, 2008 11:47 AM

KOOSHKA said:

Hi guys, could any of you please help me?

For something like:

Dim strSQL As String = "SELECT * FROM [" & getFileName & "]"

I have a connection string like:

ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & getParentFolderName & "; Extended Properties=""Text;HDR=Yes;FMT=CSVDelimited"""

But when i want to use a join Select like:

SELECT * FROM ([B#txt] B JOIN [A#txt] A

how do you build the connection string?

# February 21, 2008 11:58 AM

Fabrice Marguerie said:

If you don't know what the Windows registry is, you should first learn about it before making any changes to it. If you do something wrong with it, you can damage your Windows or software settings.

# May 27, 2008 11:57 AM

me_just_me said:

thks I will try something diferent

# May 27, 2008 12:22 PM

ScottDog9 said:

I am running into a limit on field lengths.  Using ADODB in a COM object environment, fields that are greater than 512 bytes cannot be read.

Changing the DefinedSize property of the Field object does not appear to solve the problem.

Is there an inherent limitation on the size of fields that you can read from CSV files?

Thanks in advance for any help.

# June 17, 2008 10:07 AM

Kiran said:

I am using this to imports CSV file data into datatable in one of my website. I am facing strange problem. With the same csv file sometime the code works fine, whereas sometime it throws error : unspecified error. I have tried all the solutions (giving rights to folder etc..) but still no luck! I am using OleDBconnection ADO.NET Here is the exception detail. The errors come at dataadapter.fill line

Unspecified error

Source : Microsoft JET Database Engine

Exception Details: System.Data.OleDb.OleDbException

Inner Exception: No Innner exception could be found.

# July 11, 2008 2:34 AM

Huck said:

@Kiran - maybe what you need is to google

# September 19, 2008 5:57 AM

Jack said:

How can i user select to get top 3 or 4 rows from CSV file

# November 19, 2008 3:31 AM

Andreas Warberg said:

I have a text file with a DateTime field.

Until now the precision has been in seconds and I successfully used this schema.ini line for field specification:

DateTimeFormat=yyyy-mm-dd hh:nn:ss

Jet would interpret the field as DateTime and all is well.

Now the precision has been upped to milliseconds. I tried to use this specification:

DateTimeFormat=yyyy-mm-dd hh:nn:ss.fff

But Jet just interprets the values of the field as text.

Here are some samples of the values in the field:

2007-05-03 11:07:16.270

2007-05-03 11:08:16.035

2007-08-13 14:43:22.779

What is the correct format to get the millisecond component?

Best regards

Andreas

# January 8, 2009 12:08 PM

OJ said:

I've encountered the same issue as with Moran Ben-David.  Even when the file name is enclosed in brackets, I still get the OLEDB exception:

The Microsoft Jet database engine could not find the object 'test.1083952525531.txt'. Make sure the object exists and that you spell its name and the path name correctly.

I've read elsewhere that this is known issue with the text driver, but haven't seen any KB articles on it.

Anyone have any leads?

# January 15, 2009 12:27 PM

RM said:

Hi,

As i gothrough the thread, i found that i need to either create schema.ini file or change the registry key to set MAXSCANROWS AS 0. I'm writing the client application, where few users doesn't have ADMIN rights. But to change the registry OR to create schema.ini file i need to ADMIN access. How to resolve this prob? I've asked in various forums and i didnt get any response.

Will anyone pls help me on this.

Thanks

# July 1, 2009 1:21 AM

zahir said:

Hi all ,

any one able to run this code on IIS6 64bit machine.

I am having MOSS webapplication.Which runs on 64bit configured IIS.Oledb4.0 will supprot only 32bit Webapp.Can any one help me out for this?

# July 31, 2009 2:01 AM

Tintu said:

Hi,

Anyone got solution for Moran Ben-David as OJ's problem?I've also encountered same issue,even the file name is enclosed in brackets,I get the excepton:

"The Microsoft Jet database engine could not find the object 'test.1083952525531.txt'. Make sure the object exists and that you spell its name and the path name correctly."

# October 29, 2009 1:53 AM

Ajit said:

Keep extended properties in " to avoid can not find installable ISAM

       strConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Server.MapPath("") + "\\" + ";Extended Properties='text;HDR=Yes'";

# November 27, 2009 9:09 AM

Ashlesh said:

Hi,

I am facing the problem while reading the CSV file for ' OR ".

Sometimes the data contains “ or ' such as '1122', “222222” The program can't be import if  they are showing.

I think this is due to nature of excel

Can you please help me out asap.

Thank You.

/Ashlesh

# December 9, 2009 6:08 AM

Subhankar said:

Hellow Everyone,

I am getting trouble while accessing fields of the CSV file by names. My CSV file has field names which contails space like "Customer Name","Serial No" etc. How can i filter the records based on the perticular Customer name or serial no.

Kindly help me. It is very urgent.

---

Thanks ,

Subhankar

# January 14, 2010 10:07 AM

Fabrice Marguerie said:

Subhankar, did you try with square brackets? Maybe it works. e.g. [Customer Name]

# January 14, 2010 11:40 AM

Stefan said:

Well.. I have a similar problem. I just want the database in a human-readable format like CSV (so it can be committed to a VCS and eventually even merged). The approach I found is the following:

1. Create SQLite database in temporary file

2. Import data from CSV to SQLite (using sqlite3)

3. Connect to the SQLite DB

3. Do all your stuff

4. Close connection

5. Export to CSV (again using sqlite3)

6. Delete temporary database

so you need to read/write lots of data on application startup and shutdown.

# March 12, 2010 5:58 AM

Kalyan said:

I'm trying the below::

>>>

string ConnectionString = "Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=" + dataPath + ";";

string CmdText = "select * from [" + fileName_ + "]";

OdbcConnection Con = new OdbcConnection(ConnectionString);

OdbcDataAdapter adptr = new OdbcDataAdapter(CmdText, Con);

DataTable dt = new DataTable();

adptr.Fill(dt);

<<<

Problem is the mixed type issue.

The registry solution doesn' work here.

Do you guys think I should rather use JET driver rather than Ms Text Driver??

# August 16, 2010 11:09 AM

Craig Lebowitz said:

Another thing I noticed is that on 64-bit windows, the registry key was

HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Jet\4.0\Engines\Text

Notice the Wow6432Node

# October 6, 2010 3:39 PM

Debasis said:

Thanks for the article; it provided me immense help while getting the ISAM error in connecting to the CSV File. Actually I was stuck up with the Extended Property.

# November 18, 2011 3:45 AM

Trin said:

Regarding case sensitivity. If you want to do case INsensitive search use

LCASE(ColumnName) = 'something_in_low_case'

or

LCASE(ColumnName) = LCASE(OtherColumnNameOrExpression)

# February 20, 2012 10:54 AM