Importing CSV file as a table in Latex but file too long

You want to use longtable:

\documentclass{article}

\usepackage{csvsimple,longtable,booktabs}
\begin{document}

\csvreader[
  longtable=lrrrr,
  table head=\toprule\bfseries Item &\bfseries Alpha category (\%) \\\midrule,
  late after line=\\,
  late after last line=\\\bottomrule,
  before reading={\catcode`\#=12},after reading={\catcode`\#=6}
]{mode1_with.csv}{1=\Item,2=\Percentage}{\Item & \Percentage} 

\end{document}

If you want the header and the bottom rule to be repeated in each page, use

\documentclass{article}

\usepackage{csvsimple,longtable,booktabs}
\begin{document}

\csvreader[
  longtable=lrrrr,
  table head=
    \toprule\bfseries Item &\bfseries Alpha category (\%) \\ \midrule\endhead
    \bottomrule\endfoot,
  late after line=\\,
  before reading={\catcode`\#=12},after reading={\catcode`\#=6}
]{mode1_with.csv}{1=\Item,2=\Percentage}{\Item & \Percentage}

\end{document}

You need to use a longtable rather than a tabular environment. If you use longtable's native syntax, it's straightforward to retain full control over objects such as header and footer material on every page.

Naturally, as @egreg's parallel answer shows, it's possible to do so using csvreader's syntax as well.

\documentclass{article}
\usepackage{booktabs,csvsimple,longtable}
\begin{document}
\begin{longtable}{lr} 
\toprule
\bfseries Item & \bfseries Alpha category (\%) \\
\midrule \endhead
\bottomrule \endfoot
\csvreader[
    late after line=\\,
    late after last line=,
    before reading={\catcode`\#=12},
    after reading={\catcode`\#=6}]%
    {mode1_with.csv}{1=\Item,2=\Percentage}{\Item & \Percentage}
\end{longtable}
\end{document}