Error when Importing Large Numbers into Access via csv 2

[GirlBlonde]

Hi guys i am pulling my hair out here, i have a system with card numbers on it that my users will be downloading on a monthly basis so i want to automatic via a button. the original data was in excel but as the number is 16 digits long i had to convert it to a csv file to bring it in and convert that field to text via the properties specifications. All was well until i trying to automatic via the macro import text function, for some reason this reverts back to the original problem i had and distorts my numbers again?? My users will not want to sit there and go through the hassel of importing so i need to do this at a push of a button. Can anybody help me, I'm back to where i started!

Energy & persistance conquer all things!

 

[PHV]

Use an import specifications file.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[JerryKlmns]

I think that you can deal with that, the same way as in your thread181-1423301

 

[GirlBlonde]

hi guys

i did the import specifications and it does work then fine. the problem only arises when i try to automate it through a macro. transfer text option. this is where it goes wrong, for some reason it does not import the large number with the macro? i really do not want my usesr to have to go through the whole import function themselves. i have my table with the right import specifications that it is going directly into. has anyone done this before and had the same problem?

Energy & persistance conquer all things!

 

[JerryKlmns]

Would you convert your macro to VBA (on menu Tools --> Macro --> Convert Macros to Visual Basic) and post that?

 

[GirlBlonde]

hi
will do, have it at work will post it in the morning, thanks for your help, hopefully i can figure this out. i just don't get it,i import it fine by manually doing it per say, but when i try to automatic via macro it reverts back to distorting the numbers?? anyway will post it in the morning for you

thanks for the helping hand

this file will be the death of me!!

Energy & persistance conquer all things!

 

[JulieMontgomery]

If you want to keep this import in a macro, have you tried to include your import specification name in the "Specification Name" option for the TransferText action? I remember years back having an import macro for which I was able to incorporate my import specification into the macro, it's just been a while since I've done it. Access offers some instruction for this in its Help file.

 

[SkipVought]

Do you REALLY have 16 digit NUMBERS? I mean are you doing ARITHMETIC on these values.

I'm wagering a diet cherry limeade, that these values are IDENTIFIERS, like Part Numbers, Invoice Numbers, SSNs, Employee Numbers etc. As IDENTIFIERS, they are not NUMBER, albeit containing all numeric characters. Rather they ought to be TEXT.

Would that solve any problems?

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[JerryKlmns]

Skip

they are card numbers which, they should be stored in a Text(16) data type field since there wont be any calculations but, that, might be of Decimal(16,0) which by the way uses less storage ( =12 bytes). I also have the feeling that for the 2nd, sorting - filtering - joins, are faster. Or I might have a wrong impression?

_{[I can 't digest that text(16) ]}

 

[GirlBlonde]

Hi Guys

Sorry been ill thats why been absent of late, told you this file would be the death of me! here is the macro converted to module below. The number field i have converted to text field in my import specifications as mentioned i will not be doing anything with it as it is a card number. this is what it looks likes after the macro runs 7.07134001342695E+15 should be 7071340013109850. ah seems so simple doesn't it? as i said when i do the 'manuel' import it's fine. its when i do the macro it goes beserk. HELP!!

Function Mcr_CaltexFuelCards_caltex_card_feul_download()
On Error GoTo Mcr_CaltexFuelCards_caltex_card_feul_download_Err

' h:\sue\BDRM\FBT Fleet\CaltexCard\NewMth.xls
DoCmd.TransferText acImportDelim, "", "Tbl_ImportCaltexCards", "h:\sue\bdrm\FBT Fleet\CaltexCard\Sep07.csv", True, "", 51256


Mcr_CaltexFuelCards_caltex_card_feul_download_Exit:
Exit Function

Mcr_CaltexFuelCards_caltex_card_feul_download_Err:
MsgBox Error$
Resume Mcr_CaltexFuelCards_caltex_card_feul_download_Exit

End Function


Energy & persistance conquer all things!

 

[Treman]

Hi,

I don't know if this applies to your situation but I thought I would post this anyway. I read about a problem like this on another forum that was caused by Record locking. Here is a link to the thread:

http://groups.google.com/group/micr...7fc81a2aec5/63de20ad864746e0#63de20ad864746e0

Patrick Wood

 

[GirlBlonde]

hi treman

thanks for that, thats great. i may need that later on, buy i have a problem though with a large number thats not importing.i am getting all my records fine as there are not to many of them it's just that one colum that even though it is specified as text it is still coming up distorted as above mentioned when imported via macro. it just doesn't make sense to me. it works fine without the macro...as soon as i use the macro no go...????desparate now!

Energy & persistance conquer all things!

 

[JerryKlmns]

GirlBlonde

Have you tried to call not the macro but the function converted from it?

Sub cmdImmportCSV_Click()

dim a as Boolean

a=Mcr_CaltexFuelCards_caltex_card_feul_download

end sub

 

[GirlBlonde]

hi Jerry

Sorry been absent of late go the flu. Have tried your suggestion no go i'm afraid. although i may not have done it right i will explain. i am not a wiz like you i fear. i used your code and as predicted nothing happened because i assumed i needed to add something didnt i? so i'm not sure if i just did the same thing and blew your theory but i did the below code which i guess is the macro converted anyway, and the same thing happened.Albiet much faster. i am starting to think this one is a no hoper and i will have to just build a pop up help screen to guide them through the steps of importing. i will be shattered if i can not figure this out all because of a number! the frustrating thing is that it works, just not this way.i am out of ideas and am now relying on the big guns to help me out there.....


Sub cmdImmportCSV_Click()

Dim a As Boolean

a = Mcr_CaltexFuelCards_caltex_card_feul_download

' h:\sue\BDRM\FBT Fleet\CaltexCard\NewMth.xls
DoCmd.TransferText acImportDelim, "", "Tbl_ImportCaltexCards", "h:\sue\bdrm\FBT Fleet\CaltexCard\Sep07.csv", True, "", 51256

End Sub


Energy & persistance conquer all things!

 

[JerryKlmns]

GirlBlonde

Sub cmdImmportCSV_Click() is a command' s button (placed on a form named as cmdImmportCSV) Click event.

If you like to test the Function, you could open the module where it is saved, click inside that function and press F8. This should start running the Function one statement after the other, just press it as many times as it needs.

One thing to keep in mind is that, although Excel 2003 can store 32655 characters in a cell, numeric values grater than 15 digits, like yours 16 card number, loose all digits from 16th to the end, which are changed to zero (0). That column should be formatted as text, before you enter the values.

BTW if I was a wiz I would be hovering around you explaining my thoughts, while I would be on lazy chair infront of the sea side enjoying my drinks & snacks!

 

[GirlBlonde]

Hi Jerry (i hope thats your name)

yes i know thats a command button thats where i put the code on a command button on a form and still no go. i tried your suggestion of stepping through the code and still the same thing also. i also can not convert it to text in excel because when i do it looks like this 7.07134E+15. i can not keep it in excel due to size. plus i need to have it all in the one system as i said this is a monthly download that happens on the 15th of every month.
This is a petrol card like a credit card. i have the table formatted to text that it is going into but it is still going haywire with this automating but works with the lets say manual way. i have a table set up for the data to go into that has the correct import specs. i just dont get it. any other ideas?


Energy & persistance conquer all things!

 

[JerryKlmns]

If you save the excel as a csv file and open it with notepad, does these card numbers still show in scientific? If not use the csv to import. The other alternative is to get downloaded file as txt.

Did you check if excel keeps the numbers valid? No 0 at then 16th digit? This example is frightenning

7.07134001342695E+15
7071340013109850

 

[GirlBlonde]

Hi Jerry
Firstly THANKS for helping me, this is a NIGHTMARE isn't it! i can not believe it. ah we will talk about this one. now back to it.i have no idea what you mean sorry when you say open it with notepad?? sorry, can you explain that one maybe i know and i don't know it. i can not get the data in a text as it comes to me supplied by another division and they considered themself as god and would not do anything any differently. so out of the question I'm afraid. it does not capture all 16 digits, either i checked that today. tried all those options except notepad. hit me with that one and i will give that a go, heck it can't hurt. i think it's possessed. nobody else even wants to touch it but you!

Energy & persistance conquer all things!