private void ExportToTextFile(string colDelimiter, string eol) { PFDataDelimitersPrompt delimitersPrompt = new PFDataDelimitersPrompt(); string fileName = string.Empty; string columnDelimiter = string.Empty; string lineTerminator = string.Empty; PFTextFile outputFile = null; PFDelimitedDataLine dataLine = null; try { if (columnDelimiter == string.Empty && eol == string.Empty) { if (delimitersPrompt.ShowDialog() != DialogResult.OK) { delimitersPrompt.Close(); return; } else { columnDelimiter = delimitersPrompt.ColumnDelimiter; lineTerminator = delimitersPrompt.LineTerminator; delimitersPrompt.Close(); } } else { columnDelimiter = colDelimiter; lineTerminator = eol; } if (ShowSaveFileDialog() != DialogResult.OK) { return; } fileName = _saveFileDialog.FileName; outputFile = new PFTextFile(fileName, PFFileOpenOperation.OpenFileForWrite); dataLine = new PFDelimitedDataLine(2); dataLine.ColumnSeparator = columnDelimiter; dataLine.LineTerminator = lineTerminator; for (int i = 0; i < this.dataGridAppSettings.Rows.Count; i++) { DataGridViewRow row = this.dataGridAppSettings.Rows[i]; for (int j = 0; j < row.Cells.Count; j++) { //excelApp.Cells[i + 1, j + 1] = row.Cells[j].Value.ToString(); dataLine.SetColumnData(j, row.Cells[j].Value.ToString()); } outputFile.WriteData(dataLine.OutputColumnData()); } outputFile.CloseFile(); } catch (System.Exception ex) { _msg.Length = 0; _msg.Append(AppGlobals.AppMessages.FormatErrorMessage(ex)); AppMessages.DisplayErrorMessage(_msg.ToString(), _saveErrorMessagesToErrorLog); } finally { ; } }
}//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