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
> Field Size And Decimal Changes, Access 2016    
 
   
Raas
post Sep 23 2019, 07:45 PM
Post#1



Posts: 639
Joined: 27-January 07
From: Northern Arizona


I am being asked to incorporate a comma delimited text file into an Access database. I know that the text file does not lend itself to normalization, but since this is what has been given to me by the textbook publisher, I'm trying to work with it.

The table will have 107 fields. 100 of them are for the collection of grades. Exporting from the publisher's software to a text file is fine. Importing that file to Access is mostly fine. The problem lies is having 2/3 of the fields coming in as Short Text, rather than number.

My question: Is there a way to read/scan a table field-by-field and change the field size from double to single, and the decimals from auto or zero to 2? I'm going to have to make a routine that can be done by a user, not by me.

If that's possible, I'd love to know how.

Thanks,

Go to the top of the page
 
MadPiet
post Sep 23 2019, 08:02 PM
Post#2



Posts: 3,361
Joined: 27-February 09



Can you link to the table, then do the conversions of the messy fields in your query using CINT, CDBL etc, and then import that?
Go to the top of the page
 
Raas
post Sep 23 2019, 08:12 PM
Post#3



Posts: 639
Joined: 27-January 07
From: Northern Arizona


I don't know. I don't know how to do that.
Go to the top of the page
 
projecttoday
post Sep 23 2019, 08:27 PM
Post#4


UtterAccess VIP
Posts: 11,194
Joined: 10-February 04
From: South Charleston, WV


How are you doing the import now (Docmd.Transfertext)?

--------------------
Robert Crouser
Go to the top of the page
 
GroverParkGeorge
post Sep 23 2019, 08:57 PM
Post#5


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


Normally, I would would import the data into a temporary, staging, table in the Access accdb. Then I would use update/append queries to move that new data into the permanent tables, changing the field to the appropriate datatypes while doing that.

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
Raas
post Sep 24 2019, 10:32 AM
Post#6



Posts: 639
Joined: 27-January 07
From: Northern Arizona


QUOTE
How are you doing the import now (Docmd.Transfertext)?


I use the import wizard. Didn't know anything about your referred to command. I'll have to try it, I guess.

Thanks
Go to the top of the page
 
Raas
post Sep 24 2019, 10:35 AM
Post#7



Posts: 639
Joined: 27-January 07
From: Northern Arizona


QUOTE
Normally, I would would import the data into a temporary, staging, table in the Access accdb. Then I would use update/append queries to move that new data into the permanent tables, changing the field to the appropriate datatypes while doing that.


I have tried the update query, but I don't know where in the query I can change the data type to Single, or the decimals to 2. I can see properties, but that doesn't seem to change anything.

I appreciate the help anyway.

Oh, I do import into a temporary table in access, first.
Go to the top of the page
 
GroverParkGeorge
post Sep 24 2019, 10:50 AM
Post#8


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


You can make the datatype changes in a couple of ways, but the one I'd choose would be something like this:

SQL
INSERT INTO tblDestination (YourDestinationFieldName1, YourDestinationFieldName2)
SELECT CDbl([tblTempSourceTable].[YourSourceFieldName1]) as YourDestinationFieldName1, CDbl([tblTempSourceTable].[YourSourceFieldName2]) AS YourDestinationFieldName2
FROM tblTempSourceTable;

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
John Vinson
post Sep 24 2019, 04:34 PM
Post#9


UtterAccess VIP
Posts: 4,285
Joined: 6-January 07
From: Parma, Idaho, US


I would suggest creating the table, EMPTY, with the appropriate datatypes. Then Link - not import - the text file, and run an Append query to migrate the data into the pre-created table.

--------------------
John W. Vinson
Wysard of Information
Go to the top of the page
 
projecttoday
post Sep 24 2019, 07:17 PM
Post#10


UtterAccess VIP
Posts: 11,194
Joined: 10-February 04
From: South Charleston, WV


John, can you link the file in code?

--------------------
Robert Crouser
Go to the top of the page
 
John Vinson
post Sep 26 2019, 04:37 PM
Post#11


UtterAccess VIP
Posts: 4,285
Joined: 6-January 07
From: Parma, Idaho, US


Sure. The TransferDatabase VBA method has that as an option.

--------------------
John W. Vinson
Wysard of Information
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    17th November 2019 - 01:05 PM