Access to a new line becomes _x000D_

Question

I am trying to report on access obtained from the data that I have in excel. Thus, I import my Excel sheet into access using external data parameters, but my new lines continue to be displayed as "_x000D _"

As an example, I use an excel sheet with 4 columns, all with a header and all with 1 row of data, 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" 

In my excel sheet, I tried changing the new lines to everything I could think of, but the ones that seemed to do something were char (10) and char (13), however char (10) doesn't appears in access at all, and char (13) seems to become "_x000D _"

Answer 1

See How to import from Excel and save line breaks :

Excel uses the line character (ASCII 10) as the line separator, while Access uses the carriage + line return combination (ASCII 13 and then ASCII 10) as the line separator.

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

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

So the only right way to put a new row in an Excel cell is your first version:

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

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