访问换行符成为_x000D_ [英] Access newline becoming _x000D_

问题描述

我正在尝试根据我在excel中的数据得出的访问权限中进行报告.因此，我使用外部数据选项将Excel工作表导入到访问权限中，但换行符始终显示为"_x000D _"

I'm trying to make a report in access derived from data I have in excel. So I import my excel sheet in access using the external data options, but my newlines keep appearing as "_x000D_"

作为示例，我使用一个Excel工作表，该工作表有4列，全部带有标题，并且全部包含从左到右依次包含的1行数据:

As a example I use a excel sheet with 4 columns all with a title and all with 1 row of data containing in order from left to right:

="a"&char(10)&"b" 
="a"&char(13)&"b" 
="a"&char(10)&char(13)&"b" 
="a"&char(13)&char(10)&"b"

在我的excel工作表中，我尝试将换行符更改为我能想到的所有内容，但看起来已完成某些操作的两个分别是char(10)和char(13)，但char(10)并未出现在访问权限中全部，并且char(13)似乎变为"_x000D _"

In my excel sheet I have tried changing the newlines to everything I could think of but the two that appeared to have done something are char(10) and char(13) however char(10) doesn't appear in access at all and char(13) appears to become "_x000D_"

推荐答案

请参见

See How to import from Excel and keep the line breaks :

Excel使用换行符(ASCII 10)作为行分隔符，而Access使用回车键+换行符(ASCII 13后接ASCII 10)作为行分隔符.

Excel uses line-feed character (ASCII 10) as the line separator, while Access uses the combination of carriage-return + line-feed (ASCII 13 followed by ASCII 10) as the line separator.

导入后，可以使用替换"功能将Chr(10)替换为Chr(13)+ Chr(10).例如，您可以执行如下查询:

After importing, you can use the Replace function to replace Chr(10) with Chr(13)+Chr(10). For example, you could execute a query like this:

UPDATE ImportedExcelTable SET MyField = Replace([MyField], Chr(10), Chr(13) & Chr(10));

因此，将换行符放入Excel单元格的唯一正确方法是您的第一个版本:

So the only correct way to put a newline in an Excel cell is your first version:

="a"&char(10)&"b"

然后，在导入表后，使用Update查询将Lf替换为Access换行符CrLf.

Then after importing the table, use the Update query to replace the Lf with Access newlines CrLf.

这篇关于访问换行符成为_x000D_的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！