Programmatically Change Double Data Type To Long Integer

[Qaspec]

I have a csv file that is updated by being replaced by another csv with the
same file name each day. I would like to append the data each day after the
file is updated. The problem is the CSV contains a number with a 'double'
data type. I need the data type for the number to be 'Long Integer'.

Linking the csv wipes out a majority of the numbers and linking the file
each day.

Also modifying an make/table query, changing the resulting data type
properties and then running an append query is work intensive.

How would I create a module that will create a table or excel file that will
change the double type back to long integer that I can have my db linked to?
Then I could use a macro to run my module and append query to update the data
in my db in the number format i need. Thanks for any help.

 

[Jeff Boyce]

I may not be properly visualizing your situation...

Given what I understand, what I would do is link to the raw data, then use a
query to "parse" that data (including converting your double to a long) into
your more-permanent (and hopefully better-normalized) Access tables.

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[Qaspec]

Linking the csv wipes out a majority of the numbers. Most of the records show
"#Num"

 

[Jeff Boyce]

Something is different about your situation than what I've run across.

When I link to a csv file, I see the values. What do you mean by "wiped
out"? Changed to ??? Missing ???

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[Qaspec]

The link returns #Num values in all of the records. When i check the
properties of the link it's set to 'Long Integer'....when I import the data
instead of linking the table the records show as normal but have a 'Double'
data type.

I need to somehow import the records using something like TranferSpreadsheet
but when I do that I get "Data Type Conversion Error" and "Unparsable Record"

 

[Qaspec]

This is related to the problem I'm experiencing. It's an article from
Microsoft Support.


http://support.microsoft.com/kb/824182


I still need to resolve the issue and I was hoping that there could be some
kind of workaround using VBA to use the TransferSpreadsheet Method with a
tweak to handle the data type change or somehow change the format of the csv
file to something that will automatically replace double with long
integer...or something awesome and cool that I probably haven't even heard of
yet.

 

[Jeff Boyce]

Are you attempting to "import" the data, as the microsoft article mentions?
If so, why? Why not just link to the data, then use a query to parse the
contents into your more permanent tables?

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[Qaspec]

I am not using the wizard to import the data, but similiar to the article
when I try to link the file it cannot handle the double data type as it tries
to automatically set it as long integer which then shows #Num for each
record.

When you import without using the wizard you are given the opportunity to
choose "advanced" and manually change the data type from "Long Integer" to
"Double". When you link, same as the import wizard, the "long integer" data
type doesn't convert properly from the "double" type from the source file.

I'm trying to avoid having to import the file everyday, then using advanced
to manually change the data type to double, then using a maketable query to
switch the data type to long integer so i can cross reference with another
table, and finally running an append query to add to my historical data. All
because the text file is storing a 10 digit phone number as "Double"

 

[Jeff Boyce]

As I asked in one of your other posts on this subject, please provide a
sample of the data from the csv file that is causing this issue.

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.