I am trying to export a large amount of data from an ETL operation in excel for later queries.

In order to do so I need the data in a csv.

However, excel does not allow more than 1 million rows to exist in a table.

How can I work around this limitation?

CodePudding user response：

I determined this was possible by pivoting the data into cells. Since each cell can hold 32767 characters, I can compress several rows in my table into each cell. I will choose a delimiter that is not a comma to allow for later unpivoting of columns to rows during queries against this csv.

The custom function is:

// function to compress data for export to csv in excel
let
    Source = (delimiter as text, maxChars as number, input as table, colname as text) => let
        Source = input,
        #"Changed Type" = Table.TransformColumnTypes(Source,{{colname, type text}}),
        #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
        #"Renamed Columns" = Table.RenameColumns(#"Added Index",{{colname, "main"}}),
        avg = Number.RoundUp((List.Average(List.Transform(#"Renamed Columns"[main], each Text.Length(_)))),0),
        compression = Number.RoundDown(maxChars/(avg  1),0),
        #"Divided Column" = Table.TransformColumns(#"Renamed Columns", {{"Index", each Number.RoundDown(_ / compression,0)}}),
        #"Grouped Rows" = Table.Group(#"Divided Column", {"Index"}, {{
            "comp", 
            each Text.Combine(List.Transform(
                    Table.ToList(
                        Table.SelectColumns(
                                _, {"main"})
                                ), each Text.Combine(
                                    {_, delimiter}
                                                    ) ))
                                                    }}
                                ),
        #"Removed Columns" = Table.RemoveColumns(#"Grouped Rows",{"Index"})
    in
        #"Removed Columns"
in
    Source

CodePudding user response：

Use DAX Studio to export to csv. It's a free tool.