I've linked excel file to Access database. The excel file is automatically created by company's software and I can not change it. One column in Excel file is Delimited (Alt + 010).
When I link it to Access I get (for example, field "ProductName") long, almost unreadable line of characters.
Is it possible to have the name in Access table displayed as in Excel???
I know tables are for storing data but it is also nice if they are readable as you see them
:banghead:
It is a .xlsx file.
It is not imported to Excel but is LINKED to Access.
As my English is not so good I'd need some help from your anwer's last line: if .xls, transferSpreadsheet, then parse the delimited column using a query.
Some kind of example would be much appreciated
transfer spreadsheet ????
parse the delimited column ????
First things first. If I remember correctly, ALT+010 is an embedded ASCII control character; in English, LINE FEED (or LF, the short ASCII name for it.) Also called a LINE BREAK and represented as CTRL+J in some texts. It is not uncommon to find that character in a program-generated spreadsheet. Let's say I am not surprised.
Regarding your confusion over Ranman256's comments: If your data set has been LINKED to Access then the TransferSpreadsheet and TransferText methods are not applicable. He might have just read your post too quickly and missed your opening comment about having linked Excel tables.
Many ways can be used to get data into Access tables. Linking to a spreadsheet so that it looks like a table is one way; the "Transfer" methods are two other ways to COPY data from an external file into a table.
It should be noted that linking to an Excel spreadsheet works but there are some limitations on what you can do. The biggest one is that linked Excel data is essentially read-only. You cannot make design changes, and inserting or deleting rows is also not usually allowed. You usually cannot change content (update) the data either.
I think Ranman256's question was along the lines of using one of the Transfer methods to get data into your database tables so that you could modify the content of the tables. By linking, you force yourself to do something else, like COPY data from your linked table, because you won't be able to remove the CTRL+J from the original record (in the linked table). You will only be able to remove control characters from a COPY of that data element.
Is it truly unreadable or is it merely too long to be of much use or or what other way would you describe it?
Let me describe OUR problem with this mental image: You have just told the doctor you don't feel so good. But "don't feel so good" is a bit vague. Now your doctor asks you "Where does it hurt and how bad is the pain?"
It MIGHT be a simple fix but before we make too many assumptions, we need to know how you meant to use the data from that "ugly" field and we need to know what else might be wrong with it.
It would help us to know if you are comfortable with using code in the form of VBA (or Visual Basic for Applications, since you state that you have a language issue).
Also, in another thread, we have a member whose language is Hindi. Our advice included that he do a web search for "his topic HINDI" so he could see his topic explained in that language. Since this is a UK-based (therefore English-language) forum, most of us will speak and write in some dialect of English. I might advise you to consider language-specific web searches if we name something that you cannot translate clearly.
Great thank to you, Mr. The_Doc_man. That is the way I like resolving problems.
I'll only start arround the end of your post:
1. well, my general English is not so bad, but when it comes to your, programmers English I become "a little short". So maybe in future I'll post "something stupid" only because you wrote your usual language. Sorry in advance.
2. I'm familiar with VBA, I also write some simple code (DoCmd,requery,runSql,some simple loops) and I know something about Event driven actions. Immediatly after you asked me this I asked myself: What do you really want to achive?
Some facts:
1. In our company we have a list of more than 30.000 records as PRODUCTS
2. Company's software allows me to export them to .xls file as I want - always under the SAME NAME (overwritting the old file)
3. That is the reason I prefer LINKING and not IMPORTING .xls file to access database. I can import to Excel on daily basis (and have all changes in products (new entries and editions)) and having those changes in Access without importing the file every day)
4. I've immediately noticed that the linked "table" is read-only.
SO IF I LINK TABLE, WHATEVER FORMATED, DO I REALLY NEED TO FORMAT LINKED TABLE IN ACCESS?
The answer is normally ---> NO.
It can also be done in query or VBA but I don't know how to do it ---> final result should be some kind of datashet view with delimited text in field "Product Name"
5. The level of "unreadability" would be as follows:
Chipboard 18 mm WHITE5.160 x 2.100 mmLowdensity TexturedDoublesided RAL8000
On original (company's) software looks like:
Chipboard 18 mm WHITE
5.160 x 2.100 mm
Lowdensity Textured
Doublesided RAL8000
For the sake of example, let's call that field ProdDesc (since you didn't tell us its name). And to make it realistic, I will assume you have a ProdID field and maybe some sort of product location code called ProdLoc.
In a query that you use to drive a form with product descriptions, you might use something that looks like this:
Code:
SELECT ProdID, REPLACE( ProdDesc, vbLf, vbCrLf ), ProdLoc FROM ....
What that would do would be to change the Excel line break to the Access line break, which might then display as you showed. BUT you would have to do something special to allow that potentially multi-line field to fit where you want to display it. Otherwise you would only see the first line.
You would have to somehow set the row height of your text box or allow Access to determine the best height setting. This is trivial for non-continuous forms and reports that will be printed because you can just set the property ".CanGrow" to YES and it will always fit.
Sadly, there is a remarkable difficulty for setting individual row height on continuous forms because they are not statically designed. They are more or less generated on the spot. So I don't know that you will see what you wanted to see in that case.
A couple of solutions I found online suggested that if you are willing to do a little bit of design work, you could build a report that put your individual records in the detail section of the report and then have the "ugly" field set up with the .CanGrow=YES setting. Then display the report in Print Preview mode. Oddly enough, if you aren't in Print Preview, .CanGrow does not work the same.
This is the closest I can come to what I think you want. Otherwise, you would have to do some more complex form design, and I don't think you really wanted that.
For the sake of example, let's call that field ProdDesc (since you didn't tell us its name). And to make it realistic, I will assume you have a ProdID field and maybe some sort of product location code called ProdLoc.
In a query that you use to drive a form with product descriptions, you might use something that looks like this:
Code:
SELECT ProdID, REPLACE( ProdDesc, vbLf, vbCrLf ), ProdLoc FROM ....
What that would do would be to change the Excel line break to the Access line break, which might then display as you showed. BUT you would have to do something special to allow that potentially multi-line field to fit where you want to display it. Otherwise you would only see the first line.
You would have to somehow set the row height of your text box or allow Access to determine the best height setting. This is trivial for non-continuous forms and reports that will be printed because you can just set the property ".CanGrow" to YES and it will always fit.
Sadly, there is a remarkable difficulty for setting individual row height on continuous forms because they are not statically designed. They are more or less generated on the spot. So I don't know that you will see what you wanted to see in that case.
A couple of solutions I found online suggested that if you are willing to do a little bit of design work, you could build a report that put your individual records in the detail section of the report and then have the "ugly" field set up with the .CanGrow=YES setting. Then display the report in Print Preview mode. Oddly enough, if you aren't in Print Preview, .CanGrow does not work the same.
This is the closest I can come to what I think you want. Otherwise, you would have to do some more complex form design, and I don't think you really wanted that.
1. Tried REPLACE function in query. I get question for vblf and vbCrLf parameters value in pop-up window. Don't know how to put them in query
2. Tried same thing with ….Replace([fieldName], "FindSomething","ReplaceWith") ---> worked perfectly
3. Made form frmForm3, datasheet view. Whole text still in one line.
Example 1: OMARA 201/80/45 obešalnik brez vrat1 polica, 1 izvlečni obešalnikDobida
4. When I copy text from that field to some editor, also this one, I get the text in multi rows
Example2: OMARA 201/80/45 obešalnik brez vrat
1 polica, 1 izvlečni obešalnik
Dobida 5. If I try it in Immediate Window with Debug.Print Forms!frmForm3![ProductName] I also get text divided in multi rows as it should be
Example3: OMARA 201/80/45 obešalnik brez vrat
1 polica, 1 izvlečni obešalnik
Dobida
I've used the vbLF and vbCRLF constants before, but not in functions, so perhaps they don't work quite as expected in that context. But the data types returned by the CHR$ functions are characters, unequivocally.
Second post because I was reminded of something in another thread.
Regarding how much space you need to allow for the multi-line presentation, there is another option: On a form with a text box, it is possible to enable vertical scrolling bars so you could see one or two lines at a time and scroll your way through the rest of the description. The option for vertical scrolling is on the control's Format property sheet.
3. Made form frmForm3, datasheet view. Whole text still in one line.
4. When I copy text from that field to some editor, also this one, I get the text in multi rows
Verify from the control's Format properties sheet that you havn't picked HTML format. The fact that a copy/paste picks up the CR/LF combo correctly says that the substitution is correct so the problem must be in the control's formatting.
Second post because I was reminded of something in another thread.
Regarding how much space you need to allow for the multi-line presentation, there is another option: On a form with a text box, it is possible to enable vertical scrolling bars so you could see one or two lines at a time and scroll your way through the rest of the description. The option for vertical scrolling is on the control's Format property sheet.
Verify from the control's Format properties sheet that you havn't picked HTML format. The fact that a copy/paste picks up the CR/LF combo correctly says that the substitution is correct so the problem must be in the control's formatting.
