Re: Import CSV file to .net Datatable SOLUTION CODE

Here is what I came up with. It's a little strang to have to
dynamically generate a schema.ini file but it takes care of the
removal issue with leading zeros because Jet thinks it is smart and
removes them.

/// <summary>
/// Takes a CSV file and parses the data into a DataTable
using Jet OLEDB 4.0
/// It also generates a schema.ini, so that all fields get
pulled in as char
/// </summary>
/// <param name=";inFile&quot;>filepath to CSV</param>
/// <param name=";header&quot;>Enter Yes or No if it has a header
row</param>
/// <returns>DataTable with loan numbers and other
data<;/returns&gt;
public DataTable getCSVtable(string inFile, string header)
{
FileInfo diFile = new FileInfo(inFile);
string strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=" + diFile.DirectoryName
+ ";Extended
Properties="text;FMT=Delimited&quot;;"; //HDR=&quot; + header + ";
OleDbConnection csvFile = new OleDbConnection(strCon);

//First file a dataset with one row so that the number of
columns can be pulled
OleDbDataAdapter oCmd = new OleDbDataAdapter(&quot;SELECT top
1 * FROM [" + diFile.Name + "]&quot;, csvFile);
DataSet myDataset = new DataSet();
csvFile.Open();
oCmd.Fill(myDataset);

//Then create a schema.ini file in the same directory so
that all fields become char fields
string strSchema = diFile.DirectoryName + "\schema.ini&quot;;
string strColHeader = "ColNameHeader=" + header;
FileStream fsOutput = new FileStream(strSchema,
FileMode.Append);
StreamWriter srOutput = new StreamWriter(fsOutput);
srOutput.WriteLine(&quot;["; + diFile.Name+ "]&quot;);
srOutput.WriteLine(strColHeader);
for (int colNum = 1; colNum <= myDataset.Tables
[0].Columns.Count; colNum+;+)
srOutput.WriteLine(&quot;col&quot; + colNum.ToString()
+ "=fieldname"; + colNum.ToString() + " char");
srOutput.Close();
fsOutput.Close();

//After schema.ini is generated pull all the data out of
the flat file and return the datatable
oCmd = new OleDbDataAdapter(&quot;SELECT * FROM [" +
diFile.Name + "]&quot;, csvFile);
DataSet myDataset2 = new DataSet();
oCmd.Fill(myDataset2);
csvFile.Close();
try
{
File.Delete(strSchema);
}
catch (Exception e) { }

return myDataset2.Tables[0];
}

--- In AspNetAnyQuestionIsOk%40yahoogroups.com">AspNetAnyQuestionIsOkyahoogroups.com, "Dean Fiala";
<dfiala...> wrote:
&gt;
> If you want to set the types, you need to create the schema.ini.
>
> On 7/25/07, les_claypoo1 <thomasgkrier...> wrote:
&gt; >
>; > Okay I have been back and forth across google looking at samples
for
&gt; > this and below I list the code I use (Jet 4.0 engine). My
question is
> > there any way for Jet to import every column as straight
text/string?
>; > Problem being it reads in a csv file with the the account number
&gt; > without quotes around it (ex. 0123456789). Then when it gets to
the
&gt; > datatable object it will come out as 123456789. It drops the zero
>; > because Jet thinks it is smart. Any ideas to prevent this. (no
> > schema.ini, to hard to dynamically build one).
&gt; >
>; > FileInfo diFile = new FileInfo(inFile);
> > string strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data
> > Source=&quot; + diFile.DirectoryName
> > + ";Extended Properties="text;HDR=&quot; + header
&gt; > + ";FMT=Delimited";&quot;;
> > OleDbConnection csvFile = new OleDbConnection(strCon);
> > csvFile.Open();
&gt; > OleDbDataAdapter oCmd = new OleDbDataAdapter(&quot;SELECT
* FROM
>; > [" + diFile.Name + "]&quot;, csvFile);
> > DataSet myDataset = new DataSet();
> > oCmd.Fill(myDataset);
> > csvFile.Close();
&gt; > DataTable myTable = myDataset.Tables[0];
> >
>; > return myTable;
> >
>; >
>; >
>; >
>; > Yahoo! Groups Links
&gt; >
>; >
>; >
>; >
>;
>
> --
> Dean Fiala
&gt; Very Practical Software, Inc
> Now with Blogging...
> http://www.vpsw.com/blogbaby
> Microsoft MVP
>
>
> [Non-text portions of this message have been removed]
>

Messages in this topic (0) Reply (via web post) | Start a new topic

.