Forum Discussion

djhaystack's avatar
djhaystack
Copper Contributor
Feb 27, 2025

Query Won't Load All Rows

I've seen many topics and tried many solutions and still can't find the answer. Here's a plain text CSV. When trying to query it cuts off after around 1100 rows. I've tried this with multiple files and always get a similar issue. I end up with the remainder being shoved into a new column. I've tried to find odd characters in the text file but have had no luck. Screenshots of issues here, and a link to the TXT file:

https://1drv.ms/t/s!Av3FoKc0kn-ppOcnyzX-OWnb6VMtYg?e=x3FT3T

The import is fixed width at these columns:

1, 7, 12, 83, 124, 126, 127, 133, 135, 137, 138, 144, 146, 148, 149, 155, 163, 165, 166, 172, 180, 182, 183, 189, 197, 207, 218, 228, 238

And a link to my Excel file in case it helps:

https://1drv.ms/x/s!Av3FoKc0kn-pqNld4re4xKfBn_qocQ?e=rD3dYw

excel

2 Replies

  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Platinum Contributor
    Feb 28, 2025

    Downloaded your TXT file and applied the following M-code. It created a table with 4617 rows.

    let

Source = Table.FromColumns({Lines.FromBinary(File.Contents("........................../KBVA Songs.TXT"), null, null, 65001)}),

#"Split column by positions" = Table.SplitColumn(Source, "Column1", Splitter.SplitTextByPositions({1, 7, 12, 83, 124, 126, 127, 133, 135, 137, 138, 144, 146, 148, 149, 155, 163, 165, 166, 172, 180, 182, 183, 189, 197, 207, 218, 228, 238}))

in

#"Split column by positions"

    Connect with PQ to TXT/CSV and then set the separator to 'nothing'. Then you get all texts in one column. Now split the column based on positions. Done.

     

  • Kidd_Ip's avatar
    Kidd_Ip
    MVP
    Feb 28, 2025

    Please try below:

     

    1. Check for Hidden Characters

    • Even if you couldn't find odd characters, there might still be invisible ones (like non-printable ASCII characters) causing the issue.
    • Use a text editor like Notepad++ or a tool like grep to search for and remove any hidden characters.

    2. Verify Fixed-Width Columns

    • Double-check the column widths you specified. If the data doesn't align perfectly with these widths, it could cause rows to spill into new columns.
    • Open the file in a text editor and visually inspect the alignment.

    3. Increase Row Limit

    • Excel has a row limit of 1,048,576 rows per worksheet. If your data exceeds this limit, it will be truncated. Ensure your data fits within this limit.

    4. Use Power Query

    • Instead of importing the file directly, use Power Query (Get & Transform in Excel). Power Query is more robust for handling large datasets and can help identify issues during the import process.

    5. Test with Smaller Data

    • Create a smaller sample of your CSV file (e.g., the first 500 rows) and test the import. This can help isolate whether the issue is with the file size or specific rows.

    6. Check Encoding

    • Ensure the CSV file is saved with the correct encoding (e.g., UTF-8). Incorrect encoding can cause data misalignment.

Resources