Either is fine. Pick the one that seems more readable to you. If the calculation naturally decomposes, as this one does, then the multiple maps is probably more readable. Some calculations won't naturally decompose, in which case you're stuck at the former. In neither case should you be worrying that one is significantly more performant than the other; that's largely a non-consideration.

It is because of the call to distinct().

When you call flatmap, it combines all lines in all of your files to a single Stream<String>, so distinct() will return lines which are distinct among all files.

When you use a for loop, you only are calling distinct() on the lines in each file individually. So, when you add them to your list, there could still be duplicates if the same line exists in different files.

Simply put, you only need a Consumer<String>-implementing class that knows what lines to read as the table name, the columns and rows.

public class StatementGenerator implements Consumer<String> {

    private static String START = " (";
    private static String END = ") ";
    private static String ROW_DELIMITER = "\n";

    private String delimiter = "\t";
    private String parameter = "%";
    private ObjIntConsumer<List<String>> parameterSupplier =
            (list, i) -> { list.add(parameter); };
    private String table = null;
    private List<String> columns = new ArrayList<>();
    private List<String> rows = new ArrayList<>();

    public void setDelimiter(String delimiter) {
        this.delimiter = delimiter;
    }

    public void setParameter(String parameter) {
        this.parameter = parameter;
    }

    public void setParameterSupplier(ObjIntConsumer<List<String>> parameterSupplier) {
        this.parameterSupplier = parameterSupplier;
    }

    @Override
    public void accept(String t) {
        if (table == null) {
            table = t;
            return;
        }
        if (!t.contains(delimiter)) {
            columns.add(t);
            return;
        }
        rows.add(t);
    }

    public String getTableName() {
        return table;
    }

    public List<String> getColumns() {
        return columns;
    }

    public List<String> getRows() {
        return rows;
    }

    public String getParameterizedStatement() {
        StringBuilder result = new StringBuilder("INSERT INTO ");
        result.append(constructSegment(getTableName(), getColumns())).append(
                constructSegment("VALUES", IntStream.rangeClosed(1, getColumns().size())
                        .collect(ArrayList::new, parameterSupplier, List::addAll)));
        return result.append(";").toString();
    }

    public List<String> getRawStatements() {
        String placeholderStatement = getParameterizedStatement()
                .replaceAll(Pattern.quote(parameter), "%s");
        return getRows().stream().map(r -> String.format(placeholderStatement,
                    Pattern.compile(delimiter).splitAsStream(r)
                        .map(v -> "'" + v + "'").toArray()))
                .collect(Collectors.toList());
    }

    public String getFullStatement() {
        return getRawStatements().stream().collect(Collectors.joining(ROW_DELIMITER));
    }

    @Override
    public String toString() {
        return getParameterizedStatement();
    }

    private static String constructSegment(String prefix, List<String> list) {
        return prefix + list.stream().collect(Collectors.joining(", ", START, END));
    }
}

In my sample implementation above, I've made the delimiter configurable, but there's one other configuration I want to highlight - parameter.

SQL injection

The sample implementation provides a getParameterizedStatement() as the starting point, because by right you should let your database driver handle the escaping of quotes (and possibly other magic values). We certainly do not need Little Bobby Tables to hang around here.

The parameter list is driven by an ObjIntConsumer, because if I'm not mistaken, there are certain drivers allowing for an index-based parameter substitution. That will mean you can potentially override parameterSupplier with something like (list, i) -> { list.add("::" + i); }; (or whatever the placeholder format is).

With that said, assuming you can absolutely trust your file-based input (i.e. sanitized inputs), we can then proceed with using getRawStatements(), which performs a simple substitution on the parameter with our String.format()'s "%s" placeholder. This is more aligned with what you are asking for. getFullStatement() simply concatenates all the rows into a single String, if that is what you actually require.

Here's what I used in my main() code:

public class StatementGeneratorMain {

    public static void main(String[] args) throws IOException, URISyntaxException {
        StatementGenerator generator = new StatementGenerator();
        generator.setDelimiter(",");
        try (Stream<String> lines = Files.lines(Paths.get(
                ClassLoader.getSystemResource("sqlRecords.txt").toURI()))) {
            lines.forEach(generator);
        }
        System.out.println(generator.getTableName());
        System.out.println(generator.getColumns());
        System.out.println(generator.getParameterizedStatement());
        generator.getRawStatements().forEach(System.out::println);
    }
}

And the sample output:

STUDENTS
[ID, NAME]
INSERT INTO STUDENTS (ID, NAME) VALUES (%, %) ;
INSERT INTO STUDENTS (ID, NAME) VALUES ('1', 'Mike') ;
INSERT INTO STUDENTS (ID, NAME) VALUES ('2', 'Kimberly') ;

There are two completely different things you should ask here:

a) how do I place multiple lines of code in stream.forEach()?

b) what should I do to count the number of lines in a Stream?

The question b) is answered already by other posters; on the other hand, the general question a) has a quite different answer:

use a (possibly multi-line) lambda expression or pass a reference to multi-line method.

In this particular case, you'd either declare i a field or use a counter/wrapper object instead of i.

For example, if you want to have multiple lines in forEach() explicitly, you can use

class Counter { // wrapper class
    private int count;
    public int getCount() { return count; }
    public void increaseCount() { count++; }
}

and then

Counter counter = new Counter();
lines.stream().forEach( e -> {
    System.out.println(e);
    counter.increaseCounter(); // or i++; if you decided i is worth being a field
} );

Another way to do it, this time hiding those multiple lines in a method:

class Counter { // wrapper class
    private int count;
    public int getCount() { return count; }
    public void increaseCount( Object o ) {
        System.out.println(o);
        count++;
    }
}

and then

Counter counter = new Counter();
lines.stream().forEach( counter::increaseCount );

or even

Counter counter = new Counter();
lines.stream().forEach( e -> counter.increaseCount(e) );

The second syntax comes in handy if you need a consumer having more than one parameter; the first syntax is still the shortest and simplest though.

You can make your own Collector which temporarily stores the previous element/string. When the current element starts with a $, the name of the product is stored in prev. Now you can convert the price to a double and create the object.

private class ProductCollector {

    private final List<Product> list = new ArrayList<>();

    private String prev;

    public void accept(String str) {
        if (prev != null && str.startsWith("$")) {
            double price = Double.parseDouble(str.substring(1));
            list.add(new Product(prev, price));
        }
        prev = str;
    }

    public List<Product> finish() {
        return list;
    }

    public static Collector<String, ?, List<Product>> collector() {
        return Collector.of(ProductCollector::new, ProductCollector::accept, (a, b) -> a, ProductCollector::finish);
    }
}

Since you need to rely on the sequence (line with price follows line with name), the stream cannot be processed in parallel. Here is how you can use your custom collector:

String[] lines = new String[]{
        "Ice Cream", "$3.99",
        "Chocolate", "$5.00",
        "Nice Shoes", "$84.95"
};

List<Product> products = Stream.of(lines)
        .sequential()
        .collect(ProductCollector.collector());

Note that your prices are not integers which is why I used a double to represent them properly.

Generally collecting to anything other than standard API's gives you is pretty easy via a custom Collector. In your case collecting to 3 lists at a time (just a small example that compiles, since you can't share your code either):

private static <T> Collector<T, ?, List<List<T>>> to3Lists() {
    class Acc {

        List<T> left = new ArrayList<>();

        List<T> middle = new ArrayList<>();

        List<T> right = new ArrayList<>();

        List<List<T>> list = Arrays.asList(left, middle, right);

        void add(T elem) {
            // obviously do whatever you want here
            left.add(elem);
            middle.add(elem);
            right.add(elem);
        }

        Acc merge(Acc other) {

            left.addAll(other.left);
            middle.addAll(other.middle);
            right.addAll(other.right);

            return this;
        }

        public List<List<T>> finisher() {
            return list;
        }

    }
    return Collector.of(Acc::new, Acc::add, Acc::merge, Acc::finisher);
}

And using it via:

Stream.of(1, 2, 3)
      .collect(to3Lists());

Obviously this custom collector does not do anything useful, but just an example of how you could work with it.

Use Collectors.toMap() with identity() for the key and your calculation for the value:

public Map<String, String> extractPartitionsValues(Path marketFile) {
    return partitions.stream()
      .collect(toMap(identity(), p -> partitionValueFromFilePath(p, marketFile.toString())));
}

Unfortunately, Java 8 streams do not support such extraction of elements in-between two matches. In Java 9, you could use

Map<String,String> map;
try(Stream<String> stream = Files.lines(path)) {
    map = stream
        .dropWhile(s -> !s.equals("#DATA")).skip(1)
        .takeWhile(s -> !s.equals("#DEND"))
        .filter(Pattern.compile("^[^#].*:").asPredicate())
        .map(item -> item.split(":", 2))
        .collect(Collectors.toMap(parts->parts[0], parts->parts[1]));
}
// use the map
map.forEach((k,v)->System.out.println(k+" -> "+v));

dropWhile will drop all elements before the first matching element, skip(1) will skip the matching element, takeWhile effectively removes all elements after the first element matching the end criteria.

The next filter step using the pattern ^[^#].*: will skip all lines starting with # or not containing a :. The remaining steps are straight-forward. When specifying a limit of 2 to split, it will not search for subsequent :s after encountering the first :.

Under Java 8, extracting the part between the two matches can be implemented with a Scanner before the stream operation:

String part;
try(Scanner s = new Scanner(path)) {
    part = s.findWithinHorizon("(?<=\\R#DATA\\R)(.|\\R)*(?=\\R#DEND\\R)", 0);
}
Map<String,String> map = Pattern.compile("\\R").splitAsStream(part)
    .filter(Pattern.compile("^[^#].*:").asPredicate())
    .map(item -> item.split(":", 2))
    .collect(Collectors.toMap(parts->parts[0], parts->parts[1]));
// use the map
map.forEach((k,v)->System.out.println(k+" -> "+v));