csv header captions trimmed [urgent][?!]

  • Thread starter Thread starter Wiktor Zychla
  • Start date Start date
W

Wiktor Zychla

Just a while ago I've tried to solve an issue with reading CSV files and
stepped into an issue where the header text is stripped if longer than 64
chars!

this is unexpected. what's interesting is that Excel read this CSV file
correctly.

can I really write my own CSV reading routine to solve that issue or can
anything easier be done to fix that? this constraint is new to me, I've
never found any documents saying that header data cannot exceed the legth of
64 chars.

thanks in advance
Wiktor Zychla

details: two drivers tested (JET.4.0 via OleDbc and Microsoft Text Driver
via Odbc). both trim the header caption to the length of 64 chars.

code:

using System;
using System.Data;
using System.Data.OleDb;
using System.Data.Odbc;
using System.IO;

class T {
public static void Main()
{
using ( IDbConnection conn = new OdbcConnection() )
{
try
{
string fileName = "c:\\003\\test.csv";

//conn.ConnectionString =
String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\"{0}\";Extended
Properties=\"text;HDR=Yes;FMT=Delimited\"",
Path.GetDirectoryName(fileName) );
conn.ConnectionString = String.Format( "Driver={{Microsoft Text Driver
(*.txt; *.csv)}};DBQ={0};Extensions=asc,csv,tab,txt;HDR=YES;Persist Security
Info=False", Path.GetDirectoryName(fileName) );
conn.Open();

// inicjuj dataset
IDataAdapter da = new OdbcDataAdapter( string.Format( "SELECT * FROM
[{0}]", Path.GetFileName(fileName) ), (OdbcConnection)conn );
DataSet ds = new DataSet();
da.Fill( ds );
DataTable dt = ds.Tables[0];

Console.WriteLine( dt.Columns[0].Caption );
}
catch( Exception ex )
{
Console.WriteLine( ex.Message );
}
}
}
}

data [test.csv]:

Z123456789Z123456789Z123456789Z123456789Z123456789Z123456789Z123456789Z123456789
1
 
¤ Just a while ago I've tried to solve an issue with reading CSV files and
¤ stepped into an issue where the header text is stripped if longer than 64
¤ chars!
¤
¤ this is unexpected. what's interesting is that Excel read this CSV file
¤ correctly.
¤
¤ can I really write my own CSV reading routine to solve that issue or can
¤ anything easier be done to fix that? this constraint is new to me, I've
¤ never found any documents saying that header data cannot exceed the legth of
¤ 64 chars.
¤
¤ thanks in advance
¤ Wiktor Zychla
¤
¤ details: two drivers tested (JET.4.0 via OleDbc and Microsoft Text Driver
¤ via Odbc). both trim the header caption to the length of 64 chars.
¤
¤ code:
¤
¤ using System;
¤ using System.Data;
¤ using System.Data.OleDb;
¤ using System.Data.Odbc;
¤ using System.IO;
¤
¤ class T {
¤ public static void Main()
¤ {
¤ using ( IDbConnection conn = new OdbcConnection() )
¤ {
¤ try
¤ {
¤ string fileName = "c:\\003\\test.csv";
¤
¤ //conn.ConnectionString =
¤ String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\"{0}\";Extended
¤ Properties=\"text;HDR=Yes;FMT=Delimited\"",
¤ Path.GetDirectoryName(fileName) );
¤ conn.ConnectionString = String.Format( "Driver={{Microsoft Text Driver
¤ (*.txt; *.csv)}};DBQ={0};Extensions=asc,csv,tab,txt;HDR=YES;Persist Security
¤ Info=False", Path.GetDirectoryName(fileName) );
¤ conn.Open();
¤
¤ // inicjuj dataset
¤ IDataAdapter da = new OdbcDataAdapter( string.Format( "SELECT * FROM
¤ [{0}]", Path.GetFileName(fileName) ), (OdbcConnection)conn );
¤ DataSet ds = new DataSet();
¤ da.Fill( ds );
¤ DataTable dt = ds.Tables[0];
¤
¤ Console.WriteLine( dt.Columns[0].Caption );
¤ }
¤ catch( Exception ex )
¤ {
¤ Console.WriteLine( ex.Message );
¤ }
¤ }
¤ }
¤ }
¤
¤ data [test.csv]:
¤
¤ Z123456789Z123456789Z123456789Z123456789Z123456789Z123456789Z123456789Z123456789
¤ 1
¤

I haven't seen this before, but then again I've never had a column name of that length - can't
imagine why it would be necessary. I think Excel just imports it all as data so there is no need to
determine column names.

You can rename (alias) your columns by either using a schema.ini file or via the SQL statement. You
should set HDR=NO in your connection string and use the default column names.

SELECT F1 As NewColName, F2 As AnotherAliasedColumnName FROM [LongColNames#txt]"

Schema.ini doc:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/odbcjetschema_ini_file.asp


Paul ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)
 
Back
Top