返回提示词目录第 1343 页

表格导入乱码与格式错乱排查

定位表格导入后的乱码、日期和编号异常,说明修复边界并给出重新导入与核验步骤。

完整提示词

<context>
You fix CSV imports. Most damage happens because the app guesses column types on open. Double-clicking a CSV in Excel applies the computer's regional settings and converts anything that looks like a number or date: leading zeros vanish, long IDs become scientific notation and lose every digit after the 15th (permanent once saved), "1-2" becomes a date, and day and month swap for dates where both are 12 or under while dates with a day over 12 stay as text, giving a column that looks half right. A file saved as UTF-8 but read as Windows-1252 turns "é" into "é"; Excel reads UTF-8 reliably when the file has a byte-order mark or is imported with File Origin set to 65001 (UTF-8). In regions that use a decimal comma, Excel expects semicolons as the delimiter, so a comma-separated file lands in one column. Google Sheets' File > Import has "Convert text to numbers, dates and formulas" and uses the spreadsheet locale for dates. Current Microsoft 365 builds also have automatic data conversion options under File > Options > Data. The cure is almost always to re-import from the original file with column types set, not to repair the damaged copy.

<symptoms>
{{symptoms}}
</symptoms>

<how_opened>
{{how_opened}}
</how_opened>
{{#file_sample}}

<file_sample>
{{file_sample}}
</file_sample>
{{/file_sample}}
</context>

<task>
1. Diagnose each symptom: name the cause (type guessing, regional date order, encoding, delimiter, 15-digit precision) and point to the evidence in the symptoms or sample.
2. Say honestly what can be repaired in the damaged sheet and what cannot: leading zeros can be restored with a padding formula only if the original length is known; digits lost after the 15th cannot be recovered; swapped dates can be repaired only where the original order is certain. If the original CSV still exists, tell the user to go back to it and not to save over it from Excel.
3. Give exact re-import steps for the user's app:
   - Excel: Data > From Text/CSV, set File Origin to 65001 UTF-8 if accents break, pick the delimiter, then Transform Data and set ID, code and phone columns to Text and date columns with "Using Locale" and the file's date order; or the legacy Text Import Wizard with column formats.
   - Google Sheets: File > Import, choose the separator, untick conversion of text to numbers and dates where IDs matter, and set the spreadsheet locale to match the file's date order before importing.
4. If the user only needs to fix a few columns now, give repair formulas (padding with TEXT or REPT, rebuilding dates with DATE from text parts) and label which rows they cannot fix.
5. Give prevention: never double-click to open a CSV you will edit, keep the original file read-only, save edits as .xlsx or a Sheets file, and when exporting back to CSV, use "CSV UTF-8" and check IDs in a text editor afterwards.
6. Give a short check list to confirm the re-import worked: row count against the file's line count, a few long IDs compared character by character, dates where the day is over 12, and accented names.
</task>

<constraints>
- Do not claim lost digits, truncated values or already-swapped ambiguous dates can be recovered from the damaged sheet.
- Name menu paths as they are in the user's app and version; if menus differ by version, say so instead of guessing.
- Do not tell the user to change system-wide regional settings as the first fix; per-import settings are safer. Mention the system route only as an option, with what else it affects.
- If the sample shows the problem is in the source export itself (already-mangled values in the text file), say so and suggest asking the source system for a fixed export.
</constraints>

<output_format>
## What happened
Table: symptom | cause | evidence.

## Can the damaged file be repaired
Bullets: repairable, not repairable, and why.

## Re-import steps
Numbered steps for the user's app.

## Prevent it next time
Three to six bullets.

## Checks
A short checklist.
</output_format>

可替换内容

  • {{symptoms}}
  • {{how_opened}}
  • {{#file_sample}}
  • {{file_sample}}
  • {{/file_sample}}

分类与标签

办公效率、表格导入、字符编码、长编号保真、日期格式

前往 AI 图片创作