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
> Import Field Name Being Garbled, Access 2016    
 
   
LagoDavid
post Dec 28 2017, 12:06 AM
Post#1



Posts: 446
Joined: 12-October 03
From: Texas


I am trying to use the DoCmd.TransferText command to import a CSV file into a temporary table which has the same field names as the import CSV file. I verified what the first row of the import file contains using NotePad,

My first column name is being screwed up during the import process. Instead of being simply Customer Name, it is coming in as Customer Name. I don't know the origin of these extra characters, nor how to eliminate them. The extra characters are not present when I view the import file in NotePad nor in Excel.

All of the other field names are importing from the first row of the CSV file as expected; just not the first column.
Go to the top of the page
 
RJD
post Dec 28 2017, 05:22 AM
Post#2


UtterAccess VIP
Posts: 8,964
Joined: 25-October 10
From: Gulf South USA


Hi: First try just linking the CSV file and seeing what the field name looks like. Then, if that is not satisfactory, remove the blank space in the field name. Sometimes I have found that to be an issue. Failing that, check to see if there is an invisible character in front of the field name.

Just some things to test/try. Hard to diagnose without having the file to work with.

HTH
Joe
Go to the top of the page
 
GroverParkGeorge
post Dec 28 2017, 06:32 AM
Post#3


UA Admin
Posts: 33,794
Joined: 20-June 02
From: Newcastle, WA


I prefer to use a tool like NotePad++ to view such files. It can reveal non-printing characters like these.
Go to the top of the page
 
BruceM
post Dec 28 2017, 07:44 AM
Post#4


UtterAccess VIP
Posts: 7,685
Joined: 24-May 10
From: Downeast Maine


To George's suggestion I will just add that Notepad++ is a very good freeware program. I find it especially helpful when building SQL in VBA, and writing SQL for union queries and other situations where the graphic query designer can't be used.
Go to the top of the page
 
PhilS
post Dec 28 2017, 08:49 AM
Post#5



Posts: 509
Joined: 26-May 15
From: The middle of Germany


QUOTE
My first column name is being screwed up during the import process. Instead of being simply Customer Name, it is coming in as Customer Name. I don't know the origin of these extra characters, nor how to eliminate them.

 is a Unicode Byte Order Mark (BOM). It indicates the byte order for multi-byte characters in Unicode files.
The appropriate CodePage identifiers are either 65000 (UFT7) or 65001 (UTF8).

Go to the top of the page
 
LagoDavid
post Dec 28 2017, 09:56 AM
Post#6



Posts: 446
Joined: 12-October 03
From: Texas


Phil S,
FANTASTIC! I tried both in the CodePage parameter and 65000 did not work but 65001 did. Thank you so much. I would never, never have figured this out!

For others referencing the use of NotePad++, I opened the file in NOtePad++ and the special characters do not show there either.

I did not try linking since the CodePage parameter worked.

Thank you all.

David
Go to the top of the page
 
LagoDavid
post Dec 28 2017, 10:34 AM
Post#7



Posts: 446
Joined: 12-October 03
From: Texas


Well, I thought everything was good until I looked at my imported data.

The imported data file is analytical data and contains a RESULT field which I have formatted in my temporary table as short text. This is because often the data contains the less than comparator (<). Right now, any results which contain the < symbol are not being imported. It is as if the import process is thinking that field is a numeric field and ignores any field value which contains text.

Any ideas on how I can correct this?

It is definitely a type conversion error. I see that in the import errors table that Access generates. I have verified multiple times now that the RESULT field in the temporary table is formatted as a short text, and not numeric.
This post has been edited by LagoDavid: Dec 28 2017, 10:54 AM
Go to the top of the page
 
GroverParkGeorge
post Dec 28 2017, 10:51 AM
Post#8


UA Admin
Posts: 33,794
Joined: 20-June 02
From: Newcastle, WA


One way to deal with that is to create an Import/Export specification that defines each field in the source table the way you need it to be.
Go to the top of the page
 
LagoDavid
post Dec 28 2017, 03:36 PM
Post#9



Posts: 446
Joined: 12-October 03
From: Texas


Thank you GPG. I did that and it works; at least for now. there were so many little "catches" in creating this import process I don't have a lot of confidence that it will work indefinitely.

I researched the creation of Import Specifications and created a new spec. I was not aware but I had previously created some others unknowingly. I guess a spec is created and saved every time I tried to save an import procedure using the Wizard.
Anyway I created a new spec and saved it with a meaningful name and then used that in the TransferText function. It works.

This spec that I have created, will it always be in the database even though I cannot see it other than to re-start the Wizard and look in there?
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    23rd October 2018 - 07:23 AM