using System; using System.Collections.Generic; using System.Text; using System.IO; using System.Data; using System.Data.OleDb;namespace ReadSeparatedValuesTest { class Program { static void Main(string[] args) { /* * 1. Issuse * Let's say I have 4 fields delimited by tab in each line in the tab-delimited.txt file. * I also have a schema.ini file in the same folder to indicate the format of the tab-delimited.txt file. * But it seems the schema.ini file does not override the setting in the system registry(HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Text\Format). * The text engines can not recognize tab as the delimiter. So it still reads the four fields together into one field. * * 2. Solution But Tedious * Config the default format from CSVDelimited to TabDelimited in HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Text\Format, you will get the expected result. */ string dataSourcePath = Environment.CurrentDirectory + "\\"; string dataFileName = "tab-delimited.txt"; string connString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + dataSourcePath + @";Extended Properties=""text;HDR=NO;FMT=TabDelimited"""; OleDbConnection conn = new OleDbConnection(connString); OleDbCommand cmd = conn.CreateCommand(); cmd.CommandText = String.Format("SELECT * FROM [{0}]", dataFileName); DataTable dt = new DataTable(Path.GetFileNameWithoutExtension(dataFileName)); OleDbDataAdapter da = new OleDbDataAdapter(cmd); da.Fill(dt); // output as xml. dt.WriteXml(dataFileName + ".xml"); // output into current console. Console.WriteLine("The imported data has {0} column(s):", dt.Columns.Count); foreach (DataColumn col in dt.Columns) { Console.Write(col.ColumnName); Console.Write("\t"); } Console.WriteLine(); foreach (DataRow row in dt.Rows) { foreach (DataColumn col in dt.Columns) { Console.Write(row[col]); Console.Write("\t"); } Console.WriteLine(); } Console.WriteLine(); // Console.Read(); } } }将文本文件读到DataTable里面,然后导出到excel
using System.Collections.Generic;
using System.Text;
using System.IO;
using System.Data;
using System.Data.OleDb;namespace ReadSeparatedValuesTest
{
class Program
{
static void Main(string[] args)
{
/*
* 1. Issuse
* Let's say I have 4 fields delimited by tab in each line in the tab-delimited.txt file.
* I also have a schema.ini file in the same folder to indicate the format of the tab-delimited.txt file.
* But it seems the schema.ini file does not override the setting in the system registry(HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Text\Format).
* The text engines can not recognize tab as the delimiter. So it still reads the four fields together into one field.
*
* 2. Solution But Tedious
* Config the default format from CSVDelimited to TabDelimited in HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Text\Format, you will get the expected result.
*/
string dataSourcePath = Environment.CurrentDirectory + "\\";
string dataFileName = "tab-delimited.txt";
string connString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + dataSourcePath + @";Extended Properties=""text;HDR=NO;FMT=TabDelimited""";
OleDbConnection conn = new OleDbConnection(connString);
OleDbCommand cmd = conn.CreateCommand();
cmd.CommandText = String.Format("SELECT * FROM [{0}]", dataFileName);
DataTable dt = new DataTable(Path.GetFileNameWithoutExtension(dataFileName));
OleDbDataAdapter da = new OleDbDataAdapter(cmd);
da.Fill(dt);
// output as xml.
dt.WriteXml(dataFileName + ".xml");
// output into current console.
Console.WriteLine("The imported data has {0} column(s):", dt.Columns.Count);
foreach (DataColumn col in dt.Columns)
{
Console.Write(col.ColumnName);
Console.Write("\t");
}
Console.WriteLine(); foreach (DataRow row in dt.Rows)
{
foreach (DataColumn col in dt.Columns)
{
Console.Write(row[col]);
Console.Write("\t");
}
Console.WriteLine();
}
Console.WriteLine();
//
Console.Read();
}
}
}将文本文件读到DataTable里面,然后导出到excel
Format=TabDelimited
然后剩下的工作就是换掉标题,和按照地区分多个文件了,这样的纯文本操作应该不难吧