Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

i have around 100 text files. The files have questions and 3 choices. FIles are like below

ab001.txt -- contains question
ab001a.txt -- is the first choice
ab001b.txt ---is second choice
ab001c.txt --- is third choice

There are thousnad files like this.

now i want to insert them in sql or first may in excel like

First columns questions and other three columns as answers

First two characters are same for soom files , looks like it signifies osme category so around every 30 questioons have same first charaters

Any ideas

share|improve this question

2 Answers 2

I had a similar need, concatenating a large number of similarly-named files. I whipped up a little command-line tool in C#:

class Program
    {
    static int  Main(string[] args)
        {
        if (args.Length < 3)
            {
            Console.WriteLine("Expected three to four arguments: the input folder; the input pattern; the output file name; and (optionally) the number of header rows to skip in subsequent files.");
            return -1;
            }

        string  inputFolder  = args[0];
        string  inputPattern = args[1];
        string  outputFile   = args[2];
        int     headerRows   = args.Length == 4 ? Int32.Parse(args[3]) : 0;

        // Clean up inputs
        if (inputFolder.StartsWith("\"") && inputFolder.EndsWith("\""))  inputFolder = inputFolder.Substring(1, inputFolder.Length - 2);
        if (inputFolder.EndsWith("\\") == false)                         inputFolder = inputFolder + '\\';
        if (Path.IsPathRooted(outputFile) == false)                      outputFile  = inputFolder + outputFile;

        Console.WriteLine("Reading \"{0}\" from \"{1}\" to \"{2}\".", inputPattern, inputFolder, outputFile);

        // Merge the files
        int  records = 0;
        using (StreamWriter collectedFile = CreateCollectedFile(outputFile))
            {
            foreach (string filePath in Directory.GetFiles(inputFolder, inputPattern))
                {
                if (filePath == outputFile)  continue;

                Console.Write("Reading \"{0}\"...", filePath);

                // Note that we do not want to skip headers in the first file, as we want the final file to include *1* copy of the header
                int  recordsThisFile = AppendFile(filePath, collectedFile, records == 0 ? 0 : headerRows);

                Console.WriteLine("copied {0} records.", recordsThisFile.ToString("#,##0"));

                records += recordsThisFile;
                }
            }

        Console.WriteLine("Read {0} records.", records.ToString("#,##0"));
        return 0;
        }

    private static StreamWriter  CreateCollectedFile(string path)
        {
        if (File.Exists(path))  File.Delete(path);

        return new StreamWriter(path);
        }

    private static int  AppendFile(string from, StreamWriter to, int headerRows)
        {
        int  records = 0;
        using (StreamReader reader = new StreamReader(File.OpenRead(from)))
            {
            string  line;
            while (reader.EndOfStream == false)
                {
                line = reader.ReadLine();
                if (++records > headerRows)  to.WriteLine(line);
                }
            }
        return records;
        }
    }

Usage:

MergeFiles . ab???.csv Questions.csv 0
MergeFiles . ab????.csv Choices.csv 0

Note the first parameter, ., indicating the current directory.

If you have a C# compiler handy, this'll do it. I'd post the binary, but if you're properly paranoid you wouldn't want to run such a thing anyway.

As Faucett points out, *NIX already has good utilities for this. If you're on Windows, this may help.

share|improve this answer

An easy way to put these into Excel is to combine all the questions into one file, open it from Excel and put the result into column 1. Then do the same with all the first choices and put them into column 2, second choices into column 3, and then the third choices into column 4.

From a Unix or Linux command line interface you could do the following:

cat [a-z][a-z][0-9][0-9][0-9].txt > questions
cat [a-z][a-z][0-9][0-9][0-9]a.txt > choicea
cat [a-z][a-z][0-9][0-9][0-9]b.txt > choiceb
cat [a-z][a-z][0-9][0-9][0-9]c.txt > choicec
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.