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
> Exporting Numbers To Csv From Query In Access, Access 2007    
 
   
putdownfox
post Sep 1 2019, 01:16 PM
Post#1



Posts: 3
Joined: 1-September 19



I've been struggling to get numbers that are more than 10 or 11 digits long to not truncate the decimals when opened as csv in excel. I need the decimals to be there so I can upload this data into a separate SQL database and if the decimals do not show in the CSV file only the whole numbers upload to SQL. If I open the file and format all the numbers as numbers (they export in general format) and resave, it will upload fine, but the purpose of this database was to import the data and have it export the files that need to be uploaded automatically without having to open each file and format etc.

For example, my query looks like this when i run it in access. There are decimals.
Attached File  ACCESS.png ( 7.69K )Number of downloads: 7



When I export it to CSV I get the below:
Attached File  CSV_FILE.png ( 5.43K )Number of downloads: 6



When I click in the cells of the ones that the decimals are truncated I see this:
Attached File  Cell.png ( 3.37K )Number of downloads: 0



When I sum 2 of the truncated cells they only sum the rounded numbers, which seems odd since when you click in each cell the decimals are there.
Attached File  sum.png ( 19.13K )Number of downloads: 5




Help!
Attached File(s)
Attached File  ACCESS.png ( 7.69K )Number of downloads: 3
 
Go to the top of the page
 
theDBguy
post Sep 1 2019, 01:34 PM
Post#2


Access Wiki and Forums Moderator
Posts: 76,602
Joined: 19-June 07
From: SunnySandyEggo


Hi. Welcome to UA! welcome2UA.gif

I think you may be worrying about nothing here. If you're exporting Access data into CSV with the intention of importing said CSV file into another database, then I'd say go ahead and give it a try. "Looking" at the exported data using Excel is an unnecessary step, which is causing you concerns that may not really be there. For example, if you open the CSV file using Notepad, do you see any problems?

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
putdownfox
post Sep 1 2019, 02:35 PM
Post#3



Posts: 3
Joined: 1-September 19



That's the problem. When the data gets imported into the SQL database its only takes the truncated values. If i open the files, then format as a number and upload it to the SQL database the full numbers show.
Go to the top of the page
 
theDBguy
post Sep 1 2019, 03:34 PM
Post#4


Access Wiki and Forums Moderator
Posts: 76,602
Joined: 19-June 07
From: SunnySandyEggo


QUOTE (putdownfox)
If i open the files,...
How exactly are you "opening" the file? CSV files are Text files, not Excel files. So, formatting them means you're not using a Text Editor.

QUOTE (putdownfox)
That's the problem. When the data gets imported into the SQL database its only takes the truncated values.
How exactly are you importing the data into the SQL database? Are you sure it's set up to import a CSV/Text file? What happens if you re-import the CSV file back into the same Access file where the data came from? Do you get the same problem? Remember, if you decide to try this, make sure you select/use the Import > Text route/option.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
June7
post Sep 1 2019, 04:10 PM
Post#5



Posts: 968
Joined: 25-January 16



Why is CSV involved? Why not Access link to SQL db?
This post has been edited by June7: Sep 1 2019, 04:11 PM

--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG! http://www.cpearson.com/Excel/DebuggingVBA.aspx
Go to the top of the page
 
putdownfox
post Sep 1 2019, 04:45 PM
Post#6



Posts: 3
Joined: 1-September 19



We don't have access to that connection. We have to upload the file through a web portal.
Go to the top of the page
 
June7
post Sep 1 2019, 08:24 PM
Post#7



Posts: 968
Joined: 25-January 16



I can't explain why truncation happens nor how to directly fix.

A work-around would be to have Access VBA open Excel file and set formatting and save the edited file.
This post has been edited by June7: Sep 1 2019, 08:26 PM

--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG! http://www.cpearson.com/Excel/DebuggingVBA.aspx
Go to the top of the page
 
theDBguy
post Sep 1 2019, 09:26 PM
Post#8


Access Wiki and Forums Moderator
Posts: 76,602
Joined: 19-June 07
From: SunnySandyEggo


Hi. June7's post made me think about something. I know CSV files are Text files, so I was wondering why you would open it in Excel, change the format, and then save the file again. So, I did a little experiment. I manually created a CSV Text file and entered some text and decimal numbers. When I opened the file in Excel, it looked fine to me. I then changed the format of one of the numbers to a Percent. When I saved the file and opened it using Notepad, I see that Excel actually added the percent sign to the number. I opened the CSV file again in Excel and changed the format of another number to have a fewer number of decimal places. When I saved the file again and opened it in Notepad, the number is now missing the other decimal precision. This little experiment tells me, opening the CSV file in Excel and changing its format also changes the actual data in the CSV file. So, I got to thinking, why would you need to open the file in Excel and change the format to a Number? I thought maybe the CSV file you're getting from Access has quotes around the values. So, I created a new CSV file and enclosed each number in quotes. When I opened this file in Excel, the quotes don't show up (probably because Excel strips them out) and when I saved the file to CSV from Excel and opened it using Notepad, the quotes were gone. So, my guess is your export routine in Access probably enclose the values with quotes and when you import the exported CSV file into the SQL database, it wasn't expecting the quotes.

So, again, the question was, how exactly were you exporting and importing the CSV file from Access and into the other database? Also, are you using any import/export specification to specify what data types the values are supposed to represent and if you are or are not including quotes in the exported values? Have you tried simply opening the CSV file from Access using Notepad? If so, what does it look like?

Just a thought...

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
June7
post Sep 1 2019, 09:50 PM
Post#9



Posts: 968
Joined: 25-January 16



Opening a csv in Excel and formatting requires saving the modified file as an xlsx. Is this what you do? So the web portal is importing xlsx file?
This post has been edited by June7: Sep 1 2019, 09:52 PM

--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG! http://www.cpearson.com/Excel/DebuggingVBA.aspx
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    20th November 2019 - 11:02 PM