Go Back   Access World Forums > Microsoft Access Discussion > Tables

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 08-10-2018, 02:19 AM   #1
LjushaMisha
Newly Registered User
 
Join Date: Mar 2017
Posts: 20
Thanks: 1
Thanked 0 Times in 0 Posts
LjushaMisha is on a distinguished road
Delimited fileld in table

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

LjushaMisha is offline   Reply With Quote
Old 08-10-2018, 03:00 AM   #2
Ranman256
Newly Registered User
 
Join Date: Apr 2015
Location: KY,USA
Posts: 2,997
Thanks: 0
Thanked 657 Times in 642 Posts
Ranman256 will become famous soon enough
Re: Delimited fileld in table

Is it an Excel file, .xls?
Or
a delimited text file, .prn, .csv?

Either use the TransferText if its delimited,
Or
if .xls, transferSpreadsheet, then parse the delimited column using a query.
Ranman256 is online now   Reply With Quote
Old 08-10-2018, 03:24 AM   #3
LjushaMisha
Newly Registered User
 
Join Date: Mar 2017
Posts: 20
Thanks: 1
Thanked 0 Times in 0 Posts
LjushaMisha is on a distinguished road
Re: Delimited fileld in table

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 ????

Thanks in advance

LjushaMisha is offline   Reply With Quote
Old 08-10-2018, 06:20 AM   #4
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 11,990
Thanks: 56
Thanked 1,093 Times in 999 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: Delimited fileld in table

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.

Here is the problem that I have:

Quote:
long, almost unreadable line of characters
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.
__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is offline   Reply With Quote
Old 08-10-2018, 08:02 AM   #5
LjushaMisha
Newly Registered User
 
Join Date: Mar 2017
Posts: 20
Thanks: 1
Thanked 0 Times in 0 Posts
LjushaMisha is on a distinguished road
Re: Delimited fileld in table

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

Can you help with this, please
LjushaMisha is offline   Reply With Quote
Old 08-10-2018, 04:22 PM   #6
Cronk
Newly Registered User
 
Join Date: Jul 2013
Posts: 1,812
Thanks: 2
Thanked 387 Times in 383 Posts
Cronk will become famous soon enough
Re: Delimited fileld in table

Please post a copy of the spreadsheet with a few sample lines of data with the embedded line feed.
Cronk is offline   Reply With Quote
Old 08-10-2018, 08:45 PM   #7
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 11,990
Thanks: 56
Thanked 1,093 Times in 999 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: Delimited fileld in table

LjushaMisha

If your form actually has the room to display it, you COULD do something so simple as a REPLACE function in a query.

https://msdn.microsoft.com/en-us/vba...place-function

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.

__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is offline   Reply With Quote
Old 08-12-2018, 11:14 PM   #8
LjushaMisha
Newly Registered User
 
Join Date: Mar 2017
Posts: 20
Thanks: 1
Thanked 0 Times in 0 Posts
LjushaMisha is on a distinguished road
Re: Delimited fileld in table

The_Doc_Man

Thanks. I'll try to use your suggestion. Not immediately (this is not the main thing I'm doing in the company) but I'll give you the result
Thanks
LjushaMisha is offline   Reply With Quote
Old 08-12-2018, 11:17 PM   #9
LjushaMisha
Newly Registered User
 
Join Date: Mar 2017
Posts: 20
Thanks: 1
Thanked 0 Times in 0 Posts
LjushaMisha is on a distinguished road
Re: Delimited fileld in table

To CRONK, to The_Doc_Man

tblProductsIdProdProdNameDim XDim YDim Z019826DOORS Nolte WHITE X-T-1364/0589-A490FSC Mix credit 1364x589x16572123/013801.364,00589,0016,00019868CEILING Nolte WHITE X-T-1408/0731-A490FSC Mix credit 1408x731,6x16571341/013801.408,00731,6016,00019902BOTTOM Nolte GREY X-T-0476/0293-A490FSC Mix credit 476,7x293x16572117476,70293,0016,00
LjushaMisha is offline   Reply With Quote
Old 08-12-2018, 11:18 PM   #10
LjushaMisha
Newly Registered User
 
Join Date: Mar 2017
Posts: 20
Thanks: 1
Thanked 0 Times in 0 Posts
LjushaMisha is on a distinguished road
Re: Delimited fileld in table

tblProductsIdProdProdNameDim XDim YDim Z019826DOORS Nolte WHITE X-T-1364/0589-A490FSC Mix credit 1364x589x16572123/013801.364,00589,0016,00019868CEILING Nolte WHITE X-T-1408/0731-A490FSC Mix credit 1408x731,6x16571341/013801.408,00731,6016,00019902BOTTOM Nolte GREY X-T-0476/0293-A490FSC Mix credit 476,7x293x16572117476,70293,0016,00
LjushaMisha is offline   Reply With Quote
Old 08-12-2018, 11:22 PM   #11
LjushaMisha
Newly Registered User
 
Join Date: Mar 2017
Posts: 20
Thanks: 1
Thanked 0 Times in 0 Posts
LjushaMisha is on a distinguished road
Re: Delimited fileld in table

I've tried to paste excel file
Attached Files
File Type: xlsx ZaQuote.xlsx (9.8 KB, 8 views)
LjushaMisha is offline   Reply With Quote
Old 08-13-2018, 09:57 PM   #12
LjushaMisha
Newly Registered User
 
Join Date: Mar 2017
Posts: 20
Thanks: 1
Thanked 0 Times in 0 Posts
LjushaMisha is on a distinguished road
Re: Delimited fileld in table

Quote:
Originally Posted by The_Doc_Man View Post
LjushaMisha

If your form actually has the room to display it, you COULD do something so simple as a REPLACE function in a query.

https://msdn.microsoft.com/en-us/vba...place-function

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.
Hello,

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


Any new suggestions about the metter, pls
LjushaMisha is offline   Reply With Quote
Old 08-13-2018, 10:02 PM   #13
LjushaMisha
Newly Registered User
 
Join Date: Mar 2017
Posts: 20
Thanks: 1
Thanked 0 Times in 0 Posts
LjushaMisha is on a distinguished road
Re: Delimited fileld in table

Quote:
Originally Posted by Cronk View Post
Please post a copy of the spreadsheet with a few sample lines of data with the embedded line feed.
After several attempts I succeed to post "short copy" of the file which is below in my answer.
Hope you'll get it
Attached Files
File Type: xlsx ZaQuote.xlsx (9.8 KB, 3 views)
LjushaMisha is offline   Reply With Quote
Old 08-13-2018, 10:27 PM   #14
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 11,990
Thanks: 56
Thanked 1,093 Times in 999 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: Delimited fileld in table

Quote:
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
The REPLACE function should work in a query but as function parameters? Maybe those won't work. In which case I might use

REPLACE( field-name, CHR$(10), CHR$(13) & CHR$(10) )

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.
__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is offline   Reply With Quote
Old 08-13-2018, 10:35 PM   #15
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 11,990
Thanks: 56
Thanked 1,093 Times in 999 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: Delimited fileld in table

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.

Quote:
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.

__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Delimited file - linked table tucker61 Tables 2 11-08-2011 11:51 AM
addresses. Single fileld v multiple fields Foorboy Theory and practice of database design 6 09-05-2011 11:14 PM
Populating a fileld depending on the value of another ? ciskid Forms 1 07-30-2006 11:36 AM
How to get some Fileld from Query to a string? sasolini Queries 1 11-22-2005 08:45 AM




All times are GMT -8. The time now is 09:39 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World