UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Docmd.transfertext Remove The 2 Last End Characters Of A String If Characters Are Space, Access 2010    
 
   
informer
post Jan 28 2020, 11:40 AM
Post#1



Posts: 233
Joined: 18-May 16
From: Toulon - South France


Hi

I use for uploading a csv file this method
CODE
[code]DoCmd.TransferText transferType:=acImportDelim, SpecificationName:=sSpecImport, _</div><div class="codemain">TableName:=sTableName, FileName:=sPath & sFileName, hasfieldnames:=True
[/code]

It works fine but if a CSV string finishes with 2 SPACE characters, the SPACE characters are systematically removed by the import process.

How to proceed to avoid the deletion of this SPACE character?



--------------------
Happy new year 2020

When a wise man points at the moon the imbecile examines the finger (conficius 551 BC - Death 479 BC)
Go to the top of the page
 
GroverParkGeorge
post Jan 28 2020, 12:33 PM
Post#2


UA Admin
Posts: 36,799
Joined: 20-June 02
From: Newcastle, WA


Can you provide a sample of the data in a typical csv to work on?

--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
informer
post Jan 28 2020, 12:49 PM
Post#3



Posts: 233
Joined: 18-May 16
From: Toulon - South France


Hi GroverParkGeorge

I'll provide a CSV file when I'll be back to the office
This post has been edited by informer: Jan 28 2020, 12:50 PM

--------------------
Happy new year 2020

When a wise man points at the moon the imbecile examines the finger (conficius 551 BC - Death 479 BC)
Go to the top of the page
 
GroverParkGeorge
post Jan 28 2020, 12:50 PM
Post#4


UA Admin
Posts: 36,799
Joined: 20-June 02
From: Newcastle, WA


Thank you.

--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
informer
post Jan 29 2020, 05:07 AM
Post#5



Posts: 233
Joined: 18-May 16
From: Toulon - South France


Hi GroverParkGeorge,

I uploaded a file with one row for testing.
  • The value for DES field has 2 space characters at the end
  • The field delimiter is a semicolon ";"

Thanks again for your help
Attached File(s)
Attached File  SpaceCharacters.txt ( 35bytes )Number of downloads: 3
 

--------------------
Happy new year 2020

When a wise man points at the moon the imbecile examines the finger (conficius 551 BC - Death 479 BC)
Go to the top of the page
 
GroverParkGeorge
post Jan 29 2020, 08:52 AM
Post#6


UA Admin
Posts: 36,799
Joined: 20-June 02
From: Newcastle, WA


Sure wish we had multiple rows to work with.... A sample of one is pretty thin.

We'll see what we can do with it.

--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
GroverParkGeorge
post Jan 29 2020, 09:14 AM
Post#7


UA Admin
Posts: 36,799
Joined: 20-June 02
From: Newcastle, WA


Okay, there are actually two lines here, not one.
Attached File  Textfile.png ( 2.49K )Number of downloads: 0


The first line appears to be Field Names -- ID, DES and tags.

The second line appears to be values -- MyID, Desc 10 and ERROR.

That raises a couple of additional questions. In the csv --not in this txt, but in the actual csv you must use -- does the header row appear exactly once? And do multiple record lines all have the same problem with trailing spaces in that DES field?

--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
informer
post Jan 29 2020, 09:14 AM
Post#8



Posts: 233
Joined: 18-May 16
From: Toulon - South France


Hi GroverParkGeorge
I sent a light file to upload it rapidly into Access db and avoid to pollute it by other rows
The only one solution that I found is to convert csv/txt file to Excel file and import the Excel file.With this method, the space characters are keeped

You're right
  • 1st line -> Column names. Appear once
  • 2nd line -> Values
You can duplicate x times the second line and see what happens, the same problem, space characters on begining/ending are removed.

Thanks for your help
This post has been edited by informer: Jan 29 2020, 09:20 AM

--------------------
Happy new year 2020

When a wise man points at the moon the imbecile examines the finger (conficius 551 BC - Death 479 BC)
Go to the top of the page
 
GroverParkGeorge
post Jan 29 2020, 09:27 AM
Post#9


UA Admin
Posts: 36,799
Joined: 20-June 02
From: Newcastle, WA


To be honest, the difference between one line and 100 lines in a test import would be so small I probably couldn't even tell.

I also test UA uploads like this in test accdbs that I throw away after testing anyway so now worries about "polluting" anything.





--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
informer
post Jan 29 2020, 11:06 AM
Post#10



Posts: 233
Joined: 18-May 16
From: Toulon - South France


GroverParkGeorge,
You're right but do you have the same problem with space characters?


--------------------
Happy new year 2020

When a wise man points at the moon the imbecile examines the finger (conficius 551 BC - Death 479 BC)
Go to the top of the page
 
GroverParkGeorge
post Jan 29 2020, 11:16 AM
Post#11


UA Admin
Posts: 36,799
Joined: 20-June 02
From: Newcastle, WA


Sorry, I lost my internet connection for a while.

Yes, the import from CSV or from txt files does truncate trailing spaces.

The solution is going to be the one you've adopted by going through Excel or writing VBA to read the file line by line and parsing it, which would also work but might be more work than it's worth.

--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
informer
post Jan 29 2020, 12:23 PM
Post#12



Posts: 233
Joined: 18-May 16
From: Toulon - South France


Thanks GroverParkGeorge

So there is a bug because import csv/text file process must preserve the data in its entirety

--------------------
Happy new year 2020

When a wise man points at the moon the imbecile examines the finger (conficius 551 BC - Death 479 BC)
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    26th February 2020 - 04:49 PM