Random programming things I'd want to remember

Wednesday, December 28, 2011

Extract data from Excel into binary text file

In an Excel document (.xlsx, for .xls use
as the connection string), name one worksheet "Questions" and another "Answers" (no quotes in both cases). All columns are in Excel General format; the allowable format is noted in parentheses: Questions worksheet: QuestionID (Number), QuestionText (Text) Answers worksheet: AnswerID (Number), AnswerText (Text), QuestionID (Number), Correct (Text, but the only acceptable values are TRUE and FALSE).
var fileName = string.Format("{0}\\dbase.xlsx", Directory.GetCurrentDirectory());
var connStr = string.Format(
  "Provider=Microsoft.Jet.OLEDB.4.0; data source={0}; Extended Properties=Excel 8.0;", fileName);
var connectionString = string.Format(
  "Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=Excel 12.0;", fileName);
var adapter1 = new OleDbDataAdapter("SELECT * FROM [Questions$]", connectionString);
var adapter2 = new OleDbDataAdapter("SELECT * FROM [Answers$]", connectionString);

var ds = new DataSet();

adapter1.Fill(ds, "Questions");
adapter2.Fill(ds, "Answers");

var answersTable = ds.Tables["Answers"].AsEnumerable();
var questionsTable = ds.Tables["Questions"].AsEnumerable();

List answers = answersTable.Where(
  x => x.Field("AnswerID") > 0.0).Select(x =>
     new TestQuestionAnswer
         AnswerID = Convert.ToInt32(x.Field("AnswerID")),
         AnswerText = x.Field("AnswerText"),
         AnswerCorrect = x.Field("Correct"),
         QuestionID = Convert.ToInt32(x.Field("QuestionID"))

List questions = questionsTable.Where(
  x => x.Field("QuestionID") > 0.0).Select(x =>
    new TestQuestion
        QuestionID = Convert.ToInt32(x.Field("QuestionID")),
        QuestionText = x.Field("QuestionText")

foreach (TestQuestion tq in questions)
    List a = answers.Where(x => x.QuestionID == tq.QuestionID).ToList();
    tq.Answers = a;

using (FileStream fs = new FileStream("data.dat", FileMode.Create))
    using (var writer = new BinaryWriter(fs))
//fix for Syntax Highlighter plugin glitch:

No comments: