/// <summary> /// Retrieves object containing line delimiters and column names for a table of delimited text data. /// </summary> /// <param name="tab">DataTable containing the data.</param> /// <param name="columnSeparator">String containing one or more characters that will separate columns in the line of data that is extracted.</param> /// <param name="lineTerminator">String containing one or more characters that will denote the end of a line of data.</param> /// <param name="columnNamesOnFirstLine">If true, a line containing the column names in delimited format will be the first line returned.</param> /// <param name="tableNumber">Arbitrary number used to identify the table.</param> /// <returns>PFDelimitedDataLine object that contains definition of delimited text format for specified DataTable.</returns> public PFDelimitedDataLine GetDelimitedLineDefinitionFromTable(DataTable tab, string columnSeparator, string lineTerminator, bool columnNamesOnFirstLine, int tableNumber) { int colInx = 0; int maxColInx = -1; DataColumnCollection cols = tab.Columns; PFDelimitedDataLine delimitedLineDefinition = new PFDelimitedDataLine(cols.Count); maxColInx = tab.Columns.Count - 1; delimitedLineDefinition.ColumnSeparator = columnSeparator; delimitedLineDefinition.LineTerminator = lineTerminator; delimitedLineDefinition.ColumnNamesOnFirstLine = columnNamesOnFirstLine; for (colInx = 0; colInx <= maxColInx; colInx++) { delimitedLineDefinition.SetColumnDefinition(colInx, cols[colInx].ColumnName); } if (columnNamesOnFirstLine == true) { if (returnResultAsString != null) { returnResultAsString(delimitedLineDefinition.OutputColumnNames(), tableNumber); } } return(delimitedLineDefinition); }
}//end method public static void DelimitedExtractFileTest(MainForm frm) { PFSQLServer sqlserv = new PFSQLServer(); string connectionString = string.Empty; string sqlQuery = "select GeographyKey, City, StateProvinceName as StateName, EnglishCountryRegionName as CountryName, PostalCode from dbo.DimGeography;"; PFTextFile extractFile = new PFTextFile(@"c:\temp\ExtractFileTest.txt", PFFileOpenOperation.OpenFileForWrite); Stopwatch watch = new Stopwatch(); try { watch.Start(); sqlserv.ServerName = "PROFASTSV2"; sqlserv.DatabaseName = "AdventureWorksDW2008R2"; sqlserv.UseIntegratedSecurity = true; sqlserv.ApplicationName = "TextExtractTest"; sqlserv.WorkstationId = Environment.MachineName; connectionString = sqlserv.ConnectionString; _msg.Length = 0; _msg.Append("Connection string is: \r\n"); _msg.Append(connectionString); Program._messageLog.WriteLine(_msg.ToString()); sqlserv.OpenConnection(); sqlserv.SQLQuery = sqlQuery; sqlserv.CommandType = CommandType.Text; SqlDataReader rdr = (SqlDataReader)sqlserv.RunQueryDataReader(); DataTable schemaTable = rdr.GetSchemaTable(); PFDelimitedDataLine line = new PFDelimitedDataLine(schemaTable.Rows.Count); for (int i = 0; i < schemaTable.Rows.Count; i++) { line.SetColumnDefinition(i, schemaTable.Rows[i]["ColumnName"].ToString()); } extractFile.WriteData(line.OutputColumnNames()); //foreach (DataRow row in schemaTable.Rows) //{ // Program._messageLog.WriteLine(row["ColumnName"].ToString()); // //For each property of the field... // //foreach (DataColumn prop in schemaTable.Columns) // //{ // // //Display the field name and value. // // Program._messageLog.WriteLine(prop.ColumnName + " = " + fld[prop].ToString()); // //} //} int numRows = 0; if (rdr.HasRows) { int colInx = 0; int maxColInx = -1; while (rdr.Read()) { numRows++; maxColInx = rdr.FieldCount - 1; for (colInx = 0; colInx <= maxColInx; colInx++) { line.SetColumnData(colInx, rdr[colInx].ToString()); } extractFile.WriteData(line.OutputColumnData()); } } _msg.Length = 0; _msg.Append("Number of data rows written: "); _msg.Append(numRows.ToString("#,##0")); _msg.Append("\r\n"); _msg.Append("Number of header rows written: 1"); Program._messageLog.WriteLine(_msg.ToString()); //if (rdr.HasRows) //{ // int colInx = 0; // int maxColInx = -1; // while (rdr.Read()) // { // _msg.Length = 0; // maxColInx = rdr.FieldCount - 1; // for (colInx = 0; colInx <= maxColInx; colInx++) // { // _msg.Append(rdr.GetName(colInx)); // _msg.Append(": "); // _msg.Append(rdr[colInx].ToString()); // if (colInx < maxColInx) // _msg.Append(", "); // } // Program._messageLog.WriteLine(_msg.ToString()); // } //} } catch (System.Exception ex) { _msg.Length = 0; _msg.Append(AppGlobals.AppMessages.FormatErrorMessage(ex)); Program._messageLog.WriteLine(_msg.ToString()); AppMessages.DisplayErrorMessage(_msg.ToString(), _saveErrorMessagesToAppLog); } finally { sqlserv.CloseConnection(); sqlserv = null; if (extractFile != null) { extractFile.CloseFile(); extractFile = null; } watch.Stop(); _msg.Length = 0; _msg.Append("Elapsed time: "); _msg.Append(watch.FormattedElapsedTime); Program._messageLog.WriteLine(_msg.ToString()); } }
/// <summary> /// Extracts data from DataTable into delimited text format. /// </summary> /// <param name="tab">DataTable containing the data.</param> /// <param name="columnSeparator">String containing one or more characters that will separate columns in the line of data that is extracted.</param> /// <param name="lineTerminator">String containing one or more characters that will denote the end of a line of data.</param> /// <param name="columnNamesOnFirstLine">If true, a line containing the column names in delimited format will be the first line returned.</param> /// <param name="tableNumber">Arbitrary number used to identify the table.</param> /// <param name="stringValuesSurroundedWithQuotationMarks">If true, string values will be surrounded by double quotes.</param> public void ExtractDelimitedDataFromTable(DataTable tab, string columnSeparator, string lineTerminator, bool columnNamesOnFirstLine, int tableNumber, bool stringValuesSurroundedWithQuotationMarks) { int rowInx = 0; int maxRowInx = -1; int colInx = 0; int maxColInx = -1; DataColumnCollection cols = tab.Columns; PFDelimitedDataLine delimitedLine = new PFDelimitedDataLine(cols.Count); bool[] surroundWithQuotationMarks = new bool[tab.Columns.Count]; maxRowInx = tab.Rows.Count - 1; maxColInx = tab.Columns.Count - 1; delimitedLine.ColumnSeparator = columnSeparator; delimitedLine.LineTerminator = lineTerminator; delimitedLine.StringValuesSurroundedWithQuotationMarks = stringValuesSurroundedWithQuotationMarks; for (colInx = 0; colInx <= maxColInx; colInx++) { delimitedLine.SetColumnDefinition(colInx, cols[colInx].ColumnName); if (cols[colInx].DataType == typeof(System.String) && stringValuesSurroundedWithQuotationMarks) { surroundWithQuotationMarks[colInx] = true; } else { surroundWithQuotationMarks[colInx] = false; } } if (columnNamesOnFirstLine == true) { if (returnResultAsString != null) { returnResultAsString(delimitedLine.OutputColumnNames(), tableNumber); } } for (rowInx = 0; rowInx <= maxRowInx; rowInx++) { DataRow row = tab.Rows[rowInx]; if (returnResultAsString != null) { for (colInx = 0; colInx <= maxColInx; colInx++) { if (stringValuesSurroundedWithQuotationMarks) { delimitedLine.StringValuesSurroundedWithQuotationMarks = surroundWithQuotationMarks[colInx]; } delimitedLine.SetColumnData(colInx, row[colInx].ToString()); } returnResultAsString(delimitedLine.OutputColumnData(), tableNumber); } } }//end ExtractDelimitedtDataFromTable method
public static void DelimitedLineTextFileTests(int numRowsToOutput) { PFTextFile textFile = new PFTextFile(@"c:\temp\DelimitedText.txt", PFFileOpenOperation.OpenFileForWrite); PFDelimitedDataLine line = new PFDelimitedDataLine(5); RandomString dat = new RandomString(); RandomNumber siz = new RandomNumber(); int num = 0; string str = string.Empty; try { line.LineTerminator = "\r\n"; line.ColumnSeparator = ","; line.SetColumnDefinition(0, "FirstColumn", 10); line.SetColumnDefinition(1, "SecondColumn", 15); line.SetColumnDefinition(2, "ColumnThree", 5); line.SetColumnDefinition(3, "ColumnFour", 35); line.SetColumnDefinition(4, "FifthColumn", 25); textFile.WriteData(line.OutputColumnNames()); for (int rowNum = 0; rowNum < numRowsToOutput; rowNum++) { for (int inx = 0; inx < 5; inx++) { num = siz.GenerateRandomInt(1, line.ColumnDefinitions.ColumnDefinition[inx].ColumnLength); str = dat.GetStringAL(num); line.ColumnData.ColumnDataValue[inx].Data = str; } textFile.WriteData(line.OutputColumnData()); } textFile.CloseFile(); line.SaveToXmlFile(@"c:\temp\DelimitedText.xml"); //read textfile textFile.OpenFile(@"c:\temp\DelimitedText.txt", PFFileOpenOperation.OpenFileToRead); string input = textFile.ReadLine(); line.VerifyColumnNames(input); Program._messageLog.WriteLine(line.ColumnDefinitions.ToString()); input = textFile.ReadLine(); line.ParseData(input); Program._messageLog.WriteLine(line.ColumnData.ToString()); textFile.CloseFile(); //double check loading of xml schema works PFDelimitedDataLine line2 = PFDelimitedDataLine.LoadFromXmlFile(@"c:\temp\DelimitedText.xml"); Program._messageLog.WriteLine(line2.ToXmlString()); PFTextFile textFile2 = new PFTextFile(@"c:\temp\DelimitedText2.txt", PFFileOpenOperation.OpenFileForWrite); char[] tst = { 'a', 'b', 'c', 'd', 'e' }; //line2.LineTerminator = "\r\n"; line2.ColumnSeparator = ","; line2.SetColumnDefinition(0, "Column One", 10); line2.SetColumnDefinition(1, "2ndColumn", 15); line2.SetColumnDefinition(2, "Column Three", 5); line2.SetColumnDefinition(3, "Column Four", 35); line2.SetColumnDefinition(4, "5thColumn", 25); textFile2.WriteData(line2.OutputColumnNames()); for (int rowNum = 0; rowNum < numRowsToOutput; rowNum++) { for (int inx = 0; inx < 5; inx++) { num = siz.GenerateRandomInt(1, line2.ColumnDefinitions.ColumnDefinition[inx].ColumnLength); str = new string(tst[inx], num); line2.ColumnData.ColumnDataValue[inx].Data = str; } textFile2.WriteData(line2.OutputColumnData()); } textFile2.CloseFile(); } catch (System.Exception ex) { _msg.Length = 0; _msg.Append(AppGlobals.AppMessages.FormatErrorMessage(ex)); Program._messageLog.WriteLine(_msg.ToString()); AppMessages.DisplayErrorMessage(_msg.ToString(), _saveErrorMessagesToAppLog); } finally { if (textFile != null) { textFile = null; } ; } }//end DelimitedLineTextFileTests