Share via

Simple one-way import of CSV - no "external data connection" needed

Anonymous
2023-12-13T07:14:24+00:00

I often import from CSV files by doing Data -> From Text/CSV. That apparently tries to keep some sort of live connection to the original CSV file - the imported file is listed in a "Queries & Connections" sidebar, and at the top it says, "Security warning: external data connections have been disabled." I don't want any such connection - can I permanently sever it, or keep it from being created in the first place during the import process? (In case you're wondering, I can't just open the CSV file directly with Excel and do Save As, because that method always garbles UTF-8 text. the "From Text/CSV" method usually recognizes the character encoding correctly, and when it doesn't, I can easily change it.)

And a second, related question: In that sidebar, sometimes it will say, "[number] rows loaded. 1 error." Does that error mean the imported data is not completely correct? If so, how can I find out what the error was? I've tried all kinds of ways of clicking on the Query to get it to tell me more about the error, but I can't figure it out.

(Environment: Excel 2021 on Windows 11)

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments
Answer accepted by question author
  1. Andreas Killer 144.1K Reputation points Volunteer Moderator
    2023-12-13T11:16:37+00:00

    I often import from CSV files by doing Data -> From Text/CSV.

    sometimes it will say, "[number] rows loaded. 1 error."

    a) That creates a query to load the data. If you click Data \ Refresh All you can reload the data without any further steps. So if your source CSV file changes you don't have to import again! That's the sense behind.

    If you don't like to have a query, simply right-click and delete it. The created table stays in the sheet.

    b) Click on that text and Excel creates another query that shows the rows which contains the errors. In that query you'll see some "cells" that shows Error, select it and you'll get a preview and message what's the reason.

    Andreas.

    1 person found this answer helpful.
    0 comments

1 additional answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2023-12-13T13:22:00+00:00

    If you don't like to have a query, simply right-click and delete it. The created table stays in the sheet.

    Thanks. I was afraid that might either remove the imported content or even delete the CSV file. But you're right - it's fine.

    b) Click on that text and Excel creates another query that shows the rows which contains the errors. In that query you'll see some "cells" that shows Error, select it and you'll get a preview and message what's the reason.

    Oh, the "1 error" text is clickable! That is not obvious unless you happen to hover directly over it. Clicking on the query item in general (or right-clicking and trying Properties, "Show the peek", etc.) doesn't help at all with the error. Thanks!

    0 comments